1 Reply Latest reply on Nov 17, 2015 6:08 AM by steveald

    How to display running total for one field for specific set of records

    steveald

      Apologies if this has already been covered somewhere. I couldn't find anything related closely enough. And, it may be something simple that is just not occurring to me.

       

      I have a FMP 14 server-based database that keeps records for a number of individuals that share a single vehicle. Every time one of them uses the vehicle, they create a new record in which they enter the details of that trip. One field shows the difference in how much fuel was added by the individual that created that record compared to the previous record. If the previous record showed a full tank of say 10 gallons and the current record shows the current user also filled it up to 10 gallons, then the difference would be zero. But, if the current user only put in 5 gallons, the difference would be -5. Or, if the previous record only showed 8 gallons and the current record showed 10 gallons, the difference would be +2.

       

      What I need is a field that shows a running total of those differences - unique to each individual. If the three examples given were all for the same person, their running total would be -3. So, if they are looking at a record with their name in it, they would see -3 in that field. Another individual looking at a record they created would see the running total of the difference field for just their records.

       

      I am only using Form views for this database - so a Summary may not work. Also, I have seen solutions to similar queries that suggest using ExecuteSQL. I am not at all familiar with that command, so I would appreciate as much detail as possible in any solution involving it.

       

      Next, I will need to apply the same solution to another field which will be further limited to records created in the current month. So, any additional details needed to make that work would be greatly appreciated.

        • 1. Re: How to display running total for one field for specific set of records
          steveald

          I came up with a solution. It's not pretty, but it works.

           

          It starts with a field common to all records called Fuel Difference. It calculates how much more or less fuel the current person adds in their record than the last person did in their record.

          The next step reveals the limitations of this method. A calculation field we'll call Bob's Fuel Bank checks to see if Bob is the driver listed in that record. If he is, it reads the current Fuel Difference value; if not, it enters zero.

          Next is a Summary field called Bob's Total Fuel Bank which calculates a Running Total of Bob's Fuel Bank, with Summarized repetitions set to Individually.

          Then, Bob's Total Fuel Bank Adjusted subtracts the current Fuel Difference from Bob's Total Fuel Bank so the current trip's lack of data doesn't throw off the numbers - since this data will be viewed at the beginning of the trip before the driver has added any fuel.

          These 3 fields are duplicated for each driver involved.

          Finally, Driver Fuel Bank, a field common to all records, shows the appropriate Total Fuel Bank Adjusted based on which driver is listed on that record.

          Of course, the limitations to this method are that you have to create calculation fields for each driver involved. But, in this case it's a small, static list; so that's not too much of an issue.

           

          Any suggestions as to how to make this process better are appreciated.