FileMaker Pro : Adding sort ability to list view column automatically

2008-08-18T03:51:00.020+02:00

Making the columns in list view sortable can be very tedious because you have to attach to each label a button to which you have to affect a sorting script.

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

Filemaker Pro : Clip Manager 3.0 a must have tool !

2008-08-17T21:48:00.010+02:00

FileMaker Pro development can be tedious for the advanced developer. It's all GUI based development tools are often a burden. Fortunately, thanks to the extensive implementation of copy and paste of objects in the latest FileMaker releases, a very smart company, myFMbutler, was able to create the tool I waited for years : myFMbutler Clip Manager 3.0

Among many things, it lets you edit every FileMaker object you can copy & paste (scripts, fields, layouts…) as text !

What it does is to convert the binary XML that FileMaker generates when you copy an object to textual XML that you can then edit as you want, and then transform it back to FileMaker clipboard that you can paste back in FileMaker !

The possibilities are endless, and what would takes you hours to do, can be done in seconds !

But before the 3.0 release you couldn't script Clip Manager with AppleScript. In fact even in late 3.0 ß you couldn't script it. So I asked myFMbutler to make it AppleScriptable and in one hour after my mail, I got an alpha release with AppleScript support, and in one day a pretty much final one with many more AppleScript commands ! How awesome is that company ?

In fact, I'm so impressed and grateful to them that this is the reason why I started this blog : to show them that it wasn't a waste of time to add this AppleScript support and to show you how FileMaker Pro development can be much quicker with a bit of AppleScript and Clip Manager. And maybe we could start a community sharing scripts and tricks to speed up FileMaker Pro development.

So I hope you'll excuse me about this endorsement, but Clip Manager will be an essential tool for many tips I will post here.

Vincent's digital life

FileMaker Pro : Adding sort ability to list view column automatically

Filemaker Pro : Clip Manager 3.0 a must have tool !