PDF Estimated Time To Read This: 2 – 3 minutes
I’ve spent the last several years working on solutions using a variety of databases – primarily FileMaker, but I’ve also used MySql, SQL Server and even Oracle a time or two. One of the things I like about using SQL databases is that you can join tables together pretty much on the fly. In FileMaker, if I have a calculation that requires a relationship that I haven’t defined, it means that I need to go to the relationship graph, create a new table occurrence and a new relationship. Not necessarily a big deal, but if you’ve got a lot of these calculations, your relationship graph can get complicated fairly quickly.
I recently came across a thread in the FM Forums discussing running SQL commands in FileMaker (click here to view it). It is possible to run SQL commands in FileMaker – there’s just nothing in the FileMaker interface that allows you to do so. It is possible using plugins though. There are several plugins that allow you to do this – among them are ScriptMaster Advanced (360Works), DoSQL (SH Partners / MyFMButler) and MMQuery (CNS). I did some testing using DoSQL, but the article above discusses the other plugins as well. It’s actually fairly easy to use – below I have a simple calculation that I created:
mFMb_DoSQL( “select name, province from company where country=’CA’” )
If you aren’t familiar with SQL, this statement simply retrieves the contents of the name and province field from the company table (note – you need to use a table occurrence name here, not the table name), where the value of the country field is CA. This statement is then run through function mFMb_DoSQL – below is a sample result:
CoreSolutions Software|ON¤Test Company|ON
By default, fields are separated by the ‘|’ character, and records are separated by ‘¤’ ( although there are functions that allow to change those values). So in this case two records have been returned – one for CoreSolutions Software in Ontario, and Test Company in Ontario.
While I haven’t had a chance to use this in a full solution, it’s something I’d definitely like to use in the future. I can definitely see it being useful, and a good way to cut down some of the complication in the relationship graph.
2 Comments
Nice intro, Alan!
One suggestion: You may find it useful to abstract out direct calls to vendor-specific plug-ins by creating a simple custom function named something like, well, “sql”:
sql ( sql_query ; col_separator ; row_separator )
The nice thing about this approach is that you can then switch sql calls for an entire project between vendor plug–ins simply by changing your cf. As a nice by-product, you can also have your cf parse any feedback like error messages from the plugin and store it in a global variable, making it easy to get this info regardless of the plugin used.
Looking forward to reading more…
Thanks for the feedback Brian!
The custom function is a good idea – I’ll have to build something like that in the future. I’m still playing around with different plugins, so something like this will definitely come in handy.