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.
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.
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. :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…
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:
@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.