The Support Group Blog

Looping Through Records

Robert asks:

What is the best way to create a script that loops through all selected records?

I almost always tackle it with the basic script steps:

Screenshot1

The second Go to Record/Request/Page step, with the options Next and Exit after last, will keep moving to the next record; when you attempt to move from the last record to the (non-existent) next record, it exits the loop.  But there are some things to consider, and some gotchas to look out for.

First, when you insert that second Go to Record step and select Next, the Exit after last option is NOT selected.  This is one of my pet peeves; I don’t know that I have EVER wanted to not exit a loop once I’m on the last record and try to keep going.  In my opinion, it ought to be selected by default.  So, don’t forget to check that option!

To improve performance, make sure you’re viewing the records in form view (as opposed to list or table view), and use a Freeze Window step.  If you’re using script triggers in FileMaker 10 or 11, looping through records could be setting off multiple OnRecordLoad events.  Most likely, you’ll want to move to another layout that doesn’t have script triggers enabled.

After you finish your loop and end up on the very last record, you may want to return to your original starting point.  If that’s the case, there are a couple of easy ways to do this.  You can first capture the current record number in a variable and then return to that record with one more Go to Record step. Alternatively (and this is usually my preferred method) you can open a new window before you start looping; once you’re done with your processing, close the window to return to original.

Lastly, think about what happens if you delete or omit a record as part of your loop.  Let’s say you’re checking for duplicate contact records, currently on record 6 of 10, and delete the record.  You are now on record 6 of 9, but that was originally record 7.  If you now go to the next record, you’ll be on what was originally record 8, without ever analyzing record 7.  Your adjusted script may look something like:

Screenshot2

Update:

Here’s an updated script to account for deleting the last record in the loop.  Before deleting the record, we check to see if we’re on the last record, setting a variable named $last to either True or False. After the delete, if $last is True we exit the loop.

Screentshot3

 
Share this entry
1 replies