« Fetch 5.8 released | Home | MBS Xojo Plugins, ver… »

Generate sort script automatically


Recently we had the issue that we would like to allow users to sort data in FileMaker without using the sort dialog. In the user interface we have buttons to click for the user to sort table by one of the fields. You can think about a button bar with the field names where it makes sense to sort by.

The button calls a script and passes the field name as parameter. As FileMaker doesn't allow you to script the sort command manually, we need to generate a script with a lot of combinations. Today we want to show you a sample database for generating sort scripts automatically.

You open both databases, your's and the Create Sort Script.fmp12 file from us. You enter your file name in the field and the name of the table. The first button uses FM.ExecuteFileSQL function in MBS Plugin to query the field list without summary and global fields.

The second button now creates XML based on three templates. This XML is the same as if you would copy the XML of such a script in FileMaker's script workspace. Once you use the third button to copy the script lines via Clipboard.SetFileMakerData, you can copy them into a new script in your database. The script looks like this:

The key is that we pre-populate the field selected for sorting in the sort script step. As you see the script may have hundreds of lines for all combinations. In a solution you may add an option for ascending/descending and double the script lines. You may also limit field list and then do a first and second field for sorting.

The example database will be included with next plugin prerelease and works fine here on MacOS and Windows.
16 12 19 - 15:56