A Beginner’s Guide to Working with Lists in FileMaker Pro
As you become more familiar with FileMaker Pro and make the leap from beginner to intermediate developer you will inevitably become accustomed to working with lists. By list I’m referring to a series of values separated by carriage returns such as
apple banana pear mango
The list might be referencing a series of repeating fields, some related fields from another table, or it might be a series of values that you concatenate manually to achieve the desired result. You might use your list in a script as part of a loop that iterates through the list and performs actions for each value in the list, such as creating a new record or sending an email. You might be creating a list of delimited data to use in a chart or you might be generating a list to use as a match field in a relationship (see below for more details).
List Related Functions
FileMaker Pro has a number of native calculation functions to help you generate lists and extract values from a list:
List ( field { ; field…} ) – the list function was originally introduced with FileMaker Pro 8.5 (there hasn’t been a .5 release since) and as it’s name suggests its sole purpose is to generate a list of values.
The field reference can be any related field, repeating field, or set of non-repeating fields; an expression that returns a field, repeating field, or a set of non-repeating fields, or a variable. For example you might wish to generate a list of all the Invoice Numbers for a particular Contact record. Assuming you have a table occurrence named Invoices that is related to Contacts by a Contact ID match field the following calculation:
List ( Invoices::InvoiceNumber )
would return the list of Invoice Numbers for the current customer record. You can mix and match field references, variables and literal strings – for example:
List ( "INV1056" ; Invoices::InvoiceNumber ; $invoiceID )
It’s important to note that the List function ignores blank values – List ( “apple” ; “” ; “banana” ) would return:
apple banana
ValueCount – this returns a count of the total number of values in specified list. For example ValueCount ( List ( “apple” ; “banana” ; “orange” ) ) returns 3.
LeftValues ( text; numberOfValues) – this returns the first numberOfValues from the specified list. N.B. the resulting list of values returned always ends with a carriage return. For example LeftValues ( List ( “apple” ; “banana” ; “orange” ) ; 1 ) returns:
apple¶
RightValues ( text; numberOfValues ) – this returns the last numberOfValues from the specified list. N.B. the resulting list of values returned always ends with a carriage return. For example RightValues ( List ( “apple” ; “banana” ; “orange” ) ; 1 ) returns:
orange¶
MiddleValues( text ; startingValue ; numberOfValues) – this returns the middle numberOfValues from the specified list, starting at startingValue. N.B. the resulting list of values returned always ends with a carriage return. For example MiddleValues ( List ( “apple” ; “banana” ; “orange” ) ; 2 ; 2 ) returns:
banana orange¶
FilterValues ( textToFilter ; filterValues ) – this returns all the items in textToFilter that are also in filterValues, in the order they were originally entered in textToFilter. This allows you to compare two lists to find the values they both have in common. N.B. the resulting list of values returned always ends with a carriage return. For example FilterValues ( List ( “apple” ; “banana” ; “orange” ) ; “banana¶orange” ) returns:
banana orange¶
GetValue ( listOfValues ; valueNumber ) – this allows you to retrieve a specified value from a list of values, typically used when you are looping through a list and performing some actions for each value in the list. N.B. the value returned does NOT end with a carriage return. For example GetValue ( List ( “apple” ; “banana” ; “orange” ) ; 2 )returns:
banana
Multi-Key fields
One of the more common uses for lists mentioned above was for use as a match field in a relationship. Most relationships typically use a single field/value on both sides of the relationship. For example the Company ID field in the Companies table (primary key) matches the Company ID field in the Users table (foreign key). FileMaker Pro also lets you enter mutliple values into the same field which has the effect of increasing the number of possible matching values on the other side of the relationship. You simply enter the values separated by carriage returns and FileMaker will look for matches for every line/value you have entered.
If you entered the following values into a text field (you would typically use a text field with global storage):
C1045 C2784 C3698 C1077
FileMaker Pro will then look for any matching records for each of these 4 values. Multi-key fields can be a very powerful feature of FileMaker Pro and are also often referred to as multi-line keys.
Generating a list of values from a field in a found set of records
One of my favourite new features of FileMaker Pro v13 was the new “List of” option when creating a Summary field. This option creates a return-delimited list of non-blank values in specified field for the current found set of records. One obvious purpose of this function is to create a list to use for a Multi-key field – you can’t use a Summary field itself as the match field in a relationship but you can use the result it generates in a global or calculation field.
I would also recommend reading the fileMakerhacks series on using Summary list fields in relationships.
Custom Functions and Plug-in Functions
There will be times where you need to manipulate lists and go beyond the native FileMaker functions – for example you might want to compare two lists and return all the values that are different in both lists, sort the list of values, or you might want to remove any duplicate values from a list. Here’s some of the custom functions and external plug-in functions that I use on a regular basis:
SortList ( ValueList ; Sort ; Type ) – this custom function allows you to sort a list with options for ascending or descending order
XORvalues ( ListA ; ListB ) – this custom function returns all lines that are different in two lists. If you only specify one list it will also eliminate duplicates from the list and return a single instance of each value
ZapValues ( ListA ; ListB ) – this custom function will compare two lists and remove the items in List B from List A (this is the inverse of the native FilterValues function)
Trim4 ( text ) – if you are using the native functions that also return a trailing carriage return you can use this custom function to remove this at the same time
AddRemoveListItem ( theList ; value ) – this custom function will add an item to a list if it does not already exist; otherwise the item is removed from the list
There will be times when you are not able to use a custom function due to the limitations of custom functions – recursive custom functions are limited to either 10,000 interactions or 50,000 iterations depending on the style of recursion they use (this article has more details on the limitations). When you hit these limitations you will need to use a plug-in function which are not bound by the same limitations as custom functions. Databuzz is a proud sponsor of the open source BaseElements plug-in which has a number of handy list related functions including:
BE_Values_Unique ( listOfValues {; caseSensitive } ) – this removes any duplicate values from the specified list
BE_Values_Sort ( listOfValues ) – this sorts the list in alphanumeric order
BE_Values_FilterOut ( textToFilter ; filterValues {; caseSensitive } ) – similar to the ZapValues custom function, this will compare two lists and remove items from the textToFilter list that are found in the filterValues list
(N.B. all references are to FileMaker Pro v14 which was the current shipping version at the time this article was written)
