Vehicle Fuel Log - calculating miles driven between fill ups and MPG

[petebocken]

I’m creating a vehicle fuel log. I’d like to calculate the miles driven between fill ups (trip). After that, then I will be able to easily calculate the miles-per-gallon (MPG).

I know I could easily accomplish this by adding a column for “start odometer” and “end odometer” and then simply subtract the two numbers. But that is extra work each time I create a new entry. I’d rather just add the odometer reading at the time of the fill up and have it auto-calc based on the previous (above) entry’s odometer field. I know that since Airtable is not just a spreadsheet you can’t do what you’d do in Excel with something like “=sum(b2-b1)”. Also since the sort order could change, etc.

So I was thinking, what if I setup an auto-increment ID, that way I can reference to the previous ID’s odometer field. Since the previous ID would be one less than the current, that would work, unless I delete an entry. So that may not be the best option.

Is this possible or am I just stuck with manually typing the “end odometer” and copying it to the “start odometer”?

[kuovonne]

I would do this with a same-table link. Each record currently knows the odometer reading and the number of gallons to fill up. Have a same table link to the previous record. Then use a rollup to get the previous odometer reading and do the math to get the MPG. (Assuming of course that every time you get gas you do a complete fillup.)

Creating that same-table link is a bit more difficult. For historical records, you can copy/paste your primary field, offset by one. However, you also need a way to maintain the same-table link for future records. You can choose to do this manually as part of your data entry. Or you can have an automation create the same-table link.

There are many ways to maintain an ongoing chain of same-table links. They all involve identifying the latest record in a table. Here is my current favorite method.

• Have a checkbox field that is checked for only one record–the most recent record.
• Have an automation that is triggered on the creation of a new record. This automation has three actions. The first action is a ‘find records’ action that finds the one record that has the checkbox selected. The second action is to clear the checkbox from that record. The third action is to update the newly created record to have the checkbox selected and to also create the link to the found record.

[ScottWorld]

I think his automation can be much simpler than that. He could just create a formula field that is equal to the current record’s autonumber field minus 1. Then, when a new record is created, the automation can set the linked record field to the value of the formula.

[kuovonne]

ScottWorld:

Then, when a new record is created, the automation can set the linked record field to the value of the formula

The automation would be simpler. It would require the primary field to be an autonumber field, though. And I hate having the primary field as an autonumber field. It is so ugly and meaningless.

[ScottWorld]

Yes, great point. That is the gigantic downside to that approach.

[Furst_Name]

I know you can, but I would ask ‘why?’. This is a wheel that has been invented many times with apps available for both android and iOS mobile devices.

[kuovonne]

Furst_Name:

I know you can, but I would ask ‘why?’.

Everyone has their own reasons. Maybe there is a feature missing on these apps. Maybe these other apps have a fee that the OP doesn’t want to pay. Maybe the Airtable app is just super convenient and is used to log other things and it is easier to have fewer apps to manage.

One of the reasons why I have moved things like this to Airtable is because I want to own my own data. I got tired of having my data stuck in a proprietary database that can only be accessed in one particular app with no decent export functionality.

[Bill_French]

Furst_Name:

I know you can, but I would ask ‘why?’.

I agree with @kuovonne that there may be many reasons @petebocken is compelled to sing the same tune, but with a different arrangement. Arguably, Michael Bublé has transformed a few Sinatra songs into better, more pleasurable listening experiences. :winking_face:

I’d bet that Pete has some additional ideas in mind that dovetail with other data, a concept that is almost never possible with existing mobile apps of the consumer variety.

In any case, my approach would be no odometer entries ever…

• Find a way to capture GPS data which can be used to determine miles driven
• Collect refuelling info including GPS location, time, fuel volume
• Create an automation process that is always assimilating these data sets to provide the reporting details desired

[kuovonne]

Bill.French:

my approach would be no odometer entries ever

Lol. Most of us don’t have the knowledge to build a complex system involving GPS data like you, Bill.

I also would not use this particular system for calculating mileage because my car does it for me. But for many years I had a small notebook in my car where I logged mileage and fill-ups.

[Bill_French]

kuovonne:

complex system involving GPS data like you, Bill.

Imagine a phone that already has the GPS data; you just need to unleash it such that Airtable can have it.