4 Replies Latest reply on Mar 14, 2016 12:48 PM by BeatriceBeaubien

# What's more efficient?

What is the most efficient / correct way to approach the following problem?

Q: Calculate the Sum 'Net Total' of ALL invoice records in a table IF the status is 'Complete"?

From the way I see it - I have two options?

Calculation or SQL query.

Option 1:

1) Create a new field in the 'Invoices' table named complete_total:

If ( Status = "Order Complete" ; Net Total ; 0 )

2) Create a calculation field in the Report layout:

Sum ( Invoices::complete_total )

Option 2:

1) Create a field in the Report layout:

ExecuteSQL ( "

SELECT Net Total

FROM Invoices i

WHERE i.\"Status\" = ? " ; "" ; "" ; "Complete" )

What would be my best option? Create two calculation fields in two related tables or one SQL query in the target layout?

How would I expand this solution to cover Date ranges?

So... Calculate the Sum 'Net Total' of ALL invoice records in a table IF the status is 'Complete' AND between '01-01-2015' and '01-01-2016'?

Is there an even better option to any of my two solutions?

• ###### 1. Re: What's more efficient?

I'm sure plenty of folks have an opinion on which of these is a better idea. The truth is, what's a better idea is almost always specific to your particular situation. Other folks' experience does not necessarily apply very well. The technique that turned out to be faster for one application frequently turns out to be the slower technique in another application. The best answer, most applicable to your set-up, is for you to implement both and compare them. (The Get ( CurrentTimeUTCMilliseconds ) function is your friend.) A test of what's actually faster in your context can save a lot of hemming and hawing comparing the subtleties of why one solution should be faster than another in a hypothetical general case.

• ###### 2. Re: What's more efficient?

The issue might be further upstream. If your Net Total field is something like Sum(related::line_items), then you're looking at an unstored calculation, which will become slower and slower as the number of records increases.

My experience with accounting-related solutions has been that it's best to not have unstored numbers in any fields that will be used for reporting.

As jbante says, you should test it yourself, but in general ExecuteSQL will not provide speed benefits, with the possible exception of mitigating the delay introduced be switch layouts or opening new windows. And I wouldn't recommend that you put an eSQL calc in a field definition -- that's really going to get slow.

• ###### 3. Re: What's more efficient?

For max performance you will probably have to propagate the invoice status to the single line items when it changes, then do your finds and sums in the line items table.

• ###### 4. Re: What's more efficient?

Hello lcot17,

It's important to get metrics to evaluate alternatives, as he said. Only test data may be available in the first instance. It is strongly recommended that the developer not consider this the end of the optimisation process, as when production data populates the database, process improvement assumptions may need to be re-evaluated and the metrics re-assessed.

This was articulated by Jon Thatcher, the FMS developer, at one of his under-the-hood DevCon sessions, and I consider it good advice when choosing among different ways to aggregate numbers.

Best wishes,

Beatrice Beaubien, PhD

FileMaker 14 Certified Developer

Knowledge Translation Certified Professional

On Mar 14, 2016, at 12:08, jbante <noreply@filemaker.com> wrote

What's more efficient?

reply from jbante in Discussions - View the full discussion

I'm sure plenty of folks have an opinion on which of these is a better idea. The truth is, what's a better idea is almost always specific to your particular situation. Other folks' experience does not necessarily apply very well. The best answer, most applicable to your set-up, is for you to implement both and compare them. (The Get ( CurrentTimeUTCMilliseconds ) function is your friend.) A quick test of what's actually faster can save a lot of hemming and hawing comparing the subtleties of why one solution should be faster than another.