Maybe something like this?
Let ( [
F1 = "SELECT
SUM ( \"Material Quantity Received\" ),
SUM ( \"Material Quantity Used\" )
STATUS NOT IN ( 'Used', 'Rejected', 'Expired' )" ;
F2 = ExecuteSQL ( F1 ; " - " ; ¶ ) ;
F3 = Evaluate ( F2 )
One more thing, if Status has nulls, you may need an additional WHERE statement.
Thanks for the reply. Sorry Im using Filemaker 7.
You'll probably want to create a relationship that has the status codes you want as a list. You can get that via the List function (for simplicity):
List ( Quarantine ; Near to Expiry )
Then add this field to whatever other relationship you have in place. Your calculation can then just use summary fields in the child table to get the totals. (e.g., a summary field that is the total of Quantity Used)
FileMaker 7.... hmmmm... goodness... what are you able to run that on? Why?????
Unfortunately the OP has FileMaker Pro 7 so they have no List function.
So you can use a calculation field to get the quantity for each record:
Materials Quantity Used Reportable
If ( status ≠ "Used" or status ≠ "Rejected" or status ≠ "Expired" ; 0 ; Material Quantity Used )
Then use a Summary field to sum those values for a group.
I saw "7" and thought ".fp7". Oops.
Nevertheless, you can just create the field this way:
"Quarantine¶Near to Expiry"
And yes, you can use the "old school" method to get the total as well. But I would still use a summary field rather than a calculation. More flexible, and performs a bit better.
Sorry my bad...Im supposed to say..filemaker 11.
As a further point: If you just use a Count summary field and a filtered relationship, such as what I've suggested, then there's no need for the additional field. It'll be more flexible as well, since you don't have to change database schema if the list of status codes changes.
You need two fields. The first is the one you already have. The second is a summary field that totals the first field.
The layout is showing the value of the last record. That is correct, if it's the first field. Create the second field and put that in the footer.