If you have spent any time developing in FileMaker, or programming in almost any language, you will be familiar with the concept of a loop. And when scripting your loops you probably have a favourite method that you use. This article presents and compare a number of looping techniques that can be used in FileMaker.
To help illustrate the looping techniques we will use the following example. Let’s assume that our solution is a simple CRM which has a Contacts table. The solution also has a letter writing screen where we can compose letters (the things people used before email). To make the best use of this facility, it should be able create a letter, and print a personalised version for each contact, including their name, address and so forth.
I won’t go into the detail of how this merge function would be implemented, but in order to print these personalised letters we require three important things:
• A “Letters” table to store the lettersRecords for each individual letter
• A field to act as a link between the letter and a contact record (Contact ID).
Now let’s think about how the letter might be composed. The most basic implementation would be a global text field to contain the letter currently being composed. So on our curent table which we’ll call “Home”, we create this global field. We also need to figure out who our recipients are going to be. We’re going to assume that whenever we write a letter, we wish to send it to all of our contacts.
Now to put things into perspective and give some idea of what’s going on. In the letter, we use special merge items which act as place holders for the actual contact information that will be substituted in later on. A classic example of this would be using a merge item as a place holder for a contacts name, ie:
Dear **First Name**
Later on, we would want to swap **First Name** with an actual contacts first name.
Anyways, to get back on track here, it is time to create records in the letters table. How should we go about this? Well, given the title of this article, we could…. use a loop! We can loop through every contact in the database, creating a letter record for them.
What follows is five examples of loops that we could use.
Method One – Layout Switching
Go to Layout [Home ]
Set Variable [$Letter ; Home::Letter Global ]
Go to Layout [Contacts ]
Show All Records
Go to Record/RequestPage [First ]
Loop
Set Variable [$ContactID ; Contacts::Contact ID ]
Go to Layout [Letters ]
New Record/Request
Set Field [Letters::Contact ID ; $ContactID ]
Set Field [Letters::Letter Contents ; $Letter ]
Go to Layout [Contacts ]
Go to Record/Request/Page [Next ; Exit After Last ]
End Loop
This script begins by storing the letter contents in a variable so it can be used throughout the script. It then loops through every record in the Contacts table, grabbing each Contact ID number in turn, before switching to the Letters layout and creating a record for that contact. This process repeats until it has gone through every contact (the exit condition).
While easy enough to follow, this loop is inefficient for a couple of reasons. First, there are two layout changes that are made during each iteration of the loop: the first is a change to the letters layout, and the second is a change back to the contacts layout. If we had 20,000 contacts in our database, that is 20,000 layout switches made, in addition to the 20,000 letter records created. It also sets two fields, and one variable during each iteration of the loop.
So if layout switching proves to be too inefficient, can we get rid of them? Yes. The answer lies in the use of script variables. If we can store every contacts ID number in a single step, and then loop through that list to obtain our contact IDs, we would not need to change layouts.
Let’s see how that might look if it were scripted:
Method Two – The Counter Approach
Go to Layout [Home ]
Set Variable [$Letter ; Home::Letter Global ]
Set Variable [$ContactIDs ; List ( AllContacts::Contact IDs ) ]
Go to Layout [Letters ]
Set Variable [$Total Contacts ; ValueCount ( $ContactIDs ) ]
Set Variable [$Counter ; 1 ]
Loop
New Record/Request
Set Field [Letters::ContactID ; GetValue ( $ContactIDs ; $Counter ) ]
Set Field [Letters::Letter Content ; $Letter ]
Set Variable [$Counter ; $Counter + 1 ]
Exit Loop If [$Counter > $Total Contacts ]
End Loop
This time, we are storing all of our Contact IDs in a single variable called $ContactIDs. To do this, we create a relationship between “Home” and “AllContacts” table occurrences, where “AllContacts” is based on the Contacts table. The relationship can be a cross-product relationship type (X) since we wish to relate to all contact records. the List function is used to return every contact ID via the relationship.
The next important step is at line #5 where we set the variable $Total Contacts. This is set to the total number of contacts, by using the ValueCount function. It will also help us to determine how many iterations of the loop we need to perform. We set a new variable – $Counter – which is used to keep track of the loop iteration progress.
To obtain the correct contact ID to store in a new letter record, we use the GetValue function. It lets us obtain a value at a specific position in a list. In this case, our list is $ContactIDs , and our position is $Counter. As an example, on the first iteration of the loop, we grab the contact ID at position 1, on the second we grab position 2, third 3, and so on…
As each loop iteration is complete, the $Counter variable is incremented, and compared to $Total Contacts. If the value exceeds that of $Total Contacts, we know to stop the loop because we have created the correct number of letters.
A counter seems like a great idea, and we have removed the need to change layouts. But do we really need a counter? Further to that, is all that counter management inefficient? Have a look at this example:
Method Three – Chopping a List
Go to Layout [Home ]
Set Variable [$Letter ; Home::Letter Global ]
Set Variable [$ContactIDs ; List ( AllContacts::Contact IDs ) ]
Go to Layout [Letters ]
Loop
New Record/Request
Set Field [Letters::ContactID ; GetValue ( $ContactIDs ; 1 ) ]
Set Field [Letters::Letter Content ; $Letter ]
Set Variable [$ContactIDs ; RightValues ( $ContactIDs ; ValueCount ( $ContactIDs ) - 1 ) ]
Exit Loop If [isEmpty ( $ContactIDs ) ]
End Loop
Here we have eliminated the need for $Counter. This method reduces the size of our initial list of contact IDs by one for each iteration of the loop. During each iteration, the first contact ID in the list is used in the new record. Afterwards, $ContactIDs is “trimmed” by “chopping off” the first contact ID, and leaving the remainder. The second ID now becomes the first. This process repeats in each iteration until the list is chopped down until no ID’s remain, at which point the loop exits.
Great, now what? How about something completely different. Here is a method that uses the replace records script step:
Method Four – Replacing
Go to Layout [Home ]
Set Variable [$Letter ; Home::Letter Global ]
Set Variable [$ContactIDs ; List ( AllContacts::Contact IDs ) ]
Set Variable [$Total Contacts ; ValueCount ( $ContactIDs ) ]
Go to Layout [Letters ]
Show All Records
Omit Record
Show Omitted Only
Omit Record
Set Variable [$Counter ; 1 ]
Loop
New Record/Request
Set Variable [$Counter ; $Counter + 1 ] ;
Exit Loop If [isEmpty ( $ContactIDs ) ]
End Loop
Replace Field Contents [No Dialog ; Letters::Letter Content ; $Letter ]
Replace Field Contents [No Dialog ; Letters::Contact ID ; GetValue ( $ContactIDs ; Get ( RecordNumber ) ) ]
The basic idea is this: start with an empty found set of Letters, generate the required number of letters, and then use two replace field contents script steps on the found set. If I was a scientist, and proposting a hypothesis, it might be that two replace steps will perform faster than 40,000 set field script steps, is the hypothesis true? We’ll find out soon…
Finally, and perhaps most unintuitively, is a method that will make use of a handy feature of the import records script step. You may have noticed when using this script step, that you are given the option when importing a repeating field, to either keep the values in a repeating field in it’s original record, OR split values into separate records. Hmmmmm, that would mean if we had a repeating field with 20,000 repetitions, and each repetition contained a Contact ID, and we could then split them into separate records, we would get 20,000 records. That sounds like something we might be interested in!
If we could create this repeating field on the Home table, and were to import this repeating field to the Letters table, we could theoretically create the required number of records using this one single step.
Let’s see what that might look like.
Method Five – Import Madness
Go to Layout [Home ]
Show All Records
Omit Record
Show Omitted Only
Set Variable [$Letter ; Home::Letter ]
Set Variable [$$ContactIDs ; List ( AllContacts::Contact ID ) ]
Import Records [No Dialog ; “LoopTests.fp7”; Add; Mac Roman ]
Well, first impressions would indicate that it certainly looks like a smaller script with fewer steps, however does that translate to a faster script? We’ll see soon.
A couple of things should be noted here. First, lines two through 4 are required to make our found set a single record on the Home table. This is required for the import, because we only wish to import from a single record. Next, we have changed the variable $ContactIDs into a global variable, $$ContactIDs. The reason for this is to correctly setup our repeating Contact IDs field on the Home table.
I have created a repeating field called “Contact IDs” on the Home table. This is a calculated repeating field, and the calculation looks like this:
GetValue ( $$ContactIDs ; Get ( CalculationRepetitionNumber ) )
The calculation references our global variable set in the script. For any given repetition, the Xth value in $$ContactIDs is set into it. For example, the fifth repetition will contain the fifth contact ID in our $$ContactIDs list. Similarly the 19,574th repetition will contain the 19,574th contact ID.
Back in the script, the import records step has been setup to import from the Home table occurrence to the Letters table occurrence, and its import mapping looks like:
Home::Letter ---> Contacts::Letter Contents
Home::Contact IDs ---> Letters::Contact ID
The option to split repeating field values into seperate records has been turned ON. With 20,000 repetitions in our Contact IDs repeating field, all calculating to a specific Contact ID, we end up with 20,000 records created in the Letters table, each with a unique Contact ID.
Testing and Results discussion
Time to test these loops. Tests were run on a database with 20,000 contact records and a single letter in a global field. The same table structure was used as in the article example. The test machine is running FileMaker 10 Pro Advanced, on an Apple Intel MacBook, 2.0 Ghz core 2 duo with 2gb DDR3 ram, and the operating system is OS X 10.5 Leopard.
Each Looping method was run three times and times were averaged. However it should be noted that in all cases, the variances between test times for a given loop were very small, in the range of .1 to .2 seconds.
Method One – Layout Switching: 53 seconds
Method Two – The Counter Approach: 24 seconds
Method Three – Chopping a List: 67 seconds
Method Four – Replacing: 25 seconds
Method Five – Import Madness: 47 seconds
Was it what you expected? The best performed looping method was the counter approach, closely followed by by the replace field contents method. The import came in third, almost twice as slow as the first two. Method one with the layout switching was fourth, and far behind in last was method three – Chopping a list.
So why do you think the results are the way they are? To get an idea, we’ll first start with the slowest method – Chopping a list. To me, the first thing I notice is that this method calls three functions during each iteration of the loop: GetValue, RightValues, and ValueCount. Each one of these is a relatively intensive process, and to repeat each 20,000 times is the major contributing factor to the slowness of this method. The lesson here is to avoid intensive function calls in a loop where possible.
Method two – the Counter approach – is the exact opposite approach. In this method, there is only a single function call in the loop – GetValue. GetValue is not nearly as demanding as the RightValues, or ValueCount functions. We also only need to perform the ValueCount function once, and outside of the loop.
Layout switching, while not as demanding as some functions, is still a fairly time consuming step and should also be avoided where possible.
The replace records method, while very fast, giving the counter approach a run for its money, feels a bit like overkill for what is required. It’s a good example how to think outside the box and look at other ways to approach a fairly typical situation. The reason it is very close in time to the counter approach, is because it too contains a single repeated function call to GetValue, which is done 20,000 times.
The final method, of using an import with a calculated repeating field, performs around the middle range in comparison to the other methods. It is another good example of thinking outside the box to solve a problem, but might not be the best method for this particular problem…. or is it? Stay tuned!
Combining Methods for the Ultimate speed
Well, the loops are written, the tests are done, and the counter method is the winner. It seems that reducing the number of function calls, can drasticaly increase your loop speed. But what about that import? I’m sure that when I have done imports in the past, they seem to create records faster than I could expect. Is there something more to this than meets the eye?
Time to think outside the box again. What if we run another test. This time, no field setting, just straight up record creating. Is it faster to create records by a loop, or an import?
For this example, we will use a standard loop to generate 20,000 records. We will also use a repeating field for the import creation. However this time the repeating field will not be a calculation, it will be pre-set with 20,000 values in the repetitions.
Generator 1 – A simple Loop
Go to Layout [Letters ]
Set Variable [$Counter ; 1 ]
Loop
New Record/Request
Set Variable [$Counter ; $Counter + 1 ]
Exit Loop If [$Counter > 20000 ]
End Loop
Generator 2 – An Import
Import Records [No Dialog ; “LoopTests.fp7”; Add; Mac Roman ]
What are the results? The simple scripted loop took a whopping 34 seconds to generate the required records. That’s even slower than if it had to set two fields during each iteration of the loop! It makes no sense, but after first being a disbeliever I tried it many more times with the same result. Perhaps this is something FileMaker will need to take a look at. Suffice to say, if you’re going to create many records in a single script, set something!
Anyways, with that nice little surprise out of the way, let’s see how the import generator went…. 2 seconds… 2 SECONDS !!!!!!! It took 2 seconds to create 20,000 records !!!! That’s over TEN TIMES faster than any previous method known to man (or this article…)
If only there were some way to harness this amazing speed. We know the record creation part of this method can be incredibly fast, but what else is required to obtain this speed? Well, recall that the repeating field in this example is a normal un-calculated field with its repetition values preset. If we were to use this method, we would need to preset 20,000 repetitions into our field, then run the import. How can we set these repetitions? How about a loop! What have we shown to be the fastest looping method so far, why a counter of course! So let’s combine the two:
Method Six – The Need for Speed
Go to Layout [Home ]
Show All Records
Omit Record
Show Omitted Only
Set Variable [$Letter ; Home::Letter Global ]
Set Variable [$ContactIDs ; List ( AllContacts::Contact IDs ) ]
Set Variable [$Total Contacts ; ValueCount ( $ContactIDs ) ]
Set Variable [$Counter ; 1 ]
Loop
Set Field [Home::Contact IDs[$Counter] ; GetValue ( $ContactIDs ; $Counter ) ]
Set Variable [$Counter ; $Counter + 1 ]
Exit Loop If [$Counter > $Total Contacts ]
End Loop
Commit Records/Requests [Skip data entry validation; No dialog ]
Import Records [No Dialog ; “Loop Examples.fp7” ; Add ; Mac Roman ]
So, what is going on here? Well we have combined a counter method with an import method. First, we ensure that only one Home record is selected for the import later on. Next we setup a normal counting loop as usual, but this time in the loop we are setting a repetition of our Contact IDs repeating field. For example, on the tenth iteration of the loop, we are setting the tenth repetition to the tenth Contact ID in $ContactIDs, and so on for every repetition. Finally when done, we use the import method to generate records by splitting the repetitions into separate records.
Will this be faster? Is setting 20 thousand repetitions a faster process than setting 20 thousand fields in 20 thousand records?
Yes.
For this method, the average time required to generate the records is 20 seconds. That’s a full four seconds faster than the previous best method, and well over twice as fast as perhaps the most common method, that of layout switching.
Conclusion
This article has outlined a number of different methods for carrying out the same task. It illustrates how FileMaker gives you the basic set of tools required to solve a solution, but how you use them is up to you, but sometimes the most obvious solution may not always be the most efficient. Hopefully it has offered some ideas and tips on how certain script steps and functions can be used for uses other than you might think. I hope it has also demonstrated, that with a little thought and preparation by paying attention to what goes into your loop, that you can achieve significant speed increaseses.
0 Responses to “Thinking Outside the Loop”