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]
Log in as a member to comment...