The built in scripting engine for FilemakerPro is actually pretty decent, but people design some pretty clunky interfaces. What I wanted was a button that the user could click which would open a dialog box with some search field inputs. There are a few tricks to get this to work properly.

The first step is to open the scripting engine. This is found under “Scripts” > “ScriptMaker”. Click on “New” in the dialog window to create a new script. The dialog window allows you to use up to 3 fields. Why you can’t select more is beyond me, but that is fine for my purpose.

You can start by giving the new script a name. I’m going to be searching by teacher name, so my script is called “Search by Teacher”. The name is rather unimportant right now. The first action you want to perform in the script is “Enter Find Mode”. This does exactly what you would expect, the fields that you enter data into will be searchable rather than data entry. This is important because the next step is where we open the custom dialog box. By first entering find mode, the data you enter into the dialog box will become your search fields. Down the command list under “Miscellaneous” is show custom dialog. There are 2 tabs to focus on as seen here:

The first tab is for the title, message, and buttons. You can enter whatever you want for the title and message and the default buttons work fine for me. You’ll soon see however that Filemaker makes a strange reference to the button order. They are referenced by the numbers 1-3, listed left to right as you would see in the dialog window, even though in the script window they are shown from left to right.

The input fields tab is where you’ll specify the fields in your database that you want to search by. The database I’m working with is not exactly set up very well, so I had to use a little trickery. What I’m trying to accomplish is finding all students for a specific teacher for any period. Teachers teach anywhere from 5-7 classes in a day, but they are stored with a rather strange relationship. That is a little outside the scope of what I’m trying to show here. To move forward, I’m searching by 2 fields: last name and first name which are stored in a table name Teacher 1. There are other related tables named Teacher 2 through Teacher 7. But because filemaker is only letting me search based a specific field, rather than a generic input, I have to use some variables.

Before getting to that, I want my script to check if the OK button was clicked from the dialog window. You can do that by using the following condition statement:

If[Get(LastMessageChoice)=1]
    ...
    do stuff here
    ...
End If

As stated before, 1 references the rightmost button which in this case means they clicked OK. I’m now going to use Set Variable to set 2 variables, one for firstname and one for lastname. In reference to my database structure, the code looks like this:

Enter Find Mode[]
If[Get(LastMessageChoice)=1]
    Set Variable[$lastname, Value:Teacher 1::Lastname]
    Set Variable[$firstname, Value:Teacher 1::Firstname]
End If

Variables are prefixed by the dollar sign $. This sets $firstname and $lastname to the data that was just entered in the custom dialog box. Variables can’t be used directly in the find script, which is another reason we are still in find mode. Any fields we set will be set as search parameters. We can now reference the other teacher tables to get all students, rather than only those in 1st period.

To set the variables we have to use the Set Variable. This part got a little bit lengthy in my script because I’m setting fields for all teacher tables. Just to illustrate this, I’ll show you with finding only data in Teacher 1 and Teacher 2.

Enter Find Mode[]
If[Get(LastMessageChoice)=1]
    Set Variable[$lastname, Value:Teacher 1::Lastname]
    Set Variable[$firstname, Value:Teacher 1::Firstname]
    Set Field[Teacher 1::Last Name; $lastname]
    Set Field[Teacher 1::First Name; $firstname]
    New Record/Request
    Set Field[Teacher 2::Last name; $lastname]
    Set Field[Teacher 2::First name; $firstname]
    Perform Find[]
End If

Now, you may see that setting Teacher 1 is a little redundant, but I felt it was a little more consistent. The New Record/Request call acts like an -OR- operator. Using Set Field one after another acts like and -AND- operator. Finally, calling Perform Find[] will execute the search.

Once you have created the script you can attach it to a button and assign it to this script. Now the user can click a button and enter the search data.

You may ask, what’s the point, why not just enter find mode and do the search manually. The reason I did it this way is because I was generating a report that did not have the search fields in the layout, which would make it impossible to simply enter Find Mode and do the search. Using the scripting engine allows me to search based on any related table even if it is not shown on the layout.