A funny thing happened while I was stumped over the question of how to allow one of our clients to select and deselect multiple items in a list. Multiple people would be using the list at the same time. I was struck by the fact that I’ve been making something more complicated than it needs to be for as long as I’ve been working with Filemaker. After toying with various methods which included adding fields to the records in the list, join tables, global fields and horribly convoluted scripting it occurred to me that you can actually write a relatively simple custom function to handle it all. It will add or subtract a value from a list that is stored in a global variable and then simply do some conditional formatting to highlight the selected items in the list. No additional fields or scripting are needed, the global variable is session specific and once you are done you’ve got your list of data ready to go as is.
Here are all the components of this process and how they fit together:
AddOrRemoveFromList(list, value) – The custom function that will maintain the values in the list for you.
$$selectedItems – The global variable that stores the list I want to operate against.
Selected Item – The actual record that you are adding to the list.
First I’d like to explain the custom function. Basically, that function checks a list that you the value you pass in. If the function finds that value, it removes the value and returns the new list sans value. If it doesn’t find the value, it returns to you the list with the value appended to the end of it. Here is the what AddOrRemoveFromList(value, theList) contains:
[newList = Substitute(¶ & theList & ¶, ¶ & value & ¶, ¶)];
if( PatternCount( ¶ & theList & ¶; ¶ & value & ¶) = 1 ; Middle(newList, 2, length(newList) – 2); theList & if(not isempty(theList); ¶) & value)
The reason for all those extra ¶’s is for encapsulation and searching. When you think about what a list actually is, it turns into this: apple¶orange¶pear¶applejuice. If you want to search that list for apple right now you would be editing both the values apple and applejuice. To get around that we put an extra ¶ before the first entry in the list and after the last entry so our list now looks like ¶apple¶orange¶pear¶applejuice¶. So, if we now search for ¶apple¶ we would only be working with the actual apple value. To get the correct list format returned after that you just need to use the middle function to strip the first and last ¶ from the list.
So, we now have a custom function that will handle adding and removing values from a list. The only thing we have to do to make a selection is to say where we want to store the list. Using a global variable has the advantage of being easily accessible and being completely separated from your data structure, so you don’t need any additional fields or records to keep track of what the user wants selected. So, if you choose to use a global variable, the only step you need to take is to set the variable $$selectedItems using the custom function. Here’s what that step looks like:
If you are unfamiliar with our parameter passing system, please take a look at this. So, we now have the selected items being added to the desired list automatically. So, let’s let the user see what they’ve selected. Using conditional formatting we’ll just apply that same list checking we used in the custom function. The formula should be:
PatternCount( ¶ & $$selectedItems & ¶; ¶ & item & ¶) = 1
“item” in the above example should be whatever you passed into the list. No