[Update 1 July 2017: demo file has been updated to make the found set tracking more robust; article text has been revised to reflect this.]
Today we’re going to look at two functions introduced in FileMaker 16: SortValues and UniqueValues, and you may want to download and follow along in this demo file: SortValues & UniqueValues (requires FM 16 or later).
Each of these functions operates on a return-delimited list of values, and the names are self explanatory: SortValues allows you to intelligently sort the list in ascending or descending order, and UniqueValues removes duplicate entries.
I’ll have more to say on this “intelligent” sort capability below, but first let’s take a look at the demo, the purpose of which is to provide a point-and-click sandbox to explore the behavior of these functions.
The demo consists of 50K records and the following field types: number (ID), text (Company, City, State, Zip), date, time and timestamp… in the case of these last three, the field names are the same as the field type.
To see it in action, locate a found set, e.g., City = Los Angeles, choose a field from the popup menu, and you will see unique entries for the found set reflected in the scrollable Unique Entries field, and sorted in ascending order (this is basically the same concept we looked at a few years ago in FM 13 Pseudo Indexing).
The SortValues function takes three arguments, the first being required and the second two being optional.
SortValues (values {;datatype;locale} )
Leaving “datatype” empty will give you an ascending text sort, which may or may not be what you actually want. However, today’s demo uses the FieldType function to automatically determine which of these five criteria to use as the “datatype”:
(The “locale” argument is beyond the scope of today’s article — most of the time you can leave it empty. See the online help entry for more info.)
Okay let’s dig a little deeper. Here are field definitions:
When the user makes a choice from the popup menu, this script is triggered:
The demo attempts to ensure that the values in Unique Entries always correspond to the current found set, e.g., a custom menu set has been implemented to repoint “Show All Records”, “Show Omitted”, “Omit Record” and “Omit Multiple” like so:
And this trigger has been applied at the layout level.
Finally, you can experiment with the various “datatype” settings manually, by clicking these buttons…
…and Unique Entries will update accordingly.
Note: as with many of the other “values” functions, FileMaker “helpfully” appends a trailing return to the results.