When Is A List Useful?
There are a ton of uses for lists in FileMaker apps. A common example is when you're using a picker to select records. Pickers are often used for tasks such as selecting products to add to an invoice or selecting purchase orders to email to vendors. Clicking on the record in the picker adds the primary key of that record to the list. Once the list is built, we can loop through the selected values, doing whatever task is required. But as developers, we also need to allow the user to deselect a record in case they clicked it by mistake.
The Challenges
The challenges with manipulating a list are:
- Ensuring we don't get additional carriage returns or empty rows. These will either mess up our list processing functions, or we'll have to error trap for empty rows.
- Ensuring that when removing values, only the exact value is removed from the list. This is less of an issue with UUIDs but poses more of a problem if your list is built of serial numbers.
The Custom Function
The advantage of a custom function is that you can place all your code in one place and then call that function in a calculation, feeding it any required parameters from the context from which it is being called. And if any updates to that code need to be made, you can make those changes in just one place.
Our custom function looks like this:
_AddToRemoveFromExistingList ( selected_record ; existing_list )
We prepend our custom functions with an underscore so that we know it's a custom function and they sort to the top, but it's not necessary.
As you can see, when we call this function, we feed it the selected record (expected to be a unique ID) and the list we're building (which may be held in a global variable, a global field, or potentially elsewhere). We could use this in a Set Variable step:
Set Variable [ $$my_list ; Value: _AddToRemoveFromExistingList ( $record_id ; $$my_list ) ]
Breaking Down The Code
We can break down the code for this custom function into two sections:
Defining The Let Variables
The first thing we do is define a bunch of Let variables that we can then use in a Case statement.
// Take the incoming parameter for the record clicked on and assign to a Let variable ~selected_record = selected_record ; // Take the incoming parameter for the existing list and assign to a Let variable ~existing_list = existing_list ; // The above two lines allow for this code to be run outside of the custom function with little adjustment - if running code directly in Set Variable, for example, just assign the Let variables above directly with values // Determine if the selected record is already in the existing list - add carriage returns because we want this exact value (not always necessary for UUIDs but is for serial numbers) ~value_in_existing_list = PatternCount ( ¶ & ~existing_list & ¶ ; ¶ & ~selected_record & ¶ ) ; // Determine how many values exist in the existing list ~number_values_in_list = ValueCount ( ~existing_list ) ; // Determine the position of the value in the existing list // There is no native way in FileMaker to determine the position/row number of a value in a return delimited list, so therefore we must use a custom function to determine this ~position_in_list = See full custom function for options
The Case Statement
Now that we have defined our variables, we can use them in a Case statement to determine what we should do with the value passed into the function:
// If the value is not in the list then add it, else remove it from the list Case ( // Not in list and first value being added to list - list is empty so set list to be equal to the value ~value_in_existing_list = 0 and ~number_values_in_list = 0 ; ~selected_record ; // Not in list and list has other values already - other values exist so add a carriage return and the value to the existing list ~value_in_existing_list = 0 and ~number_values_in_list > 0 ; ~existing_list & "¶" & ~selected_record ; // Already in list and only answer in list - set the list to be an empty string ~value_in_existing_list = 1 and ~number_values_in_list = 1 ; "" ; // Already in list and first answer in list - replace the value and following carriage return with empty string ~value_in_existing_list = 1 and ~position_in_list = 1 ; Substitute ( "¶" & ~existing_list ; "¶" & ~selected_record & "¶" ; "" ) ; // Already in list and last answer in list - replace the value and preceding carriage return with an empty string ~value_in_existing_list = 1 and ~position_in_list = ~number_values_in_list ; Substitute ( ~existing_list & "¶" ; "¶" & ~selected_record & "¶" ; "" ) ; // Already in list and more than one answer in list - replace the value, preceding and following carriage return with a carriage return ~value_in_existing_list = 1 and ~number_values_in_list > 1 ; Substitute ( ~existing_list ; "¶" & ~selected_record & "¶" ; "¶" ) ; "" )
Summary
You can download a sample file with the full custom function and demo here. The easiest way to get this into your FileMaker file is to select the function in the Custom Function list and hit Ctrl-C or Cmd-C. Then, open your file and go to the Custom Function list and paste using Ctrl-V or Cmd-V.
We hope this helps you manage your lists. As always, if you'd like additional help with any of this, please don't hesitate to contact us.
