Recently, in a virtual list report, I had a need to sort on rep number 2 of a repeating field called cell_text_r.
FileMaker does not offer an obvious way to script a sort on a field rep > 1, and I had worked around the problem in the past by…
A. Defining some special non-repeating fields in my virtual list table like so
B. Populating a variable, for example $vText1 with “cell_text_r[2]”, and then
C. Scripting my sort on virtual_text_1.
Does it work? Yep. Does it seem needlessly clunky? Yep.
At any rate, it turns out the above is not necessary (unless one is planning to sort on more than one repetition at the same time). To script a sort on a field rep > 1…
1. Place it on a layout.
2. Return to browse mode, and if in table view, manually sort on the field like so…
…otherwise, control- (on Mac) or right- (on PC) clicking inside the field to display a context menu and sort accordingly.
3. Add the “Sort Records” step to the script, making sure “Specify sort order” is checked.
Long-time FileMaker users will recognize this trick from the old days when all scripted sorts needed to be “memorized” in a similar manner.
Nice tip, weird how they support sorting by a field rep but don’t support setting it via the GUI.
very cool indeed, thanks Kevin!
This is really interesting, and I’m excited by the ability to skip having extra sort fields.
I thought I’d see if I could add a script to my FmClipTools to allow you to copy a Sort Records script step set to repetition #1, then it would let you pick a different repetition, put that modified step into the clipboard, so you could then paste it back into your script.
But, I found bad news.
At least in FileMaker Pro 21, if you copy a Sort Records script step that has the repetition specified, that repetition is NOT included in the clipboard data.
That means if you copy/paste a script step that was using repetition #2, the pasted step will be using repetition #1. So copy/paste is out, and thus so is FmClipTools.
If you duplicate an existing step, the specified repetition is preserved.
But, this could be dangerous, since if you ever move a block of code by doing a copy/delete/paste, the Sort Records step will drop the repetition you wanted.
This definitely seems like a bug: copy/paste should maintain the integrity of the object copied. But, I’m not sure it’s worth reporting to Claris – they didn’t even provide an obvious way to set the repetition in the sort records dialog and repeating fields are somewhat disfavored, so I bet this would be very low on their priority list.