ExecuteSQL in Filemaker 12

Early last year, I wrote an article on executing SQL queries in FileMaker. As I mentioned in the article, with FileMaker Pro 11 and earlier this is possible, but can only be done with the aid of plugins like DoSQL. However, FileMaker 12 has added in a new calculation step called ExecuteSQL which allows you to do this natively in FileMaker.

The ExecuteSQL function accepts 4 parameters, which are listed below.

• sqlQuery – this is the actual SQL query that you want to execute.
• fieldSeparator – this is the character used ti separate field in the result. If left blank, FileMaker uses a comma to separate fields
• rowSeparator – this is the character used to separate records. If left blank, FileMaker uses a carriage return to separate records
• arguments – one of the things you can do is use a question mark (?) in a query ti indicate a dynamic parameter; this acts sort of like a variable which allows you to dynamically input data into sql query. You can have multiple arguments in this section.

As an example, lets assume you are on a company record, and you want to find the name and phone number of all companies in the same province and country as the current company. The calclation would look something like this:

ExecuteSQL ( “select name, phoneNumber from company where province=? and country=?”; “” ; “” ; province; country )

In this case the sql query is using 2 dynamic parameters – one for the province, one for the country. We’ve supplied 2 fields as arguments – the province field and country field. So, if the province in the current record is ‘ON’, and the country is ‘CA’, then the query that actually gets sent to FileMaker would look something like this:

select name, phoneNumber from company where province=’ON’ and country=’CA’

The results of this query night be something like this (assuming that these are the only companies with province=’ON’ and country=’CA’):

CoreSolutions Software,519-555-5555
Test Company,519-555-6666

Note that the order of the arguments is important; the value of the first argument will get substituted for the first ? in the query, the second argument gets substituted for the second ?, etc. If you were to reverse the arguments as follows:

ExecuteSQL ( “select name, phoneNumber from company where province=? and country=?”; “” ; “” ; country; province )

Then the query would return a different set of results, since in this case we are specifying the province=’CA’ and country=’ON’.

While this function won’t be used in every solution, it is a handy tool to have, and while it is was already possible to do this with plugins, it is nice to now have it built in natively into FileMaker.