Benefits of ExecuteSQL

Previously in this blog I have discussed the new ExecuteSQL functionality in FileMaker12 and how useful I find this. I would like to share with you one example of how I have used this.

I have a client who has a sales system which has a report that shows the position of Accounts for all their account customers.
This was previously created using a simple Find in the Contacts table for all the account customers which shows a sub-summary of all the outstanding accounts. This is further summarised by customer category.
The report also shows the account debt split by age – up to one month overdue, up to two months, up to three months and over three months.
In total there are seven summary fields in the sub-summary part of the report and the same in a trailing grand summary part.

Originally the summary fields were adding up an un-stored calculated field which was using the sum() function. The field in the sum() function was another un-stored calculation over a complex relationship.

The result was a report that was very useful, but took up to 30 minutes to generate.

To speed up this report I replaced the

sum(complexrelationship::invoice_balance)

with

Execute (“SELECT sum(invoice_balance) FROM INVOICE TABLE WHERE contactkey=? AND date>? AND date<? AND accountsale=’YES’ ; “” ; “” ; contact::primarykey ; date1-1 ; date2+1)

I have used < and > on the date field as it appears to be quicker than using <= and >= (this is why I have modified the dates to allow me to use < and > based on the original dates). I could have also used the BETWEEN function, but this is very slow in FileMaker’s SQL.

The result of using the above function is a report that is now generated in only a few minutes. As you will appreciate this is a great improvement.

Simon Ward

Simon has been developing FileMaker databases since the late 90s and joined Linear Blue in 2006. Over this time he has developed database systems for clients in many different industries from Order Processing to Book Publishing. Simon’s BSc in Chemistry from Thames Valley University comes to the fore in his analytical skills and he is certified in FileMaker versions 7 through 12.

More Posts - Website

2 thoughts on “Benefits of ExecuteSQL”

  1. I like the aside about >= dates. Thanks for sharing.

    A question………

    I take it the following extract was an unstored calculation?sum(complexrelationship::invoice_balance)

    Did you simply change the calculation formula to
    Execute (“SELECT sum(invoice_balance) FROM INVOICE TABLE WHERE contactkey=? AND date>? AND date<? AND accountsale=’YES’ "; “” ; “” ; contact::primarykey ; date1-1 ; date2+1)

    or did you change the field to numeric and then populate it by a script step?

  2. In the database I was working on the report was generated from a Preference Table (single record) so the ExecuteSQL is in the field definition.
    I would NOT recommend using ExecuteSQL in an unsorted calculation as this slows down dramatically as the data set increase on size.

Leave a Reply