Log in
Hot & Trending on FMT

SQL for the Common Man

SQL for the Common Man SQL for the Common Man
l>

The most important feature in FileMaker 12 is arguably the ExecuteSQL function.  This is the first of an occasional series highlighting some real life uses of this new and exciting technique. One reason I’m learning to embrace ExecuteSQL is because it lets you perform many tasks without accessing the Relationship Graph.  Recently I started working on a large warehouse project.  This system has more than 200 users spread across the country with at least 50 active connections any any time.  Opening Manage Database to access the Relationship Graph on a hosted database with active users is problematic for a number of reasons that have been explored in detail elsewhere – you run the risk of file corruption if a connection drops, committing database changes can be difficult if users are modifying data, and so on.

So on this system, I can only open Manage Database during a predefined time frame.  Any work that I can do that doesn’t include the relationship graph is a plus.  ExecuteSQL lets me grab information I may not have a relationship for without creating new relationships.

Just like filtered and sorted portals helps you reduce the number of relationships in the relationship graph, you can use ExecuteSQL to do many tasks that were previously only able to get accomplished using the graph.

What is SQL?  In a nutshell it is the way most other databases perform what a FileMaker developer would call a Find Request.  Unlike FileMaker it is not done using a visual representation of a database like using a relationship graph and entering find requests in find mode.  SQL is widely used in most other database platforms and is completely text based.  A SQL query will have this type of structure:


Select <<These Fields>>
From <<These Tables>>
Where <<This equals That, etc>>
Order By <<Fields used in the sort>>

There can be a lot more to a Select statement, but this will do for a start.  As it is implemented in FileMaker, you write a SQL query like the one above, and it will return a list of the data you asked for. Lets show a simple example.  We have a database of people names, and I want all the people named John.  We can put an ExecuteSQL statement in a variable then show the result in a dialog box.  The SetVariable step would look something like this;

In this solution, there is a table called People, and two fields FirstName and FullName.  The capitalization is to make the statement more readable.  The Default Separators will always be two null strings; let FileMaker use the defaults.  Note that the Select string ends with a question mark.  The “?” refers to the Argument at the end of the statement (in this case the string John).  You can put in more than one ?, each one refers to a separate argument at the end of the statement.

A result from the above statement might look like this:

John Doe
John Dewey
John Wayne

Note that in the SQL statement, we didn’t reference any relationships.  This statement can be called from any context even if there is no relationship from the current layout to the table you are querying.  This point will be important later.

How can we use this great new tool?  Let’s look at an example that I came across is checking data entry.  In the sample database, we have a button to add new contacts.  A common problem is people adding contacts without first checking to see if they are in in the database.  In the past, this was done using a relationship to check for duplicates.  The user types the new name into a pair of global fields and clicks a button to add the names into the database.

The script attached to the Add Person button is shown below;

Note that in the second if clause, we’re checking whether there is a record down the ‘dupCheck’ relationship.  This relationship is based on the two data entry fields.  Using FileMaker 12 this relationship is totally unnecessary.

Using ExecuteSQL, we can check for duplicates without creating a new relationship.  Instead of a relationship to the contacts table based on the global fields, we perform a select statement.  If that statement returns a value, we know it is a duplicate.  In our database, the global fields are called gFName and gLName.  The SQL statement would look something like this:

ExecuteSQL("Select "__kp_PersonID" from Contacts where First =? and Last=?";"";"";ITSDemos01::gFName;ITSDemos01::gLName)

This gets replaced in the script where we need to check for duplicatse.  If this statement returns a number (the __kp_PersonID value) we know that the First Name, Last Name pair is already in the database.

There’s one gotcha with the statement as written.  SQL is not nearly as forgiving as FileMaker is when it comes to capitalization.  When used in a relationship or a Find, FileMaker ignores the case of the arguments.  A relationship based on two text fields will be true if one field is set to ‘John’ and the other is set to ‘john’.  SQL is case sensitive.  ‘John’ is not equal to ‘john’.  To overcome this let’s make both arguments all caps:

ExecuteSQL("Select "__kp_PersonID" from People where Upper(FirstName) = ? and Upper(LastName) = ?";"";""; Upper(ITSDemos01::gText1);Upper(ITSDemos01::gText2))

Another thing to keep in mind when using ExecuteSQL is that certain characters are not allowed to be the first character of a field name.  One of those is the underscore.  For those of you that follow the FileMaker naming conventions and prefix key fields with an underscore this can be a problem.  To get around this problem, enclose the field name in escaped quotes as above.  If you plan to use ExecuteSQL extensively you may wish to change your naming conventions accordingly.

In addition, we are no longer constrained by a limitation relationship graph, namely that when referencing a related repeating field one can only use the first repetition.  With ExecuteSQL, you can use a single global field with repetitions as a scratchpad/array for data collection and retrieval – one less relationship and fewer global fields.

Using this technique, we can check for duplicates without creating a new relationship that will be used for only this one purpose.  See the attached file for a complete demo of the technique.

Next month we will look at another simple way to use executeSQL.