Q: I have a database that contains a field NAME (contains LastName, FirstName of people). Users want to populate a table of people names so they can exclude the records that match. I can't seem to get the constrain script step to work correctly and remove the names from the found set. Here's my script so far:
Go to Layout [ "Main Menu" (ACI Tracker) ]
// do some steps
//
Go to Layout [ "Omit_Labor" (Omit_Labor) ]
Show All Records
Go to Record/Request/Page [ First ]
Loop
Set Variable [ $Omit_Match; Value: Omit_Labor::Description]
Go to Layout [ "Main Menu" (ACI Tracker) ]
Enter Find Mode []
Set Field [ACI Tracker::NAME; $Omit_Match]
Constrain Found Set [ Specified Find Requests: Omit Records; Criteria: ACI Tracker::NAME: "$Omit_Match" ] [ Restore ]
Go to Layout [ "Omit_Labor" (Omit_Labor) ]
Go to Record/Request/Page [ Next; Exit after last ]
End Loop
Go to Layout [ "Main Menu" (ACI Tracker) ]
Do you have any ideas?
-- Micheal Haley
A: What a great scripting question! The goal is to let users add names to one table, then perform a find in another table that automatically omits anyone in the first table. Maybe some other time we'll discuss ways you could accomplish this by setting up relationships between the two tables, but for now, let's treat it purely as a scripting exercise.
The problem you're running into is that you're trying to set the search criteria both statically and dynamically. You shouldn't be restoring any static search criteria as part of the Constrain Found Set step … you've already set the criteria dynamically with the prior Set Field step. And even if you wanted to statically set the criteria as part of the Constrain options, you can't reference a script variable from in there, only static values.
Two small changes will, therefore, fix your script. First, deselect the Restore option on the Constrain. Then, add an Omit Record step right before it. Now the logic goes something like this: Go to the first record in the list of omits, stick the value in a variable, return to the main table, and constrain the current found set to omit any records that match the value in the variable. Just repeat that process for each item you want to omit. Problem solved.
Although the routine works with those two edits, it isn't as elegant or efficient as it could be. Specifically, we don't like the ping-ponging between the two layouts, nor that the script has to perform umpteen separate finds. A better algorithm would be to grab all the values to omit in one fell swoop, then perform one single find that omits all the values you've grabbed.
There are a few ways to create an array (list) of all the values in the omit table. One option is to loop across all the records and append each item to the end of a variable. Another is to use the Copy All Records script step and paste into a global field. A more elegant way, however, is to create a Cartesian relationship (cross join, the "x" relationship operator) to the table of omits, then use the List() function to assemble all the items you want to omit into a return-delimited list.
After you have assembled the complete list of items to omit, you can create a loop that parses the list into separate find requests. The result is a script that's shorter, simpler, and performs better:
Go to Layout [ "Main Menu" (ACI Tracker) ]
Set Variable [ $Omit_Match; Value: List (Omit_Labor::Description) ]
Enter Find Mode []
Set Variable [ $counter ; Value: 1]
Loop
Set Field [ACI Tracker::NAME; GetValue ($Omit_Match ; $counter)]
Omit Record
Set Variable [ $counter ; Value: $counter + 1]
Exit Loop If [ $counter > ValueCount ($Omit_Match)]
New Record/Request
End Loop
Perform Find []
The GetValue() function was introduced in FileMaker Pro 8, and the List() function in FileMaker Pro 8.5, so if you're using an older version, you'll have to make some adjustments. Either of the other alternatives for grabbing the list of omit values would work, and you can use MiddleValues() in place of GetValue() if need be.