Executing with SQL

I hope you find this article interesting and, even better, useful to you in your efforts.


After you’ve read the post, please take a moment to add a comment.  (It helps me to know if I’m hitting the target with what I’m posting about).  Anybody who comments will automatically be entered into a monthly drawing for a copy of my new book, due for release at the end of 2017.  (1 out of 10 people will win a copy)

If you have bought either of my two previous books and haven’t yet written a review on the iTunes store, please do so and then email me a copy of the review (so I know your email address) and you’ll also be entered into a different monthly drawing where 1 out of 10 will also get a free copy).

Thanks and please don’t hesitate to get in touch if I can help you.

Michael Rocharde
(303) 856-5778

FileMaker™ has long had the ability to query SQL databases using ODBC and in FileMaker™ 12 (I believe) the ExecuteSQL  function was introduced.  I played with it a little but didn’t really find a valid use for it, that is, until recently and I’ve been able to, with the tremendous help and patience of Colin Campbell in Boulder,  solve two rather tricky FileMaker problems.

This post covers one of those two problems and I’ll be covering the other one in a later post.

The client manufactures and sells off-road vehicles with most of the parts being sourced in China.  He sells those vehicles world wide and has a network of dealers in multiple countries and they use a WebDirect solution to login and order both parts and vehicles.  The requirement (before I came on the scene) was to have a Languages option so that each dealer could select their own language and see all of the Parts Names in their own language.

To accomplish this, a Languages tables was added and then a secondary table for the translations; this table being named LPN.  From the Parts table, a user could enter the different translations into a portal by first selecting the relevant Language and then entering the translation.  The initial phase was to have 7 languages so a single part could have 7 related records in the LPN table.

However, with around 1300 parts, that was a lot of related records to create and to keep track of which ones needed to have the translation still done as well as view all of the translations in a single table view was a little complex.  Enter ExecuteSQL.

Let’s take a look at the data we have to work with.  Below is a grid showing some of the 3169 records that are, currently, completed.

I’ve added a fake translation record for the two languages that weren’t being used for this part for testing purposes and will delete them once it is done.

Now we need to add a table to display all of the translations and I’m going to call this table Translation_Grid.  We only need 1 field for data entry and that is the Part Number itself; every other field is going to be a calculation.

Firstly we need to add a calculated field for each Language and that calculation is simply the primary key for the Language.

Then we add the calculation fields that use ExecuteSQL to extract the correct values from the LPN table.  The image below shows all of the fields in our table

Now let’s take a look at our first calculation, the one we use to extract the French translation.

Let ( [ parameter1 = French ; parameter2 = PartNumber ; response = ExecuteSQL ( “SELECT partnameText FROM LPN WHERE id_languages = ? AND Part_Number = ?”; “” ; “” ; parameter1 ; parameter2 ) ] ; response )

Parameter1 is the field that gives us the primary key for the language.  For each subsequent language, all we have to do is change that parameter so once the first calculation is correct, it is incredibly simple to duplicate it for all the other languages and just change the language field used as parameter1.

Let ( [ parameter1 = French ; parameter2 = PartNumber ; response = ExecuteSQL ( “SELECT partnameText FROM LPN WHERE id_languages = ? AND Part_Number = ?”; “” ; “” ; parameter1 ; parameter2 ) ] ; response )

So, let’s break this down.  We’re setting parameter1 with the language primary key and parameter2 is the Part Number.

In the calculation we are grabbing the field partnameText from the LPN table where the id_languages = parameter1 and the Part_Number = parameter2.

It looks simple and it really is BUT SQL is very fussy so you have to be very careful to get everything absolutely right or you’ll get a ? as the result.

One thing to notice is that there is no normal relationship established between the two tables.  ExecuteSQL is creating the relationship on the fly in order to grab the response.

Having now added all of the translation calculations, after testing that the 1st calculation did actually work, I can now do a full test by creating a blank record and entering the Part Number that I’m using for my test record.

As soon as I exit the field, ExecuteSQL will populate the values.

So let’s quickly look at how this looks for multiple parts.  It is easy to see which translations are missing and very easy to export an XLS file to give to a translator.  (In that file, you need to include the Part Number, the Language field, the original English name and the field for the translation).  What I did was simply find all of the missing translations for each language (Enter Find mod and type an = sign in the field and then hit Enter).  Then export those records and send the file to the translator for that language making sure they understand that they are to enter the translations and must not change any of the other fields.

Then when the file comes back, one simply imports that file into the LPN table and you’re done; well, not quite.

ExecuteSQL stores the results but won’t recalculate them until you force it to.  So as you add translations, you need to force the update.  Typically, I simply have a script that deletes records in the Translations table as those records are changed and then add them back in.  For a large update, I’m going to import the translations and then go from there to the related records in the Translations table, delete all the found records and then import just the Part Numbers from the Excel sheet that I just received.  (It’s all a scripted routine and very easy to do but I’ll cover that in a subsequent post and put together an example file.

$14.99 at the iTunes store.

Click cover to order.

Money Back Guarantee

$14.99 at the iTunes store.

Click cover to order.

Money Back Guarantee

Leave a Reply

Subscribe To Our FileMaker Tips & Tricks

Join our mailing list to receive the latest FileMaker tips, tricks and video how tos from Michael Rocharde

You have Successfully Subscribed!

Subscribe To Our
Video Posts

Join our mailing list to receive info on new videos as we post them

You have Successfully Subscribed!

Website Apps