Random Finds with Multiple Column Repeats
by Jim Kramer (j-kramer@uiuc.edu)
RATING: Intermediate
VERSION: FileMaker 3/4/5
PLATFORM: Macintosh & Windows
TECHNIQUE: RepeatFind.FP3 & RepeatFind2.FP3
In an era of related files and portals, it's possible to look down on what some consider to be a forgotten feature of FileMaker Pro. While most databases are best structured for utilizing relational design, repeating fields have become, for me, a quite useful tool in many of my FileMaker Pro applications. My favorite use of repeating fields is to allow users to perform random finds on a their database files.
Random I will also demonstrate how repeating fields can be used in multiple columns to create a nice layout element. For an additional touch, I'll demonstrate how to add an optional self-join relationship to display the results of the repeating field find in a portal on the same layout. These will add up to create a very nice user-interface for performing random finds.
Why bother?
Why bother with random finds in a database? Databases are exceptional for organizing data that is either marked by some common element, or can be put in some common order (alphabetical or numerical). I have a membership database in which it is very easy to set up a report of members by country, state, company, first name, last name, etc. Finding member records from the same state, ordering names alphabetically by last and first names are truly easy tasks that can be accomplished by most any database application.
I use my membership database to send out a variety of mailings, including membership renewal forms. These forms are then returned by the members with payments.
As the membership renewal forms and payments are randomly returned, I need to record the payments and print renewal receipts. I need an efficient way to find a dozen or so records (out of hundreds) to process the received membership forms. Since the membership forms come back to me in a random order, I need a good way to find the matching records in the membership database. For this, I use a global repeating field to create a random pick-list of the incoming member ID numbers.
Combined with a scripted find, the repeating fields provide a very efficient way to find the required records in the database and process the membership information. (If you think about it, after I find and process the membership information, I'll have a common piece of information to link the day's set of incoming membership forms together. That is, the date I process the information. However, to change a date in each record, the records need to be located first.)
Adding the random finds with multiple column repeats method is easy to add to any application.
Random Finds
The first step is to create a global field (text or number) and configure it as a repeating field with a maximum of 15 repetitions. The repeating field will hold the membership IDs, invoice numbers, etc. you need to find. Here, I call it _FindRepeat (note that I use the underscore as a prefix for my global fields).
For your application, create _FindRepeat with enough repeats to handle the number of records you may need to process at any one time. In fact, I have one application I use that gives the operator a column of 30 repeats to fill in if necessary. While having a field with a large number of repeats is handy, it is problematic from a design standpoint. But, don't be dismayed! I'll discuss how to split the repeating field into multiple columns later.
The second step is to create a global number field to be used as the repeat counter. I call the global counter _FindCounter.
Third, we need to add a looping script that will search through each of the 15 repeats of _FindRepeat, perform a find for each repeat (using the NewRecord/Request script step while in Find Mode). The ScriptMaker steps of the Find Repeats Script are:
If [Count(_FindRepeat) <1)]
Exit Script
Else
Freeze Window
Set Field [_FindCounter, 1]
Enter Find Mode
Loop
Set Field [IDNumber, GetRepetition(_FindRepeat, _FindCounter)]
New Record/Request
Set Field [_FindCounter, _FindCounter + 1]
Exit Loop If [_FindCounter > 15]
End Loop
Perform Find
End If
The first If statement (If [Count(_FindRepeat) <1)) in the Find Repeats Script:
If [Count(_FindRepeat) <1)]
Exit Script
This checks to see if any of the repetitions in _FindRepeats are filled. If all repetitions are empty, the script is stopped by the Exit Script step.
The Loop uses the _FindCounter to get the information out of every repetition of _FindRepeat, places it in the IDNumber field (this could be your Invoice number, membership number, etc.) Because the Set Field and New Record/Request steps occur in Find Mode, this creates 15 successive "OR" finds on the data file when the find is run by the Perform Find step. (If you need more or less than 15 repeats, adjust the number of repetitions in your repeating field and change the number in the Exit Loop If step appropriately.) Attach this script to a button near your _FindRepeat field and you will be ready to run your first random find on your data.
Cool features of the Find Repeat Script are:
1. It will work with IDNumbers entered in any order in the _FindRepeat field.
2. Entries in repetitions that don't match actual records are skipped when the Find Repeats script is run.
3. Empty repetitions are skipped when the Find Repeats script is run.
4. The list of IDNumbers you listed in _FindRepeats is static. Thus, if you discover that you mistyped or forgot an IDNumber, just make the required changes to the entries you made in the _FindRepeat field and run the Find Repeats Script again.
This is particularly useful in a fast-paced, fast-changing environment where it is conceivable to receive renewals up to the last minute of running the script. The Find Repeat Script method gives me the flexibility I need to find and process the records for the renewals, add the new members (who won't have IDNumbers yet), add the IDNumbers for the new members to the _FindRepeats field. Running the find again on the updated list of IDNumbers in the _FindRepeats field allows me to print receipts for both renewals and new memberships based on the found set.
Believe me, once you get the used to this random find feature, you'll wonder how you did without if for so long!
Clearing Your Pick List
So, you run your find, modify the set of records based on the new information that just came in today and you're set, right? Well, not quite. One addition that is needed for a smooth interface is a script to clear the _FindRepeat field. You will want to do this before processing a new batch of forms.
A simple looping script, which I call Clear Find Repeats Script, is needed to do this:
Freeze Window
Set Field [_FindCounter, 15]
Go To Field [_FindRepeat]
Loop
Clear [Select]
Go To Next Field
Set Field [_FindCounter, _FindCounter - 1]
Exit Loop If [_FindCounter = 0]
End Loop
Once again, we need a counter to end the loop (we can use the same _FindCounter).
The Go To Field is required to identify the repeat field (_FindRepeat) we will loop through. This starts the script at the first repetition on _FindRepeat. Once the Clear step empties the first repeat, the Go To Next Field step stays in the repeating field and goes to the next repetition if theTab Order of the repetitions in _FindRepeat are in consecutive order. Please check the Tab Order before running this script. If the _FindRepeat field's Tab Order for all repetitions is not set properly, the script may just proceed to the next field in your layout and clear that field's data. (The good news is, it will stop after clearing one field in one record. However, it will do this each time the script is run.) Attach this script to a Clear Picks button and you now have a nice system for finding random repeats and clearing your picks when you need to.
Creating a Scrolling List of Your Found Records
Now that we have a system to perform random finds, let's make some user-interface improvements. Once the random records are found with the Find Repeats script above, wouldn't it be nice to see your finds right on the same screen?
These kinds of screen views are relatively easy to setup using a self-join relationship, and a scrolling portal. Note that you can't form a relationship based on a repeating field (well, technically you can but only the first repetition can be used to match up to related records). To see the found set of records in a portal, the repeating field information needs to be transferred to a file that will allow a relational match. To do this, create a global text field (_RepeatStore). Create a relationship (Show Finds) in your file (mine is Member.fp3) to match RepeatStore to MemberID (RepeatStore::=MemberID) in files Member.fp3 to Member.fp3. Create a subscript to move the information from _FindRepeat to _RepeatStore as follows.
Freeze Window
Set Field [_FindCounter, 1]
Set Field [_RepeatStore, ""]
Loop
If [GetRepetition (_FindRepeat, _FindCounter) <> ""]
Set Field [_RepeatStore, RepeatStore & NumToText(GetRepitition (_FindRepeat, _FindCounter )) & ""]
End If
Set Field [_FindCounter, _FindCounter + 1]
Exit Loop If [_FindCounter > 15]
End Loop
Set Repeat Values to Field Sub-Script
The Set Field script step:
Set Field [_RepeatStore, RepeatStore & NumToText(GetRepitition (_FindRepeat, _FindCounter )) & ""]
This transfers the values from any filled repetition in _FindRepeats into _RepeatStore and separates them with a carriage return (the "") so the values line up in one column. The IF statement:
If [GetRepetition (_FindRepeat, _FindCounter) <> ""]
that precedes this Set Field step prevents a blank line from being added to the _RepeatStore field.
Please note that a counter is needed to control the loop through the repeating _FindRepeat field again. However, you can simply re-use the same _FindCounter field created earlier. The NumToText function can be deleted if _FindRepeat is defined as a Text field. This sub-script is best used if the at the end of the Find Repeats scripts.
If [Count(_FindRepeat) <1)]
Exit Script
Else
Freeze Window
Set Field [_FindCounter, 1]
Enter Find Mode
Loop
Set Field [IDNumber, GetRepetition (_FindRepeat, _FindCounter)]
New Record/Request
Set Field [_FindCounter, _FindCounter + 1]
Exit Loop If [_FindCounter > 15]
End Loop
Perform Find
End If
Perform Script [Subscript, Set Repeat Values to Field]
Find Repeats Script with Set Repeat Values to Field Sub-Script
Put a scrolling portal to the Show Find relationship on the same layout the _FindRepeat field is located. Now, as soon as you run the Find Repeats script, the records (or part of them - I use the full names of my members) will pop-up in the portal for review. A nice touch!
To clear the _RepeatStore field (and the portal), just use a Set Field script that sets the _RepeatStore field to nothing.
Set Field [_RepeatStore, ""]
It's a good idea to add this step to your Clear Repeats script so the _FindRepeats and _RepeatStore are cleared at the same time.
Splitting the Repeating Field into Columns
I often use one repeating field column on a find layout with a portal showing my finds. If your layout doesn't have room for one long _FindRepeat field, why not just split it into two shorter fields? That would be nice wouldn't it? The bad new is, you can't do this... The good news is, you can easily link two repeating fields together with simple scripts so they operate as one. And if you got this far, most of the work is already done!
Let's assume you need to have 30 repetitions in your _FindRepeat field (instead of the 15 repetitions we used so far in this example.) To do this, duplicate the _FindRepeat field and name it _FindRepeat2. Place the _FindRepeat2 on your layout next to _FindRepeat. Extend your scripts so they loop through both _FindRepeat and _FindRepeat2.
If [Count(_FindRepeat) + Count(_FindRepeat2) <1)]
Exit Script
Else
Freeze Window
Set Field [_FindCounter, 1]
Enter Find Mode
Loop
Set Field [IDNumber, GetRepetition (_FindRepeat, _FindCounter)]
New Record/Request
Set Field [_FindCounter, _FindCounter + 1]
Exit Loop If [_FindCounter > 15]
End Loop
Set Field [_FindCounter, 1]
Loop
Set Field [IDNumber, GetRepetition (_FindRepeat2, _FindCounter)]
New Record/Request
Set Field [_FindCounter, _FindCounter + 1]
Exit Loop If [_FindCounter > 15]
End Loop
Perform Find
End If
Find Repeats (1 & 2) Script
Notice that this script simply resets the _FindCounter and loops through the second repeat field (_findRepeat2). Simple enough. Now, we just need to modify the other scripts a bit to work with both global repeating fields.
Freeze Window
Set Field [_FindCounter, 15]
Go To Field [_FindRepeat ]
Loop
Clear [Select]
Go To Next Field
Set Field [_FindCounter, _FindCounter - 1]
Exit Loop If [_FindCounter = 0]
End Loop
Set Field [_FindCounter, 15]
Go To Field [ _FindRepeat2 ]
Loop
Clear [Select]
Go To Next Field
Set Field [_FindCounter, _FindCounter - 1]
Exit Loop If [_FindCounter = 0]
End Loop
Set Field [_RepeatStore, ""]
Clear Find Repeats (1 & 2) Script
You will also need a new subscript to display the selections form each repeating field in your portal.
Freeze Window
Set Field [_FindCounter, 1]
Set Field [_RepeatStore, ""]
Loop
If [GetRepetition (_FindRepeat, _FindCounter) <> ""]
Set Field [_RepeatStore, _RepeatStore & NumToText(GetRepitition (_FindRepeat, _FindCounter )) & ""]
End If
Set Field [_FindCounter, _FindCounter + 1]
Exit Loop If [_FindCounter > 15]
End Loop
Set Field [_FindCounter, 1]
Loop
If [GetRepetition (_FindRepeat2, _FindCounter) <> ""]
Set Field [_RepeatStore, _RepeatStore & NumToText(GetRepitition (_FindRepeat2, _FindCounter )) & ""]
End If
Set Field [_FindCounter, _FindCounter + 1]
Exit Loop If [_FindCounter > 15]
End Loop
Set Repeat (1 & 2) Values to Field Sub-Script
Be sure to add this subscript to the Find Repeats (1 & 2) Script. Again, notice I use the same _FindCounter again. It can actually be used over and over in this way since it is reset to the appropriate value each time and it is not used in separate scripts at the same time.
Well, if you want to stop at 2 repeating field columns on your layout, you are done at this point. It is also easy to extend this technique if you prefer to have 3, 4, or more columns on your layouts. (Breaking up the scripts into sub-scripts will certainly be a good idea at this point. I'll let you figure this part out.)
Let's Repeat
Well, even if you are a student of serious relational database design, I hope my repeating fields technique provides you with an alternative school of thought!
Happy FileMaking!