Level: Intermediate, Version: FM 16 or later

FM 16: SortValues and UniqueValues

[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).

2017-06-30_224120

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”:

2017-06-30_192146

(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:

2017-06-30_233807

When the user makes a choice from the popup menu, this script is triggered:

2017-07-01_110646

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:

2017-06-30_233515

And this trigger has been applied at the layout level.

2017-06-30_234659

Finally, you can experiment with the various “datatype” settings manually, by clicking these buttons…

2017-06-30_235311

…and Unique Entries will update accordingly.

Note: as with many of the other “values” functions, FileMaker “helpfully” appends a trailing return to the results.

3 thoughts on “FM 16: SortValues and UniqueValues”

  1. 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.

    1. 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

  2. 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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.