Getting the total from one field in several records based on a condition
Hi folks. Very much an FM newbie, but slowly getting the hang of it.
I have two tables, Jobs and Estimates
In the Jobs table I have a relationship to the Estimates tabled named Estimates_Inhouse
In the Jobs table I have a layout showing related Estimates_Inhouse records in a portal, all is well.
There is a text field in Estimates_Inhouse named "Billing Category"
There is a number field in Estimates_Inhouse named "Total", it is a dollar amount
What I'm trying to do is:
On my layout in Jobs, I want to be able to SUM up all the Total fields, but only if the first 4 characters of the Billing Category match a certain string.
I created a field in Jobs:
fldGrandTotal = Case(Left(Estimates_Inhouse::Billing Category;4)="1_DF";Sum(Estimates_Inhouse::total);"")
The problem is that the result seems to only have data from the very first associated record in Estimates_Inhouse. For example JOBID 3333 has the following records in Estimates_Inhouse:
1_DF Billing Cat > $500
1_DF Billing Cat > $250
1_DS Billing Cat > $200
Always results in:
fldGrandTotal = $950
It should be $750
I don't think this is a syntax problem. Perhaps I'm going about this all wrong due to my greenness
Thanks for listening!