4 Replies Latest reply on Jun 28, 2012 10:13 AM by VeronicaDaigle

    Summing fields for related records only

    VeronicaDaigle

      Title

      Summing fields for related records only

      Post

      I know there was a similar post yesterday but I cannot seem to get this to work. I have a table where the user enters the type and amount of fuel that was delivered. There is an automatic field in the background for each fuel type that separates the input into the various fuel types. 

      So the user enters in 1 of 3 fuels: Clear Diesel, Dyed Diesel, or Unleaded Gasoline, then enter the amount in Litres. There are 3 fields which the user does not see, one for each fuel type. So if a particular delivery was Gasoline, the hidden field Gasoline for that record automatically is set to be equal to the amount of fuel for that delivery, and the fields for Clear Diesel and Dyed Diesel are set to zero. So each record has one of the three fields that is non zero.

      Now on my Daily Report layout I have a portal which allows users to enter the fuel deliveries for that day and shows all deliveries for that date through a relationship between the daily report date and the fuel delivery date. Below this portal I have a totals row that shows the total for each of the fuel types that are delivered on that day (so the records which are visible in the portal), which is suppsed to sum the hidden fields on the related records only.

      I have the Sum fields set up to autoenter a calculated value which replaces the existing value. The calculation should be fairly straightforward, as this is nearly identical to the examples shown in the filemaker help file for the Sum function. The calculation I have set up for the "Sum Dyed Diesel" field (located in the Daily Report table) is:

      Sum ( Daily Fuel Use::DyedDiesel )

      The calculation is based on the context of the Daily Report table, and no other tables are listed when I tried to play with that to see if it was the problem. I thought maybe the "Sum Dyed Diesel" field needed to be located on the Daily Fuel Use table instead of the the Daily Report table, but would that sum every record, instead of just the related ones, and how would I then show the correct value on the Daily Report layout? It is probably something silly that will make me feel dumb, but if anyone knows what it is I would appreciate the help! Thanks

        • 1. Re: Summing fields for related records only
          philmodjunk

          I have the Sum fields set up to autoenter a calculated value which replaces the existing value.

          That won't work. Auto-enter calculations that refer to fields in other tables will not automatically update when the data in the other table is changed.

          Change these to a fields of type calculation ( the auto-enter calculation will automatically show up as the calculation for the calculation field) instead of number and they should update correctly for you.

          • 2. Re: Summing fields for related records only
            VeronicaDaigle

             

             

            So I changed all my sum fields to calculation fields, but now they seem to be summing only the first record for 2 of the different fuel types, and the other type just stays at 0.

            Here are the values I entered as a test 

            DeliveryID Date Vendor Fuel Type Litres of Fuel Delivered DyedDiesel ClearDiesel Gasoline
            44 18/06/2012 Test Vendor Unleaded Gasoline 450 0 0 450
            45 18/06/2012 Test Vendor Unleaded Gasoline 125 0 0 125
            46 18/06/2012 Test Vendor Clear Diesel 200 0 200 0
            47 18/06/2012 Test Vendor Dyed Diesel 180 180 0 0
            48 19/06/2012 Test Vendor Clear Diesel 450 0 450 0
            49 19/06/2012 Test Vendor Dyed Diesel 145 145 0 0
            50 19/06/2012 Test Vendor Unleaded Gasoline 80 0 0 80
            51 19/06/2012 Test Vendor Clear Diesel 95 0 95 0

            And the values I am getting as sums:

            For 18/06/2012 Dyed Diesel=180, Clear Diesel=0 Gasoline=450

            For 19/06/2012 Dyed Diesel=145, Clear Diesel=450 Gasoline=0

            It seems to be only taking one value from each field for each day, but I am not sure what the logic it is using is. I've attached a quick print screen of the calculations I have.

            • 3. Re: Summing fields for related records only
              philmodjunk

              The value returned for the Dyed Diesel sum is correct for your test data. Why the other two are incorrect, I can't tell from what you have posted to this point.

              Compare what you have to this demo file: https://dl.dropbox.com/u/78737945/SumFunctionDemoFuel.fp7

              • 4. Re: Summing fields for related records only
                VeronicaDaigle

                Ok I knew I was doing something dumb, I was displaying the wrong field in my layout for the other two sum fields, it was displaying the value for the Daily Fuel Use table... DUH! It was working the whole time, but now I have it displayed correctly. Thanks for the help Phil!