dotfmp Developer Challenge

At recent dotfmp conference in Berlin (did I already mention this is the best FileMaker conference I know?), a developer challenge was orgnised.

The challenge was to find the fastest way to download data from a FileMaker hosted database to a FileMaker Pro Client over the network.

The table had 50K records (10 fields) of which only 10K had to be downloaded.

But the important thing was that data, once on the client, had to be in any structured, workable, searchable form, not necessarily records/fields.

@AndriesHeylen and I came up with a solution that I believe was considered interesting by the audience, so I’ll share it here, not to say that this is the best way or that I would necessarily use it in production, but I think it’s valid and it’s a good opportunity to visit some nice tricks.

I’ll write this post in a kind of story-telling style, because I think the path we followed was as interesting as the solution, if not more.

To rephrase the challenge, here is what needed to be done:

1. select 10 000 records among 50 000 (any would do)
2. transform the data stored in a table into something more appropriate for the next steps
3. make the data transit over the network from the server to the client
4. ‘render’ this data as a structured array, if that was not already the case, depending on what we would do during step 2.

For each of this tasks we had many options in front of us. So because we hadn’t the time to explore them all (oh yes, I forgot to mention that we had approximately 45 minutes to solve this… while eating in a nice Vietnamese restaurant on Kastanienallee)

So our first approach was to brainstorm about each step independently, so we would only see later how our favorite (supposedly fastest) techniques would combine nicely.

Before we begin, let’s mention that there was one ‘obvious’ solution that was doing the whole thing: declaring a variable on the client, using ExecuteSQL function.
ExecuteSQL ( "SELECT * FROM Datset FETCH FIRST 10000 ROWS ONLY" ; "#F#" ; "#R#" )
Where #F# is a custom field separator and #R# a record separator.
We didn’t follow that path because… there was no fun, and we still had 44 minutes to go.

For step 1, selecting the records, we thought about 2 different ways:

• through the found set
• through a query

A SQL query could be used in the context of an ODBC import (Import records from ODBC), but there’s a no go for this: the only output of Import records is… creating/updating records. We knew that this would take too long, so this ‘easy to solve’ first step showed us immediately that our strategy was not perfect.

Before we could brainstorm on each step independently, we needed to exlude ways that we knew for sure would slow down the process.

We identified 3 potential bottlenecks that we wanted to avoid

• A – pre-formatting the data on the server using unstored calculation. Even with a simple calc this would be expensive, and it would make the solution not scalabale.
• B – parsing data on the client, including creating records: data had to be ready to use or should not need more than a global transformation, not a per record or per field one.
• C – sending a long text string over the network: we assumed it would take longer than if the string was encapsulated in a file

Given this, we browsed the 4 steps

1 – Select the 10K records

To select the 10K records we could only use the foundset since the SQL Query could happen only in Import Records, and we didn’t want to import records (B)
So we would run a script like:
Go to layout [ Data ]
Show all records
Go to record [ First ]
Omit multiple [ 10000 ]
Show omitted only

2 – Export records

To avoid A and C, we thought the best way was to Export records to a text file (CSV)

This made it clear that the main process (including step 1) should be a server side script because the export would be much faster on the server.

We chose CSV because we believe this is the fastest, but we knew there was a glitch there: the result would not be very easy to use because the flavor of CSV FileMaker uses for its exports, using comas, double quotes and separator for multiline contents is not easy to parse using FileMaker calculation functions (certainly doable, but not easy)

So we thought that if we had time (there was still 25 minutes to go), we would use XML/XSLT instead. It wouldn’t make a big difference in terms of performance.

3 – Make the data transit over the network

What we know (or think we know, which are two different things) is that sending a long string as a script result would take long. So we wanted to return a text file instead (C). Now that I’m writing this post and that I have much more time, I wonder if we shouldn’t have simply returned the text file as a script result (script results, as script parameters or variables can be of type container). But while on a hurry we went for something different: we created a ‘transfers’ table with a container field and an indexed ID field (serial number)

So after exporting (2) our process:

• creates a new record in ‘transfers’
• inserts the file into the container (using Insert from URL and the file protocol, because Insert File is not server compatible)
• exits the server side script with the ID of the transfer record as a result (token)
• the calling (client side) script gets the token using Get ( ScriptResult )
• goes to the transfers layout
• finds the transfer record

I should mention that inserting a file using insert from URL ate a full 5 minute of our time, therefore ruining our hopes to order desert. This was due to a change in FileMaker 16 that we had forgotten: to convert the path to where we export the records to into a URL from which we can insert the file, we have to remove the system drive. But since version 16, Get ( SystemDrive ) returns empty on FileMaker Server on macOS, so we have to extract it from the Temporary Folder path.

4 – Extract the data into a variable

OK, so now the client has a text file. It’s stored in a container field of the current record, but it could have been available as a script result.

What we now have to do is to read this text file and ‘extract’ its content into a string.

To do this we simply use this not so simple calculation :

Base64Decode ( Base64Encode ( transfers::file ))

In fact we could also use simply TextDecode ( transfers::file ; "UTF-8" ), but it only occurred to me later on. And as a matter of fact, we had several questions about the former expression while presenting our solution, so I’ll try to give a bit of explanation.

Base64 is a encoding algorithm that allows encoding any data. This is very much used to embed binary files in a text, like for example an image in a html e-mail when you don’t want the image visualisation to rely on an internet connection. It is not an encryption method, although a base 64 encoded text is not readable by a human being (that I know, that is to say).

So basically Base64Encode and Base64Decode are two inverse functions, which means that

Base64Decode ( Base64Encode ( "abc")) = "abc"

But it’s not as easy as this and I’ll try to give a clearer explanation that the one I gave when I was asked in Berlin.

In this process, a text can be in 3 different states:

• 1⃣ a string (data of type text in FileMaker)
• 2⃣ Base64 encoded text
• 3⃣ encapsulated in a text file

Base64Encode ( data ) converts a string or a file to a Base64 encoded text depending on the parameter it receives (1⃣➡2⃣ or 3⃣➡2⃣)
Base64Decode ( text {; fileNameWithExtension } ) converts a Base64 encoded text to a string (2⃣➡1⃣) or to a file (2⃣➡3⃣) depending on whether you pass a filename or not.
So the two functions are inverse, but on two different operations.

In our example, Base64Encode translates a file into Base64 (3⃣➡2⃣) and Base64Decode translates the base64 encoded string into a string (2⃣➡1⃣).

But again, TextDecode (released with FileMaker 16) is the best option.

That was it, we had 4 minutes left, so just the time to wrap all this in 2 scripts (client side and server side), and add some performance measurement using get ( CurrentTimeUTCMilliseconds ) while paying the restaurant bill, fold the mac and head to the conference room where the solution had to be presented.

Running on a local machine (with FileMaker Server and Pro Advanced on the same computer), the whole process was taking 0.4 seconds. On a local network (WIFI, quite busy and unstable, it took between 0.6 and 0.8 seconds). Not bad.

Please do not hesitate to share your ideas or leave a simple comment below.

Download the FileMaker file here: dotFMPcdotFMPchallenge (Admin / 1234)