by Jon Rosen <jonro@aol.com>
Manipulating Lists with FileMaker Pro
A list is a type of data, similar to text, numbers, dates, etc., but it can contain many items. Lists are one of the most powerful programming constructs, but haven't been available in FileMaker Pro. If you have ever programmed in a language that has good list manipulation commands then you know how much more easily certain programming problems can be solved. In some languages, like C or AppleScript, for example, a list may contain any combination of data types, including other lists. In FileMaker, by using the techniques in this article, a list must be a text field to work properly. So, just about anything but a graphic or a sound can be coerced into a list.
When would this be useful in FileMaker? Consider the situation when you have a found set and you want to do a series of finds on each record in the found set. However, when you do a find your current found set is replaced by the new found set. In order to do what you are trying to do, you would be forced to create a temporary file, import record IDs into it and then loop through a script in that database that calls a script in the first database. It's not that this is so difficult to do, but it is inconvenient, a little time-consuming and causes a proliferation of cross-file subscripts.
I will show you a much simpler way to do it, and it can all be done within the same file. Incidentally, using FileMaker Pro 3.0v3, we can get the nth value of a repeating field within a script by using the Get Repetition script step. But, there is no equivalent Set Repetition script step. It can only be done within a script by explicitly declaring the repetition number - which is found as the number you enter at the bottom of the dialog that's used when selecting a field to affect within ScriptMaker. If and when we can set the values of individual items in a repeating field by field value, creating lists will become even simpler and you may not need the following techniques. Until then, this can be a valuable tool for the FileMaker developer.
Earlier I mentioned that a list is a text field. It should be a global field of type text because global fields are available to the entire file. In the examples below, any field name preceded by a "•" (bullet) is a global field. I use that convention in all of my files. Aside from making them obvious, when you display fields by name, all of these global fields are displayed at the bottom of the field listing. Unlike many other databases, you can put a
"Carriage Return," or paragraph marker "¶", within a text field to get multiple lines. FileMaker recognizes that a paragraph marker also delineates words. These two items allow you to create lists: a global (text) field with multiple paragraphs. These lists can be manipulated easily using the text functions that deal with words.
Here is an example of creating our list from a found set. The records we want have already been found. We go to a layout with a single field on it, the field we will use for finding records. Then, the Copy All Records command creates a list of all Record Numbers. Copy All Records copies to the clipboard. So, all that is left to do is to paste the clipboard into our list field.
Script Step / Options Script Parameters
Go to Layout [Layout with only one field on it]
Copy All Records
Go to Layout [Layout with the •List field on it]
Paste•List
Select entire contents
Here is an example using a list within a loop:
Set Error Capture [On]
Loop
Comment [Data:Gets first item from list.]
Set Field [Find Field: Calculation [(LeftWords(•List, 1)]]
Perform Find
Comment [Data:Error Code 401 is "record not found."]
If [Calculation: Status(CurrentError)= 401]
Comment [Data:
A second list that gets the exceptions found.
This calculation may look complicated, but all
it does is to make sure that the first item on
the list isn't a "¶".]
Set Field [•Exceptions: Calculation If(•Exceptions, •Exceptions & "¶"
& LeftWords(•List, 1), LeftWords(•List, 1))]
Comment [Data:
If no record was found, we put the entry from
the list into the exception list, otherwise we
do nothing.]
Else
End If
Set Field [•List: Calculation MiddleWords(•List, 2, WordCount(•List)-1)]
Comment [Data: This removes the first item from the list. We exit the loop when the list is empty.]
Exit Loop If [Calculation •List=""]
End Loop
FileMaker's text functions are recursive. Basically, that means the field can modify itself. It starts at the 2nd word in the list and gets the rest of the list and replaces •List with that value. Therefore the 1st item on the list is simply deleted.
Let's say we are designing a database for a company that owns bookstores, and the owner wants a report that will show him which books are sold in store A, but not in Store B. How would we do this? Well, as I mentioned earlier, we would probably have to put the Bookcodes for Store A into another file and loop through the file. Using list techniques, we place the Bookcodes for Store A list field. Then we have several possibilities, depending on exactly what we want to accomplish. We could loop through the list with a counter. If we find a book from Store A that's not in Store B we could remove it from the list and place it in another list. Or, we could only remove books from the list that are in both stores. That way we end up with a list of exceptions without using a second list.
List Commands
Here are some script steps that will allow you to manipulate FileMaker lists easily:
Get first item from list:
Set Field [fieldname: Calculation LeftWords(•List, 1)]
Remove first item from list:
Set Field [•List: Calculation MiddleWords(•List, 2, WordCount(•List)-1)]
Remove last item from list:
Set Field [•List: Calculation LeftWords(•List, WordCount(•List)-1)]
Remove nth item from list:
Set Field [•List: Calculation LeftWords(•List, n-1) & "¶" & RightWords(•List, WordCount(•List)-n)]
Get nth item from list:
Set Field [fieldname: Calculation MiddleWords(•List, n, 1)]
Get last item from list:
Set Field [fieldname: Calculation RightWords(•List, 1)]
Add item to bottom of list:
Set Field [•List: Calculation •List & "¶" & item]
Add item to top of list:
Set Field [•List: Calculation item & "¶" & •List ]
Add item to nth position in list:
Set Field ]•List: Calculation LeftWords(•List, n-1) & "¶" & item & "¶" & RightWords(•List, WordCount(•List)-n+1)]
## END ##
Jon Rosen is the president of Intellitec Inc. If you would like to contact him send email to <jonro@aol.com>