This edition's tips are drawn from reader comments posted to the Advisor Web site. Subscribers can read a complete archive of this column and all the articles from Advisor Basics of FileMaker Pro.
Figure 1: Worlds collide! -- Robert Smith and Patty Smythe may never occupy the same room, but you can have them in the same found set.
In a previous column, I provided the following steps to perform a find that adds the matching records to your current found set of records rather than replacing the current found set with a new one.
- Perform a regular find for "Smith."
- Enter Find mode.
- Type "Smythe" in the Last Name field.
- DO NOT click on Find. Instead go to the Requests menu and select "Expand Found Set."
- FileMaker Pro performs the find and adds those records to the existing found set.
Reader Fadi commented in part, "I like the steps provided above. I was wondering how I can translate it into a script." Well, I'll give you two options.
Version one works just like the original example:
- Open ScriptMaker and create a new script.
- Add a Perform Find step to the script.
- In the Script Step Options area, check Specify Find Requests.
- Click on the New button to create the find request.
- From the list of fields, select the one you want to search. For this example I'm using a field called "nameLast."
- In the criteria box, type in your search term (I'm using "Smith"), and click on OK twice (figure 1).
- Now add an Extend Found Set step to your script and repeat steps 3 through 5.
- In the criteria box, type in your second search term (I'm using "Smythe"), and click on OK twice.
Save your script and try it out. After running the script you'll have a found set of all the Smiths and Smythes in the database.
Version two consolidates the Perform Find and Extend Found Set script steps into a single step:
- Open ScriptMaker and create a new script.
- Add a Perform Find step to the script.
- In the Script Step Options area, check Specify Find Requests.
- Click on the New button to create the find request.
- From the list of fields, select the one you want to search. For this example I'm using a field called "nameLast."
- In the criteria box, type in your search term (once again, I'm using "Smith"), and click on OK.
- Click on the New button to create a second find request.
- From the list of fields, select the same field you chose in step 5.
- In the criteria box, type in your search term (surprise! I'm using "Smythe"), and click on OK twice.
- Save your script and run it.
Version two returns the same results because the second find request is treated as an "or" search. Specifically, you're instructing FileMaker Pro to find all the records with "Smith" or "Smythe" in the nameLast field.