Join Cris Ippolite for an in-depth discussion in this video Adding find criteria to a script, part of FileMaker Pro 11 Essential Training.
Finding records is one most common task that users perform in FileMaker Pro databases. As a result, finding records are also a common action that Scripts are called to perform. Scripts can be set up to perform any search that a user can perform manually, and even some that they cannot perform manually. Because you can control a scripted search as a developer more easily than you can a manual search, scripted searches are often more efficient and reliable, mainly because they'll always have the same outcome, because you don't have to worry about users messing up the criteria. This can also help with system performance. There are actually several scripts steps involved in the finding records activities within your scripts, and we'll take a look at those here.
If we go to Scripts > Manage Scripts, and let's double-click on Print Welcome Letter. You'll notice under Found Sets, we've got the ability to Perform a Find, Perform a Quick Find, Constraining Found Sets, Extending Found Sets, and even Modify Last Find, which is somewhat integrated into constraining and extending, and then some of the reset options, like Showing All Records and Show Omitted Only. So, all of these really speak to creating a Found Set, and then manipulating the Found Set, of course. Most of them are pretty self-explanatory. So let's say, in our Print Welcome Letter script, that what we would like to do, instead of just running the report and printing off a letter, that we want to isolate the customers in the database that have a pending status, and then print a welcome letter for each one of them.
So in our case, before we can get to the point where we want to print, which is right about at the Go to Layout step, actually, we're going to want to instead perform a find. This way we're only printing a Found Set instead of all the Records. So really, we could add a Perform Find either before or after we're going to the Layout, but since it really won't matter with this technique that we're going to work with, we're going to click on Perform Find. And you see that it added itself in the second line. If it didn't, you can just click on it and use the handles to move it up or down. But now under Perform Find, you see that we've got a Script Step Option for Specifying find requests. We'll hit Specify.
You'll see it's the same window used in Saved Finds, and here I've already got some Criteria saved. If you don't have any Criteria saved, it's going to look something like this. That's okay. We'll just start from scratch. So first, when you hit a Find Request, you'll notice that everything that we put inside this window represents one find request. And you'll see that you can have multiple Find Requests, just like you're searching in the database through FileMaker Pro; you can have as many Find Requests as you want, but they all make up one search. So in our case, we just have one find request, and we'll create that.
So back in the Edit Find Request window, we can either choose to Find Records, which would be the Include button when we're searching, or Omit the Records, all based on the Criteria we're about to add to this request. So in this request, we want to find a field which mimics the same behavior as clicking into a field while in Find mode. In this case, we want to go to the Status. Once selected, now we put the Criteria. So this is what we would actually type into the field while we're in Find mode. And in this case, we want to say, Pending.
We can also use any operators that we might be used to using when in our normal finding procedures. But here we're just going to say anywhere where Status equals Pending. Now we have to hit the Add button, which adds it to our list of Actions. Now after we hit OK, we see Find Records with the Criteria, the term Pending inside of the Customer Status field, and we'll hit OK, and we see now the word Restore shows up inside of our Script. That's because we've told the Script what to do. If you recall in our previous exercise, in our Print Setup, we had saved some settings in here to make sure we have certain Paper Sizes and Page Setup.
Then also in Print, we want to make sure that it prints All the Pages, and all the Records being browsed as well, because now we have a Found Set. So after we update this script, we can close it, which of course will prompt us to Save, if we haven't already. And go into Layout mode and right-click on the Printer. We see that there is no Script assigned to it. So we're going to have to go in and say Print Welcome Letter. If you already have the Script assigned to it, of course we don't need to do this, because we just modified an existing Script. Go into Browse mode, Save our work.
And now let's go ahead and hit the button, and see what happens. We're going to print it to Preview, so we can see them onscreen. You see now that we're printing several pages. If we look in our Found Set, you see we've got a message that pops up, and we could probably eliminate this from our script moving forward. But we'll look, and we see I have a Found Set created of 1,221 records. And when we look at these in Customer List view, we can see that the Status of all these is Pending. So our Find performed properly, and the script generated the print of the correct letter, just like we had asked it to.
And all of that was done by adding a Print step inside of our script. Since finding records is one of the common tasks that your users can perform in FileMaker, it's a good idea to get familiar with the different Find Script Options when you're authoring scripts, since creating scripts is really just emulating the work that users do by hand anyways.
- Creating databases from templates
- Creating fields in spreadsheet format
- Creating tables and relationships
- Defining key fields
- Adding validation and auto-enter values to fields
- Managing records, including duplicating, locking, and deleting records
- Creating and managing layouts
- Formatting layout objects
- Finding and sorting data
- Creating calculation fields
- Building reports
- Printing and saving as PDF or Excel
- Writing and triggering scripts
- Using relationships throughout a database
Skill Level Beginner
Q: In the Chapter 16 tutorial, “Using Text Functions,” the instructor discusses how to calculate the First Name and Last Name from the Full Name. However, the method does not account for names ending with “Jr.” or “Sr.” or “III,” etc. How can I account for added suffixes in names?
A: For cases like this, you can create a third "Suffix" field. Then change the FullName calculation to:
NameFirst&" "&NameLast&" "&Suffix
This way, nothing will appear if the Suffix has no value, but if it does have a value the suffix will appear.
Q: What information is actually on the “Invoice Line Item” table in the examples, and how does it actually connect to the tables that it comes from?
A: The information in each line item is native to the "Invoice Line Item" table. The fields are defined in that table and each record represents "A Product appearing on an Invoice."
Each time a product is used on an invoice, a record in the line item table is created. Many of the fields, for example "Quantity," are native to that table because those values only exists when a Product is used in an Invoice, and not as attributes of a Product itself.