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