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
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.