AnsweredAssumed Answered

What's more efficient?

Question asked by lcot17 on Mar 14, 2016
Latest reply on Mar 14, 2016 by BeatriceBeaubien

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?

Outcomes