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”?
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.
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.
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.
Yes, great point. That is the gigantic downside to that approach.
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.
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.
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.
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
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.
Imagine a phone that already has the GPS data; you just need to unleash it such that Airtable can have it.
I don’t think this is about consumer mileage. More likely a fleet of trucks or something.
@kuovonne Your implementation works perfect! Also thanks @ScottWorld That works too, but I definitely wanted to keep the date as the primary field.
Also thanks @kuovonne and @Bill.French for noting the use cases. They all apply for me.
I’ve actually been using Road Trip (Road Trip for iPhone and iPad - darrensoft.ca) for many years and it’s worked perfectly fine. However, as was mentioned, there are a few reasons for wanting to move this into Airtable:
- Full control/Owning my data (Road Trip actually lets you export everything to csv and even automatically), but I still like the idea of having this in Airtable
- Logging other things: I log my service records in Road Trip as well, but how it handles it’s text fields isn’t as nice as Airtable
- Accessible on my Mac: Road Trip is iOS only (at least for now) I have an M1, so it’s possible it could become available on macOS, but so far running iOS apps on Mac hasn’t been super useful in most cases.
- I already have so much other data in Airtable, moving this into a centralized location is attractive.
@Bill.French LOL, no fleet of trucks here, literally just a 2011 Equinox and a Tesla. Which is the other reason why Road Trip is actually not used that much anymore since our daily driver is the Tesla, which I have connected to TeslaMate (GitHub - adriankumpf/teslamate: A self-hosted data logger for your Tesla 🚘), so all it’s data is automatically recorded for me. I just record service logs, which in the two years we’ve had the Tesla haven’t been much obviously!
The Equinox isn’t driven that much, so I’m not entering fuel logs that much either.
I was gonna’ say - just get a Tesla and automate this end-to-end, but I figured that was presumptuous. The best part is no part at all.