>Loops, Numbering Subsummaries
Example file: LoopLoop.fp3
Loops are a special subset of scripts. They begin with Loop and end with End Loop; whatever steps are in between will be repeated on each record of the found set (unless otherwise instructed).
They are used whenever you need to “walk” through a set of records, usually making a comparison, then a decision whether to execute an operation on each record, before moving on to the next, exiting after the last.
In the example file I’ve created several scripts with Loops to show some common uses, as well as an unusual one. Someone asked me for a method to number Subsummaries within a found set. There is no built-in way to do that.
I extended the method to create an outline-like effect with a couple of levels, Harvard style. I’ve often wondered if I could do that, as I use outlines often (to write these little articles, for example).
As far as FileMaker is concerned, I think it makes a useful report layout, especially for information which has no intrinsic numbering (ID #’s, etc.) We like long lists to have labels, such as A., B., and 1., 2., etc.. It makes it easier to comprehend and navigate.
The actual scripts and their explanations are in the file itself. I think that makes easier reading, and they’ll make more sense after you see them in action.
There are other looping scripts in the file, which demonstrate some of the common pitfalls which we all fall into. Each step in a Loop has an effect on it, and even the order of the steps can make a huge difference.
Omit or Delete
One of the most common problems with Loops is how and when to move to the next record. It’s especially tricky with Omits and Deletes.
The tip is simple: Omit does not require a “Go to Next Record” step.
After omitting or deleting the current record, you are automatically on the next record. Try it manually (Cmd-M) on a list and you’ll see. Thus:
Loop
Omit
End Loop
will omit all the records, one at a time.
The trouble is, it won’t stop (short of “Cmd-.”). That’s the second problem with loops, affectionately known as an “endless loop.” So we add a step, after Omit:
Exit Loop If [“Status(CurrentFoundCount) = 0”]
In other scripts, the step,
Go to Record/Request/Page [Exit after last, Next]
will usually stop the loop. But sometimes it won’t. For example, if a step within the loop deletes a record that matches certain conditions, and the last record gets deleted, the “Exit after last” can’t evaluate. The loop will continue, beginning with the first record again. Eventually it will stop, when the last record doesn’t meet its conditions.
Order of Steps
The order of the steps within a loop can be important. When you are passing values back and forth between global holding fields and the regular fields in each record as it passes through the loop, it’s pretty easy to put the wrong one first, or leave out a step, especially since they tend to have similar names.
This is one place where adding the letter “g” to the field name of globals is almost mandatory.
There are a few Omit scripts in the example file that show what happens when you switch two steps.
Counters
One way to keep track within Loops is to set a counter, a global (number) field that gets incremented with every pass of the loop.
A database has the ability to set a field by the value of the same field. In this case:
Set Field [“CounterNum g”, “CounterNum g + 1”]
Don’t forget to set it to 1 before the loop begins.
The Number Subsummaries script in the example file uses a counter, not to exit the loop, but to enter into a regular number field. It only increments when there is a new value in a field.
The Omit w/Counter script shows how to use the Found Count and a decrementing count (-1) to exit a loop.
New or Duplicate Record
Another problem to watch out for is the effect of the Sort Order when creating New or Duplicate records with Loops. This is critical for the position of the new record.
When records are unsorted, a New record is created as the last record.
When records are sorted by a field with only unique values, such as a Serial ID field, the New record will be the next record, just after the current record.
But, and this is the “gotcha,” when records are sorted by a field that has several equal values in several records, resulting in “blocks” of records, the New record will go at the end of the block, not necessarily the next record.
[Thanks to Ilyse Kazar for this important tip.]
The fastest Loop for Duplicating Records comes from Eric (the genius) Sheid:
Unsort
Go To Record/Request/Page [LAST]
Loop
Duplicate Record/Request
Set Field [“Contact ID”, “Field1::Temp ID”]
Omit
Omit
Exit Loop If [“Status (CurrentFoundCount) = 0”]
End Loop
Find All
He begins at the end, rather than the beginning. The Unsort ensures that you’re on the last record. The ingenious double-omit Omits the duplicate and its parent record, ready for the next in line (backwards), ending when all records in the found set have been duplicated.
The Set Field [“Contact ID”, “Field1::Temp ID”] is for setting the new records via the Contact ID relationship to a temporary ID in another file. I’m not sure exactly what he was doing there; I didn’t save that part. You could set a field for many different reasons at this point.
Relationships and Replace
For some final craziness, I attempted to duplicate the results of the Outline and Omit loops by using self-relationships. I was generally successful, but with some difficulties.
I found that the “Replace” via calculation operation can be used similarly to Loops, but with distinct differences. Overall, I’d say that it’s more limited in its record-selection and decision-making.
It’s faster at changing values in fields to some other set or calculated value, especially in large found sets, and should be used for that. Loops are often much slower for these simple tasks.
But it’s dismal at sorting. I was unable to get second-level lists numbered properly by sort orders when using the relationship method, while it was fairly easy with the Loops.
Sorting the relationship worked, and was necessary to get the first level numbered, but it could not sort the 2nd level. A concatenated ID and second relationship for the second level, sorted, would probably work. You’d have to go to each “block” separately, Replace, then go to the nextÅ Aargh.
Way too much overhead to do something that the Loop did easily.
Relationships and Omit
I had much better luck with the Omit scripts and relationships. Especially in the scripts finding Duplicates, either a loop or a loop with a relationship seemed to work equally well.
You could also do it using a Replace with just relationships, no loop, Replace Earlier Dupes script.
The Replace script would tend to be faster on 1000+ records, but otherwise they’re pretty equal; take your pick.
But, for “walking” your records, making complex decisions, creating new records, omitting, deleting or numbering–especially when sorting is needed–Loops are the tool to try.
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.