Databases for research header image

Problems with FileMaker Pro’s export command and some workarounds

FileMaker Pro’s export command and its script step do what they must do with plenty of options (CSV, tab-text, xlsx, FMP, xml and more; and many possible encodings for both Mac and Windows). However, on the programability and user experience side, one could wish for more. But for some of these problems workarounds are possible.

Problem : low programmability of field selection

My biggest problem with the ‘export records’ script step is that field selection can only be done at programming time, not at deployment time. Unless one invests a lot of development effort ( I will get back to this later ) the user can only be given a lot of flexibility by setting the ‘With dialog’ option to ‘on’.

Problem : opening the entire list of tables and fields to the user.

The problem with this option (as with the ‘Export records’ menu item) is that it opens up the belly of the beast to the user: suddenly he/she is confronted not just with the fields that might make sense to them but also with all the other fields that are necessary for the solution to work. This is not just awkward and problematic for the user, one may also give away trade secrets to the development work because not only the current table becomes visible but all other tables and their fields as well.

Problem : column headings

The last major problem concerns the field names that are presented in the export’s first row when FMP exports data to xlsx format (and possibly others that I am not familiar with). In principle it is of course a good thing that they are exported but when it come to data from related tables, FMP’s naming conventions of ‘table_occurrence::field_name’ does not always make sense to the users. The more so since those names usually are not given with the user in mind.

Existing workarounds

Clickworks, field names in the field comments

I googled this problem and sure enough, there are a few solutions out there. For example Jeroen Aarts and Jan Stieperaere at Clickworks present a solution using an additional table especially for the export of data. This is what I originally had in mind as well. I think their solution is overly complex because they use a custom function that relies on a field name stored in the comments of a field definition. (sound cryptic, but have a look at their demo file). Why not simply hard code the calculation? The only reason I can come up with is that it is less work to add additional fields to the ExportTable (you guessed it, it is the name of the export table that they use). The custom function does add additional time during the export. Hard coding takes more coding work but will result in faster export.

In their presentation, Jeroen and Jan leave it to the reader how to populate the ExportTable with the right keys and suggest importing or using a virtual list technique. My solution would be to populate the table long in advance with keys that will serve for a long time in the future. That is part of the virtual list technique, but then do the find in the original table and then use ‘go to related record’ script step, which is blazing fast compared to an import.

Notice that the Clickworks solution doubles all the relationships needed for an export. In a simple file, like their demo, that does not seem a lot of work. However, with more complex databases, it may be prohibitively much work. Instead, I would connect an export version to each table that needs exporting and set up a 1:1 relationship between them. It creates less extra tables. On the other hand, it will create more additional records but that maybe an acceptable price. But the benefit will be no doubling of relationships and checking whether the doubles are set up in exactly the same way as the originals.

Kevin Frank and Beverly Voth, using xml to format a .csv export

At the FileMaker Hacks blog, Kevin Frank and Beverly Voth explain a method where they use xml and XSLT to format an exportas a .xsl file. I am not even a beginner in xml so, for me it is difficult to follow the details, let alone come up with a critical evaluation.

Fortunately, Beverly talks us through a step-by-step demo in a FileMaker Hacks post at https://filemakerhacks.com/2017/11/05/excel-exports-using-xml-and-xslt/ . If you want to learn more about the use of xml and XSLT in FileMaker, Beverly has written a whole book about it. FileMaker Pro 6 Developer’s Guide to XML/XSL (Port of the Wordware Library for Filemaker) The book is from 2003 but I am sure that much, if not all is still applicable today. I am seriously thinking about ordering it to be sent to Costa Rica and drag it with me this year, until I am back in Sweden.

0 Record

This is very simple solution, that every FileMaker rookie might come up with and which is probably documented by others elsewhere. This is just my version. The solution works but it has a record-set integrity downside if you will.

It works like this. Before the export of a found set, you add a record and sort the set so that this record becomes the very first record, hence the name, ‘0 record’. You populate the fields of this record with the field names as you prefer them to appear in the export. Then you do the export and then you remove the 0 record again.

Sounds simple enough, but there are a few details that need attention: 1) getting rid of the actual header row; 2) Allowing the user to control the path and filename and 3) How to create the 0 record. I will discuss these point in the following sections.

Getting rid of the actual header row

