Summing fields for related records only
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