Developer’s have been able to run SQL queries against their FileMaker data for many years. In the early days it required a 3rd party plugin, but since version 12 we’ve been able to run SQL Select statements against our FileMaker data with the native Execute SQL function. RazorSQL can be a big help.

The advantages and disadvantages of SQL in FileMaker are pretty well known. It can help keep your relationship graph simpler, allow you to query data without changing your context, and makes virtual lists easier to build among other things. On the down side, you have to be very careful about its impact on your application’s performance and building SQL queries in FileMaker’s calculation dialog is challenging.

With regard to the last issue, RazorSQL is a nice tool for working directly with your FileMaker solution to very quickly test SQL code. Once you have your query perfected in RazorSQL, you can then build it in FileMaker, knowing that you have the basic query correct.

RazorSQL connects to FileMaker via the ODBC interface, so you have to have that enabled in your standalone solution or FileMaker Server. If you are working with a standalone database, you can only connect from RazorSQL to your solution on the same machine. For the remainder of this post, I’m going to limit myself to talking about FileMaker Server.

In addition to enabling ODBC in the FileMaker Server Admin tool, the privilege set you are working with needs to have the access via ODBC extended privilege enabled.

Finally, your firewall needs to allow RazorSQL to connect to your FileMaker server on port 2399.

Once you have all that set up, its fairly simple to configure a connection to your solution and start making queries. RazorSQL has a bit of documentation on this for FileMaker.

I particularly like the ability to highlight a query and click the execute button to run just the highlighted text. It makes it easy to leave off the ‘where’ clause or the ‘order by’ clause. The free form nature of the query entry section lets you easily work with multiple versions of your query and quickly copy and paste to create a new one.

Below is a quick example of a simple query against a FileMaker file with a table called ‘Assets’ and the three records that are the result of the query.

RazorSQL can connect to many ODBC data sources, so you can use it to test queries against MySQL, SQLServer, etc… This makes it a very helpful tool when doing integration projects between FileMaker and other environments when you are using ODBC.

The results of your query can be easily exported in a variety of formats, so RazorSQL can also be very helpful as an aid to data migration or ad hoc reporting.

~John Newhoff