Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 30, 2022 10:42 AM
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”?
Apr 30, 2022 11:26 AM
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.
May 01, 2022 04:36 AM
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.
May 01, 2022 06:01 AM
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.
May 01, 2022 06:19 AM
Yes, great point. That is the gigantic downside to that approach.
May 01, 2022 06:36 AM
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.
May 01, 2022 09:46 AM
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.
May 01, 2022 04:30 PM
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…
May 01, 2022 05:11 PM
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.
May 01, 2022 06:19 PM
Imagine a phone that already has the GPS data; you just need to unleash it such that Airtable can have it.