FileMaker allows to pass a parameter to a script that would theoretically permit us to create one script for all columns. Unfortunately, in the particular case of sorting, this is not very helpful because the sorting key can't be a variable. FileMaker forces us to hard code the sorting key in the "Sort" script step.
So, we have to create a sort script step by hand for each sorting key (x2 if we want to have both ascendant and descendant sorting), and then attach the script to each column. Frustrating !
Fortunately, thanks to Clip Manager 3 AppleScriptability, I was able to cook a much faster solution. With my "List view columns sorter" script, all you need to do is 3 very small steps and a few seconds, and then, regardless the number of columns, you get all you columns sortable ascendingly and descendingly. You can save hours of frustrating work !
As a matter of fact I wrote that solution because I was so frustrated with the manual method that I gave up and left many columns unsortable in my database.
Now, all I have to do is select the labels and fields in layout mode, launch my script, wait a few seconds, re-position the columns (because unfortunately when pasting FileMaker moves the pasted object and there's no way to script the object position).
The only requirement is myFMbutler Clip Manager 3.0
Thinks to know about :
- I STRONGLY advise to set Filemaker to NOT save layout content automatically, so if something goes wrong you can just go to browse mode and reply no when prompted to save the layout change.
- GUI scripting must be enabled in System Preferences/Universal Access ("Enable access for assistive devices" checked)
- Enable FileMaker GUI Scripting and Auto-Paste in FileMaker Pro must be enabled in Clip Manager's preferences.
- You have to select only the labels and fields. No other elements should be selected (borders are ok)
- Number of labels and fields should be the same : you can't select 13 fields and only 12 labels
- Labels and fields must be in the same continuous order (because the script knows that the label 1 goes with field 1 because it's the first label and the first field)
- I meant to get the requirements as minimal as possible so I wrote everything in 100% pure AppleScript without requiring any OSAX. However, AppleScript is slow at parsing text, for 10 columns it can take 36 seconds to process (2Ghz intel MacBook Pro). I you want more speed, please install SATIMAGE'S Smile OSAX, with it the same 10 columns get processed in … 5 seconds !
- The default name of the sorting script that is generated is the name of the layout + "column sort". You can change it in scriptmaker afterwards
- Of course, the layout window as to be frontmost
- If you use Clip Manager 3.0 Demo version, you have to launch Clip Manager before invoking the script and make sure you already clicked "OK" on the demo version reminder.
How does the script work :
First it asks FileMaker to cut the selected objects, then that FileMaker clipboard containing all the objects description is transferred to Clip Manager that translates it to a text XML version that AppleScript can manipulate.
Then the AppleScript script will look for the labels and the fields in there particular order from left to right. It will generate the XML code of the Scriptmaker script that will allow the column to be sorted ascendingly/descendingly.
Then it will ask Clip Manager 3.0 to translate that XML to a FileMaker clipboard format and will open the Scriptmaker window and paste it in it thanks to Apple's GUI scripting.
Once that ScriptMaker script created, it will process the XML copied in the beginning to attach to the labels the FileMaker script it just created with the label name as a parameter.
Finally, that modified XML is translated back in the native FileMaker clipboard format by Clip Manager 3.0 and pasted back in the FileMaker layout by Clip Manager itself.
You have to reposition the pasted elements because I can't script that position. You should have the object info palette open before and remember the first two numbers once you have selected the elements before invoking the script. Then, once the script just pasted the new elements all you'd have to do is to report back those two numbers in the object info palette.
I left the script unlocked so you can customize it. Download it here
