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:

Go to Record/Request/Page [First]
Loop
  # do something here with the current record
  Go to Record/Request/Page [Next; Exit after last]
End Loop

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 top 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 to 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:

New Window []
Freeze Window
Go to Layout ["dev_contacts" (contacts)]
View As [View as Form]
Go to Record/Request/Page [First]
Loop
  If [ //your test for a duplicate ]
    Delete Record/Request [No dialog]
  Else
    Go to Record/Request/Page [Next; Exit after last]
  End If
End Loop
Close Window [Current window]

Update: Ward makes a great point in the comments.  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.

New Window []
Freeze Window
Go to Layout ["dev_contacts" (contacts)]
View As [View as Form]
Go to Record/Request/Page [First]
Loop
  If [ //your test for a duplicate ]
    Set Variable [$last; Value:Get ( RecordNumber ) = Get ( FoundCount )]
    Delete Record/Request [No dialog]
    Exit Loop If [$last]
  Else
    Go to Record/Request/Page [Next; Exit after last]
  End If
End Loop
Close Window [Current window]

Ward Clark
05/20/2010

The record processing loop needs to be bit more clever with the Delete Record logic because deleting the last record does not exit the loop.  What I’ve done in this situation is test for the last record *before* the Delete and then conditionally exit after the Delete.

Chad Novotny
05/20/2010

Ward, good point.  My thought was that you just end up touching the second-to-last (and now last) record twice.  But you’d definitely want to be aware that could happen and account for it somewhere. 

Thanks for the suggestion!

Seoirse Mac an Garda
12/07/2010

Very good. And thank you for what you said of creating a window, do whatever you need and close the window.

Log in as a member to comment...