Jump to content

Cannot refresh when using executeSQL

Recommended Posts


I have a field to count the number of records between certain date in calculation:

ExecuteSQL ( "Select Count(*) FROM Order2 WHERE DueDate BETWEEN WeekStartDate AND WeekEndDate AND Decoration = 'Digital'"; ""; "")

It works the first time but it cannot refresh in the field when there are changes.  Is there anything I can do to refresh it?  Thanks.


Link to post
Share on other sites

If the field is unstored then it will be refreshed every time FM has to display it or otherwise reference it.

However; this is a classic case of where you would NOT want this to be a calculation and certainly not an unstored one.  If this calc fires when the record is not committed yet, it will be a very expensive operation since FMS will send the whole table down to the client to resolve that calculation.

If you have not seen that behavior in action yet, download the performance test file from my 2014 devcon presentation: https://www.soliantconsulting.com/blog/executesql-named-buckets/


Link to post
Share on other sites

To add to what @Wim Decorte said:

If you want/need this to be an unstored calculation, then use native Filemaker methods (e.g. an auxiliary relationship to filter the records to count, and the Count() function to count them) instead of SQL.


Link to post
Share on other sites

And to add one more option: if the underlying changes are part of a scripted workflow: set the count field as part of those scripts so that there is no slowness if you need to use it in reports.

Usually that is the approach with the most work vs. just relying on a field-level calc, but it is the approach guaranteed to not become a performance dog over time.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.