Jump to content

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


This topic is 3027 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Edited by steveald
Link to comment
Share on other sites

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.

Edited by steveald
Link to comment
Share on other sites

Could you provide a better description of the problem? I don't understand the fields involved, esp this part:

22 hours ago, steveald said:

One field shows the difference in how much fuel was added by the individual that created that record compared to the previous record.

I don't quite see the significance of such field. What exactly is your goal here?

 

Link to comment
Share on other sites

This topic is 3027 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.