AnsweredAssumed Answered

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

Question asked by steveald on Nov 16, 2015
Latest reply on Nov 17, 2015 by 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.