FilterValues patterns and list manipulation

Expert

Use FilterValues(), SortValues(), and UniqueValues() to filter, sort, and deduplicate return-delimited value lists in FileMaker 19.2+ calculations.

What you'll learn

How FilterValues() selects matching items from a list
How UniqueValues() removes duplicates from a list
How SortValues() sorts a list alphabetically or numerically
How to combine these functions for multi-step list processing

FileMaker 19.2 introduced FilterValues(), SortValues(), and UniqueValues() -- three functions that dramatically simplify list manipulation. Before these functions, developers needed recursive custom functions or While() loops to filter and deduplicate lists. Now these operations are single function calls.

1/4

FilterValues(): keep only matching items

FilterValues(list; filterList) returns only the values from list that also appear in filterList. Think of it as an intersection: items that are in both lists.

FileMaker Script

// Keep only the statuses that are in the "allowed" set
Let ( [
  statuses = List ( Contacts::Status ) ;   // e.g., "Active|Pending|Archived|Active"
  allowed  = "Active|Pending"
] ;
  FilterValues ( statuses ; allowed )
)
// -> "Active|Pending|Active"  (Archived removed)

// Count active contacts:
ValueCount ( FilterValues ( List ( Contacts::Status ) ; "Active" ) )

SQL aggregation in FileMaker calculations Compound key calculations