Editor’s note: Today it’s my privilege to present an informative guest article written by Jonathan Fletcher on the elusive topic of sorted dynamic value lists. Jonathan is a well-regarded, thoughtful and thought-provoking fixture in the FileMaker community, known for his generous and frequent contributions to various online FileMaker forums.
Have you ever had a need for a value list that displayed a generated list of years in descending order? Have you ever had a client ask for a popup of dynamic characteristics or statuses in the order they are most often employed by the users?
Most FileMaker developers can remember times when they were asked to display a drop-down, pop-up or other dynamic value list in a particular sort order. And we have always had to say, “Naw, you don’t want that. Picker portals are way better!”
Inspiration!
A little over a year ago, Soliant developer Marcelo Piñeyro posted about his idea of using one of the two “Byte Order Marks” (Unicode 65279, in particular) to control sorting dynamic value lists.
The basic idea is to use a character that has little effect on the list other than forcing FileMaker to ignore its alphabeticity and display it in whatever order it’s provided. The Byte Order Mark fits the bill in that it is a zero-space, non-breaking character, totally invisible to the naked eye, yet still having a useful effect. [1][2].
For example, say you have a list of names of executives and you want them to display in the order that they appear on the org. chart:
If you fed their records to a dynamic value list in FileMaker you would get:
Hardly appropriate (“The first shall be last?”).
Using Mr. Piñeyro’s technique, we would add the BOMs to the beginning of each line of the original list (bullets represent the BOM here for illustration purposes):
•Tim
••Eddy
•••Craig
••••Jonathan
•••••Peter
••••••Dan
•••••••Phillip
••••••••Bruce
•••••••••Jeff
You have to remember to set the value list to sort by Unicode (waaaaay down at the bottom of the list of languages to sort by) but that’s it.
When you go to use this value list FileMaker sees the really high (65279!) unicode value(s) and pretty much—for all practical purposes—ignores the alpha characters.
So, how do we generate such a list in FileMaker? Well, I can already see the wheels turning in your minds, my fine-feathered FileMaker functionistas, but let’s look at a couple of approaches:
The way that Marcelo applied it was to use a sort order field and have the prepended characters calculated onto the visible value according to a manually entered ordinal [3]:
And his dialog looks like this:
His approach is excellent for when you have your data in a related table and want to store IDs but only show the value in the popup. You can read his article and get his demo here: <http://www.soliantconsulting.com/blog/2012/09/extending-filemaker-pro’s-value-list-sort-capabilities-using-char-function> to get the gist of that approach.
Hmmm…
A recent discussion I was involved in sparked a different idea, however: out of the cerebral fog began to emerge a custom function that would take any list and return it to a calculation with these strange and wonderful characters appropriately prepended to the beginning of each line. That way you could employ any type of list: a calculated list, a related field or combination of fields, a manually entered list in a preference field, or effectively any combination that can be calculated at all:
Send your list to the CF, point your value list to the result (Likely, it would be in a single-record preferences table, but it doesn’t have to be) and violá: “Hey, Martha, lookee that! A dynamic value list that isn’t alpha sorted! Have you ever seen such a thing?!”
The accompanying file [http://www.fletcherdata.com/FunWithSorts.zip] demonstrates how that might be done. The file starts off slowly, defining the problem for even the greenest viewer by setting the stage with different behaviors resulting from some everyday uses of value lists.
One useful application of the technique is immediately visible, if not readily apparent: the popup navigation field on the layout gathers the layout names and puts them in a list, in the order that the layouts are in the file.
Digging Into The File
Perusing the file, you see some applications of the idea and the results. There are three CFs that do the heavy lifting:
- Repeat: an elegant character repeat function suggested by Nils Waldherr in the comments below Marcelo’s article.
- CustomList: Agnes Barouh’s ever-useful method for generating a list of just about whatever you can write a calculation for.
- SortableList: A basic CF that I wrote to coerce the other two CFs into joining it in the creation of the required assemblages. This could definitely be made a recursive function, but I got lazy and passed on the hard work to CustomList.
The guts of the CFs are not the point here, though. You can use them as is and put them to work right now without ever attempting to divine their aura or reading their entrails.
The calculations in the file apply the technique in various (slightly) different ways, but the concept is the same:
- Create a list
- Feed it to the CF(s)
- Point the value list definition at the result
- Set the value list sort to Unicode
The original request that started the aforementioned recent conversation was about a reverse-sorted, dynamically-generated list of years, starting with the current (or other management-defined) year. Done:
An issue that is addressed in the file is the fact that the BOMs remain in the data after you select a value from the list. You can choose to leave them there if the data is for display only, or you can remove them with a trimmer auto-enter calc on the field, if you need to use the data right away:
However, if you “clean” it, you lose the ability to see any already-selected value in the pop-up upon reenter. If that bothers you, you can remove the BOM’s later, anytime before you need to use the value.
[A friend pointed out that the BOMs can be added back in with a trigger upon clicking into the field, but you have to decide if you want to go to the extra trouble for that.]
Another point to remember here—just in case you like your value lists really long—is that FileMaker only indexes, and thus sorts, the first hundred characters of a field’s contents, so the maximum number of BOMs that FileMaker will pay attention to is 100. That, in a practical sense, limits your lists to 100 values with this particular technique. That’s more than enough for normal situations, but still a good thing to know, just in case.
Sorting It Out
So, to recap: sorting in a dynamic value list is solved. You now have at least two ways to do it: Mr. Piñeyro’s related-table-two-field approach and the above single-list-custom-function approach.
Now you can tell your clients that they can have their dynamic value list and sort it, too.
j.
Footnotes:
[1] Please note that the Byte Order Mark is an essential part of some UTF-16 data types and, as such, will have a different meaning in text exported as UTF-16 (Nope, not a clue. You?). Filtering it out prior to export will likely avoid any conflicts that might cause.
[2] Some Windows users have noticed that the BOM’s show up as boxes, but others who have used this technique on Windows haven’t been able to replicate that behavior. If you happen to run across the boxes while applying this technique, please be so kind as to relay the conditions under which you saw them, so our journey to the sorted side might be made complete.
[3] Since version 10 and the advent of the “Char” function, this technique is much simpler to implement, but earlier permutations of The Platform could possibly work with the BOM character copied and pasted into a CF or global field from elsewhere. The author is unaware of definitive testing being done in earlier versions, however. (That said, it hasn’t been tested in future versions of FileMaker, either, likely owing to that little detail about being in the future!)
Jonathan Fletcher lives in Louisville, KY, with his wife and daughters, and dreams of playing a FileMaker developer on TV. He coordinates his local FileMaker developers group, and hosts the FileMaker Talk: Business Edition podcast.