Sorting Data Super Fast
Inside of FileMaker is one of the most powerful engines you could ever hope for. It's not even the native FileMaker calculation engine. It has almost two decades of optimization applied to it, and much of the world's most popular software takes full advantage of it. It's JavaScript - and you have access to it.
If you don't (yet) know JavaScript then you can't say "Oh, I just do FileMaker". Because doing so is the same as saying "Oh, I don't really care about solution speed."
In this video article and technique file, you'll find some of the most powerful code for accessing things FileMaker can't currently do and this extends well beyond the topic of sorting.
When it comes to sorting data, not records, there's no native 'filemaker' way of doing this. The solution is typically either FileMaker's custom functions, some creative trick with sorting records or using a plugin.
However, with the use of the readily available JavaScript, you can sort all kinds of things in all kinds of ways. This video will gently introduce you to using one of the tools you should certainly have in your mental toolbox!
Comments
Real World
I enjoyed this video and it's a great technique, but how can we apply it to a typical FileMaker application? There are two things I usually need to do when I sort data. I want the found set itself to be sorted by the sort field and I may have to sort on multiple fields. How can I use this technique to sort on multiple fields and how can I use it to sort a found set.
Sorting records is handled by FileMaker
A real world application of this might come in the form of presenting data within an on screen merge variable.
If the situation is as you mention in your comment, then FileMaker is going to handle sorting records based on field data. However, if you need to display some data on screen, in a sorted order, then you could use this technique and simply populate a global variable to display the data on screen. For example.
Tags: <<$$TAGS.SORTED>> would show Tags: A, B, C, D
Of course, in that simple example, you can also take advantage of the fact that FileMaker sorts value lists in alpha order. You can create a value list and simply use the Design function ValueListItems.
This technique of sorting data comes into play when you need display data sorted. It also showcases how to find the unique values of an array and when you need to do things like find the values within list A that are not within list B. You have far more tools in JavaScript than those provided by native FileMaker when it comes to list manipulations. ;)
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Example file is not working
Hello, I just downloaded the sample file and it seems does not work, I ran the data viewer and I can see the data into the variable but I dont get anything into the SORTED field.
Thanks
What operating system
We'll need more information in order to do any debugging.
-- Matt Petrowsky - ISO FileMaker Magazine Editor
I am using Windowos 8 and
I am using Windowos 8 and FM13.0v1
FileMaker 13.0v3 is required
FileMaker didn't add the dollar sign (local desktop client) support for fmp:// url until v3.
-- Matt Petrowsky - ISO FileMaker Magazine Editor
I just updated FM and now the
I just updated FM and now the example works fine!
Thanks!
There's no native 'filemaker' way of doing this ?
How about ExecuteSQL ?
ExecuteSQL ( "SELECT DISTINCT " & MYFIELD & " FROM " & MYTABLE & " ORDER BY " & MYFIELD & " ASC" ; "" ; "¶" )
Even so, JavaScript is 3x faster, I do agree on that...
Yeah, you got me on that one.
True, ExecuteSQL can do a basic sort in either Ascending or Descending. However, it can't do complex xor and not operations on arrays. That was my intention of saying FileMaker does not have the 'native' functionality.
I should have clarified that when I mean native, I'm talking about internal functions. I would expect native FileMaker support for sorting or filtering duplicates to unique to have functions like Sort ( values ; order ) and Unique ( values ).
When I consider basic list operations (array manipulations) I think in terms of other languages which do provide such functionality.
By all means, if you only need a basic sort and you don't want to add any extra stuff to your solution go with ExecuteSQL if the data set is small. The larger the data set the more speed you get out of JavaScript.
Thanks for the feedback and reminder!
-- Matt Petrowsky - ISO FileMaker Magazine Editor
File Does not Work
Hi Matt - I am very interested in this as I have 50,000 records in a solution that is very slow to sort when viewed in this view.
I have downloaded the file and unzipped it, but I get an error of "Your access privileges do not allow you to perform this action."
Here is the debugging information.
##
Use this web viewer temporarily on your layout to view debugging information. You can also append additional information as needed. The Debug function can be pushed into a global variable named $$DEBUG and then also placed on the layout via a merge variable such as <<$$DEBUG>>. With FileMaker 13+ you can also integrate this into a Popover button and hide it using the Developer custom function.
Environment
ApplicationVersion ProAdvanced 16.0.3
SystemPlatform -2
SystemVersion 10.0
ApplicationLanguage English
SystemLanguage English
HostName DESKTOP-A3CUIRU
HostApplicationVersion
SystemIPAddress 192.168.28.12
HostIPAddress
CurrentTimeStamp 10/24/2017 6:49:04 PM
CurrentHostTimeStamp 10/24/2017 6:49:04 PM
InstalledFMPlugins BaseElements;3.0.0.0;Enabled
ThemeStudio;3.14;Enabled
AllowAbortState 1
ConnectionState 0
CustomMenuSetName
FileName Sorting Data Super Fast
FileSize 1257472
FoundCount 35217
SystemNICAddress bc:5f:f4:37:d6:08
PersistentID 190F216ECD8FEACB9AF36C913B0A2765
RecordOpenCount 0
Errors
LastError 0
LastODBCError ?
User
UserCount 1
AccountName Admin
AccountPrivilegeSetName [Full Access]
CurrentPrivilegeSetName [Full Access]
AccountExtendedPrivileges fmapp
fmreauthenticate10
CurrentExtendedPrivileges fmapp
fmreauthenticate10
Layout
LayoutID 2
LayoutName Main
LayoutNumber 12
LayoutTableName Technique
LayoutViewState 0
LayoutAccess 2
LayoutTableName Technique
ActiveLayoutObjectName
ActivePortalRowNumber 0
Windows
WindowName Sorting Data
WindowMode 0
WindowOrientation -1
WindowStyle 0
WindowZoomLevel 100
WindowVisible 1
WindowTop -8
WindowLeft -8
WindowHeight 1056
WindowWidth 1936
WindowContentHeight 915
WindowContentWidth 1920
WindowDesktopHeight 1040
WindowDesktopWidth 1920
Records
RecordID 3
RecordNumber 1
RecordAccess 2
RecordModificationCount 3
RecordOpenState 0
SortState 0
TotalRecordCount 35217
Field
ActiveFieldName
ActiveFieldTableName
ActiveFieldContents
ActiveRepetitionNumber 0
ActiveSelectionStart
ActiveSelectionSize
CalculationRepetitionNumber 1
Script
ScriptName
ScriptParameter
ScriptResult
Paths
TemporaryPath /C:/Users/troyt/AppData/Local/Temp/S10/
DesktopPath /C:/Users/troyt/Desktop/
DocumentsPath /C:/Users/troyt/Documents/
FileMakerPath /C:/Program Files/FileMaker/FileMaker Pro 16 Advanced/
FilePath file:/C:/Users/troyt/Downloads/SortingDataSuperFast/Sorting Data Super Fast.fmp12
PreferencesPath /C:/Users/troyt/AppData/Local/
SystemDrive /C:/