2 Replies Latest reply on Jan 20, 2011 9:37 AM by JK

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



      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!

        • 1. Re: Getting the total from one field in several records based on a condition

          Left(Estimates_Inhouse::Billing Category;4), since it is defined in jobs, only refers to the "first" related estimates records. If it starts with those letters, the Sum is computed. Since Sum(Estimates_Inhouse::total) computes the total of all related estimates records, you get the total you are seeing when the first record's category field contains text starting with 1_DF.

          You'll need to use one of two approaches here:

          Option 1

          Define this calculation in Estimates, in a calculation field, c1_DF Total:
          IF ( Left(Estimates_Inhouse::Billing Category;4)="1_DF"; Total);"")

          Then Sum(Estimates_Inhouse::c1_DF Total) will compute the correct total. This does indeed require a separate calculation field in Estimates for each such category where you want a total.

          Option 2 (Requires FileMaker 11 and can require extra window refreshes)

          Define a summary field as the Total of your total field.

          Place this summary field on your layout inside its own one row portal that refers to the same table occurrence in show records from as your first portal.

          Define a portal filter expression: Left(Estimates_Inhouse::Billing Category;4)="1_DF"

          The advantage here, is that you can make the same "Grand Total" compute and display totals for different categories simply by changing the value of a global field referenced in the filter expression or you can put several copies of this portal and summary field on your layout, but with different filter expressions to get different category based totals.

          • 2. Re: Getting the total from one field in several records based on a condition

            Thanks very much - that makes perfect sense.  I created my SUM in the Estimates table based on the condition (value is zero if condition is not met) and then on my Jobs table i added a SUM of that field.

            Haven't made the jump to 11, so I think this will do - thanks very much.