AnsweredAssumed Answered

Getting the total from one field in several records based on a condition

Question asked by JK on Jan 20, 2011
Latest reply on Jan 20, 2011 by JK

Title

Getting the total from one field in several records based on a condition

Post

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!

Outcomes