[Update 7/1/17, 12 noon (PDT): 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: FM 16 – 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.
Great article, Kevin. Thanks!
“Note: as with many of the other “values” functions, FileMaker “helpfully” appends a trailing return to the results.”
That’s why I wrap most lists (and a bunch of other things) in Ray Cologon’s “Trim4” custom function. It has totally replaced the native Trim function for me, because it makes short work of compost like this.
Thanks Jonathan, I appreciate you taking the time to comment. I completely agree about Ray’s custom function. I make extensive use of it in my own work, along with several variations. Here’s a link for those who may be curious — http://www.briandunning.com/cf/166
I concur with Kevin. Thank you. First I heard of this new feature. Previous to this I had to store the values in a list including duplicates in a special table with only one record, then create a value list with this field, then capture the unique values (because only in a value list will it get rid of duplicates). Big horsepower used to get the value list data. I had to put a pause after the single field was set otherwise it might not get the data.
This is a big improvement. Thank you for pointing out this new feature as well as SortValues to us.
howard@efficiencyanalysts.com