First of all, FileMaker will not consider the 0 record to be the header record and it may/will still add the actual header row with the unwanted technical field names. You have two options here.

The first option is to leave it to the user and run the ‘export records’ script step ‘With dialog’ set to ‘on’. The user will then at some point see this dialogue where he/she can uncheck ‘Use field names as column names in first row’. This will work, but you will have to somehow instruct the user to do so. And they probably do not read the manual – and you may not have written it anyways. So, I would opt for option 2.

In the second option, you set ‘With dialog’ to ‘off’. Then click the settings icon at the script step and click the top ‘Specify’ button. You end up in the ‘Specify Output File’ dialogue.

• If you want to give the user the option to specify a location (but not a name) then enter a variable name in the text box. Otherwise select a specific location and filename, which the user then will not be able specify.

• Make sure you select the File Type ‘Excel Workbooks (.xlsx)’. After you do so, you will end up in the ‘Excel Options’ dialogue where you uncheck the ‘Use field names’ option.

• Click OK and OK again to return to the script editor.

• Don’t forget to specify the export order as you need it.

Now you got rid of the actual header row which makes the 0 row the first thing to occur in the export. This solution created however a small new problem, which is that the user cannot determine where the export will be made and what the filename will be. This is the topic of the next sub-section.

Allowing the user to control the path and filename

In the previous section we set the ‘File path list’ in the ‘Specify Output File’ dialogue to the variable $file_and_path. To allow the user to control the path and filename, you need to design an interface for that. You can ask for the file name through a ‘Show custom dialogue’ or ask the user to enter the name in some field. In fact, I would leave this out completely because this step and the next are two dialogues that feel clumsy together. Since I think the user will care more about the location of the export than the name (that is, I care more about the location than the name), I would programmatically determine a name but let the user choose the location. This would definitely be the preferred choice when you want to export more than one file, but in the end it is up to you, of course.

That leaves only the location. The ‘Get Directory’ script step comes in really handy. The script step is available since FMP 14.0, and opens a dialogue box in which the user can select a target directory. You can specify a variable that will contain the selected path. It also offers the opportunity to specify a dialogue title, but that title does not appear when I run the script step on my FMP 16 copy on a Mac.

Before you can use the path it needs to be preceded by the right prefix for either Mac or Windows machines. For Webdirect and iOS the export works completely different and you don’t have the possibility to choose the export folder.

All can come together in a ‘Set Variable’ script step for $file_and_path (I guess $path_and_file would have been a better name) with the following calculation or something similar.

Case (

	$system_platform = 1 ; // intel-based Macs

		"filemac:" & $path_from_get_directory ;

	$system_platform = -2 ; // Windows

		"filewin:" & $path_from_get_directory ;

	""

)

&

your_table::user_chosen_file_name & ".xlsx"

How to create the 0 record?

To create the 0 record, I see two options. First is, you create one just before each export and delete it immediately after. The second is you create one the permanently lives in the export table.

The second option has the advantage that you can easily create it as the 0 record. In a table with a unique record number key field, it can literally have the number 0 from the very start. For the export you do a find to find the relevant records, then expand the find to find the 0 record, or do the two steps in one find. After that you can omit it again from the found set if necessary.

The first option takes a bit more programming because you need to create the record and populate the fields with the right names. You might still need to fiddle with the find before the export and you need to delete it afterwards. The upside of this method is that you only need to deal with the 0 record during the export. That is quite an upside in most situations, which brings me to the downsides of the method. Well, I see only one, but it is a considerable one.

What is the downside of the 0 record technique?

Having a 0 record in your data table is not to be taken too lightly. Especially if you’ve opted to make it a permanent one.

You will constantly need to keep it out of sight. The user may for example do a search and suddenly be confronted with a record that she/he had not created. So you will need to programmatically add a search criterion to specifically omit the 0 record. It may pop up in portals where you had not expected it. So you will need to adjust all the relationships with an extra criterion to make sure you keep it out.

The record may also create weird problems because it will contain text in all its fields. Even if the field type is not text, but number, date or time.

In short it is a constant itch that you need to address in your code. Unless, of course by some inherent quality of your design, it automatically stays out of everybody’s way. It is possible but not likely.

If you use the option of creating and deleting the 0 record right before and after the export, you may still have to be aware of this itch. Especially in a multi user application and especially especially when there are many users working at the same time. The 0 record might exist long enough to be causing problems to other users.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.