It Does What Now?

Let me begin by saying the possibility for unexpected behavior exists in any area of life. Now, I do not have a mountain of empirical evidence to back up my statement, but I do have a German Shepard. Several weeks ago, my wife and I were watching TV after supper and realized the dog was missing. Concerned that we had put her outside and then forgotten to bring her back in, we immediately went to check. While she rarely leaves the yard, our dog was no where to be found. Before searching the neighborhood in a panic, we came back into the house to check again. We discovered that she had decided it was bedtime and had gotten tired of waiting for us. She had trotted upstairs and was now curled up in her spot on the floor beside our bed, fast asleep.

This unexpected behavior from the four-legged member of our household got me thinking about the unexpected behavior I’ve encountered while working with the FileMaker Platform, if only because that little anecdote would serve as an excellent introduction to the topic. Sometimes the unexpected is a pleasant surprise and at other times not so much. There are a few examples I could choose, but I’ll start with the most recent. (Depending on the response, this blog entry could become the first in a series surrounding this theme.)

To adequately explain, let me start at the beginning. The FileMaker 12 Platform added the ExecuteSQL ( ) function to the developer’s toolbox. For those of you not aware, this powerful function gives developers the option to use the SQL SELECT statement to generate a return-separated list of values. The power lies in the ability to return meaningful results without worrying about the context of the current script and function. The developer is also able declare relationships (or JOINS) on the fly independent of any existing relationships declared via Manage Database.

Due to my unfamiliarity with SQL and the complexity I perceived with this new function, I was hesitant to use it in my own development. After attending a few FileMaker Developer Conferences, I began to see the advantages with employing ExecuteSQL ( ) as I also became more comfortable with the function’s syntax. I also discovered that it should not be used everywhere, but it is a great complement to the features that the FileMaker Platform has provided users since the very beginning. Since that time, I have used it sparing but would not longer hesitate once determine it was the best tool for the given task.

So, that’s the background; now, let me set the stage for the scenario where I first noticed the unexpected behavior.

Our client was collecting registration information using a Google Form. Then, that data was to be imported into a FileMaker solution for payment tracking and reporting. Rather than import the data from the spreadsheet directly into the primary database table, we set up a process to first import the spreadsheet into a “holding” table. This extra step allowed us to clean-up the values and preserve data conformity i.e. remove extra spaces, remove carriage returns, etc. We were also able to check for any existing and/or duplicate entries before transferring the information into the primary table. The reason for this extra step in the import process is not because I don’t trust users to provide the required information in the format requested, but because I just don’t trust the users. Before you ask, I’m not a pessimist. I’m simply an optimist with experience, but I digress.

While we experimented with several methods, the technique that gave us the most flexibility was the use of ExecuteSQL ( ). We were able to search the primary table using information from the current record in the holding table as we looped through the found set. We were able to quickly check for an existing registration by full name, first initial and last name, and last name only. If we returned a single value, the script was designed to take one action automatically. If we returned multiple values, the script would pause and wait for input from the client to make a choice.

The ExecuteSQL ( ) calculation we used to check by first initial and last name looked like this:

Let ( [
   fInitial = Left ( Holding_Table::FirstName ; 1 );
   lName = Holding_Table::LastName ;
   query = "SELECT a.RecordId
              FROM Primary_Table a
              WHERE a.NameFirst LIKE ?
                 AND a.NameLast LIKE ?"
] ;

ExecuteSQL ( query ; "," ; "¶" ; "%" & fInitial & "%" ; "%" & lName & "%" )

)

At first glance, this approach worked very well. But then, the unexpected behavior revealed itself. While SELECT statements in SQL are not case-sensitive, the results returned by my SELECT statements using ExecuteSQL ( ) were. As noted above, I don’t trust end users. How could I check all existing registrations if one person leaves the Caps Lock key on while another doesn’t know why the Shift key even appears on the keyboard. Luckily, I work with a team of talented FileMaker developers, and they were quick to share the resolution with me.

As in FileMaker, SQL includes functions to force upper case or lower case. By using both the FileMaker and SQL functions in the calculation, we are able “insulate” the results of the SELECT statement from the unexpected behavior I experienced. The updated calculation now looks like this:

Let ( [
   fInitial = Upper ( Left ( Holding_Table::FirstName ; 1 ) ) ;
   lName = Upper ( Holding_Table::LastName ) ;
   query = "SELECT a.RecordId
              FROM Primary_Table a
              WHERE UPPER ( a.NameFirst ) LIKE ?
                 AND UPPER ( a.NameLast ) LIKE ?"
] ;

ExecuteSQL ( query ; "," ; "¶" ; "%" & fInitial & "%" ; "%" & lName & "%" )

)

Since making the change, the entire scripted process has worked exactly as intended. Also, I automatically include the few extra functions highlighted above to ensure that my SELECT statement is returning the expected results. Just as I relied on the assistance of my co-workers, I hope this brief write-up helps you when using ExecuteSQL ( ) in a similar fashion.

Until next time,