When FM Scripts Are Faster Than PHP for Custom Web Publishing

Often it’s best not to use FileMaker scripts to perform complex operations within a Custom Web Publishing application, if only to keep all your code logic in one place, but there ARE times where using FileMaker scripts can provide a huge speed improvement over equivalent logic in PHP.  I recently worked on a project where we wanted to update roughly 500 records following a form submit.  The problem was it was taking over 30 seconds due to the massive number of commits. Every round trip to the server was costing us time, so we needed to drastically reduce the number of calls we made.  While we might have been able to use a portal (that’s another post, I guess), in this situation we decided to use a script to offload most of the work to FileMaker using a single server connection from PHP.

First, on the PHP side I made the information headed for those 500 records into a single long string with delimiters separating each row and field value. For this project I used ^|^ for a field delimiter and *|* for a row delimiter.  You can use whatever you want as delimiters but you should do your best to make it sufficiently obscure that a user won’t ever be entering data with one of your delimiters in it!

Then I sent that long string over to FileMaker, specifically to a FileMaker script that I wrote to be able to dismantle my long string and turn it into record updates.  The PHP call to do this did a Find Any, which returns one randomly-selected row, because it doesn’t matter what record context our script runs with and because that random find runs faster than a real find — and called the script with my long string as the parameter.

Inside of the FileMaker script we used

Set Variable [$array, Value:Substitute (Get (ScriptParameter) ) ; "*|*"; "¶")]

to break apart the string into return-separated values, each value representing a record we want to create.   Then later, in a loop, we used

Set Variable [$row; Value:Substitute (GetValue($array ; $counter) ; "^|^"; "¶")]

to break apart the field values within each row, so we could work through them and set them into the proper fields on the proper record.  When the script was done running, those 500 FileMaker records had been updated with the new field values sent across in the long, delimited string parameter.

Using this method we went from 2 PHP server requests for every record we wanted to update — one to find it, another to write changes to it — down to just one server request for all of them, no matter how many record updates we needed to do.  This method might not be faster for updating 2 rows, but for the hundreds of records that we were updating each time, we saw a speed improvement of almost 10x, bringing our form submit time down to around 3 seconds.   So if you find yourself with a web page submission that needs to update a lot of records and it’s running slow, consider shifting the burden of updating those records out of PHP and onto FileMaker scripts to speed things up.