Cannot refresh when using executeSQL

[Spidey]

Hi,

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.

KC

[Wim Decorte]

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/

 

[comment]

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.

 

[Wim Decorte]

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.

[comment]

3 minutes ago, Wim Decorte said:

a performance dog

LOL. Auto-correct can be hilarious at times.