Beverly Voth, Level: Intermediate, Version: FM 14 or later, XML

Excel Exports using XML and XSLT

Editor’s note: Once again it is an honor and a privilege to present an in-depth guest article written by Beverly Voth.

This is a follow-up to the User-Friendly Excel Exports series by Kevin Frank, but using an XML export along with various XSLT to give you .csv or .xls (for Excel). The first demo is a quick export (to CSV – comma-separated values) using an XSLT to help explain the relationship with FileMaker Pro XML export. How an XSLT style sheet reads that data to transform it into another text format (.csv or .xls) is also explained throughout the article and in comments within the XSLT. The demos will get increasingly more complex, but ultimately more “user-friendly”.

Demo file: Excel-Exports-using-XML-and-XSLT.zip

Continue reading “Excel Exports using XML and XSLT”

Level: Intermediate, Version: FM 14 or later

Reciprocal Linkage, part 3

Note: Leaner does not always mean faster, especially if record counts will be large… in which case I would recommend going with the approach discussed in part 2.

[3 Jan 2018: demo updated to version 3.2 as per Jens Rasmussen’s comment below.]

[2 Nov 2017: demo updated to version 3.1 as per my exchange with Jonathan Mickelson in the comments section below.]

I thought after part 2 that this technique had been thoroughly discussed and dissected, but Geoff Gerhard of Creative Solutions writes to point out that, if the goal is to have as few moving parts as possible, the table occurrence (TO) count can be further reduced, from four to three…

…and he has provided a demo file which I am sharing with his permission.

Demo file: reciprocal-linkage-GGv3.2.zip

Continue reading “Reciprocal Linkage, part 3”

Level: Intermediate, Version: FM 14 or later

Reciprocal Linkage, part 2

This is a quick follow up to last week’s article on reciprocal (bi-directional) linking, and will assume the reader is familiar with that material. Paul Jansen of APJ Computing Solutions sent in a revision of one of my files demonstrating that, among other things, the graph could be a good deal leaner (as per my stated goal of making the approach as simple and lightweight as possible), and I am sharing that file today with his permission, along with my own attempt at further simplification.

Demo Files

Continue reading “Reciprocal Linkage, part 2”

Level: Intermediate, Version: FM 14 or later

Reciprocal Linkage

Challenge: in a multiuser document management system, facilitate the linking (and unlinking) of any document with any other. Make it clear which documents are linked to the current document, and make sure the approach is multiuser friendly — in other words, allow document records to be linked and unlinked even if one or both of those document records are currently being edited by another user.

2017-10-19_12-12-12

Today we have two demo files, one using standard serial numbers and one using UUIDs. Except for serials vs. UUIDs, the two files are functionally identical, and in the interest of clarity, I’m going to focus primarily on the serial number version.

Demo files: reciprocal linkage & reciprocal linkage with uuids  Continue reading “Reciprocal Linkage”

ExecuteSQL, Level: Intermediate, SQL, Version: FM 14 or later

FM 14: Separation Model Data Mining

Note: Interface file #1 requires FM 14 or later; interface file #2 works with FM 12 or later.

What do you get when you combine the Separation Model + FM 14 placeholder text + ExecuteSQL + a million-record table + a variable array + Get(CalculationRepetitionNumber) + the Mod and Ceiling functions + a couple custom functions, with blazingly fast (local, LAN and WAN) performance thrown in for good measure?

This article w/ accompanying demo (fm-14-separated-data-mining.zip), that’s what.

7-7-2015 12-33-33 PM

The Challenge

Provide a data-mining interface to query a million-record table (cc_transactions) containing 20 years’ worth of credit card transactions. The client wants to be able to pick a date via a calendar widget, and see transaction info for that date, summarized by card type, transaction type and region.

7-6-2015 8-54-58 AM

This is a separated solution, with cc_transactions living in a file called z_data.fmp12, and a specific requirement for this project is to not make any schema changes to the data file. The data-mining will take place in a separate interface file… or in this case, two interface files, since we’re going to look at two methods.  Continue reading “FM 14: Separation Model Data Mining”

Level: Intermediate, Version: FM 14 or later

FM 14: Separation Aggregation Aggravation revisited

Four years ago I posted the original version of this article, exploring a technique that worked fine in FM 11, but that broke and required extensive work arounds in FM 12 and 13. What you’re about to read is a revision of the original article, showcasing a FM 14 method made possible by a new feature: placeholder text.

Introduction

FileMaker developers take it for granted that calculated sub-total and total fields will update automatically when portal rows are created, edited or deleted. This becomes problematic when one a) uses the separation model, and b) either intentionally or unintentionally holds the parent and related child records open (uncommitted) while editing, but still expects to see these aggregates update in real time.

7-3-2013 2-57-02 PMToday’s demo file is FM 14 Aggregates and Separation, and for demonstration purposes it has interface elements in both the Data file and the Interface file. Normally of course, in a separated solution, you wouldn’t have interface elements in the Data file (since that would defeat the purpose of separation). Here we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three calc fields below the portal are tracking the changes in real time. Continue reading “FM 14: Separation Aggregation Aggravation revisited”

Level: Intermediate, Version: FM 14 or later

PDF Thumbnails in FileMaker 14

churchill-3“It is a riddle wrapped in a mystery inside an enigma but perhaps there is a key. The key is how you get the PDF into the container field, and which platform and FM version you use to accomplish this.” 

Winston Churchill, BBC radio address, 1939
(Just kidding about the second sentence.)

If you work with digital images, you’ve probably noticed the trend towards using PDFs as an image file format. I doubt many of use could have foreseen this happening when the PDF format was introduced in 1993, but in many organizations PDF has become the standard file format for images.

The good news is that the GetThumbnail function now works with PDFs in container fields in FileMaker 14, and in this article I’m going to suggest a couple reasons why you might find this useful, and explore some nuances between how PDFs in container fields behave on the Mac vs. on the PC.

pdf thumbnails demo

Today’s demo file, pdf thumbnails, is a 45Mb download, consisting of 32 PDF images (average size 160K, average dimensions 1160×880) as well as six stored PNG thumbnails of varying dimensions for each PDF. Continue reading “PDF Thumbnails in FileMaker 14”