New user with a few questions.
I’m trying to create a Car Maintenance Log database and ran into a small problem. I’m looking to manually input my mileage into a table and reference it from another table. I have tried several varations of formulas and can’t get what I’m looking for.
Hopefully someone can point me in the right direction. Thanks in advance.
What Tables do you have? Seems like you need the latest record: Find last entry
You pretend to have a global variable, that does not exist in Airtable. To only way I know to reference that cell is as you are doing, and I’d prefer to just go to that table to check the value. But I think there is a more interesting way.
I have a similar base with this tables:
In your Car Log you can track the Mileage of every Task, but I think you need a Vehicles table (even if you only have one), to link every Task with the car. Then you could add a Last Mileage field to the Vehicles table, and get the latest Mileage. Of course, you will have a historical mileage data.
I’m thinking you could also have the Vehicle in a SingleSelect field in the Tasks table, and you could Order By date and check the newest.
Thanks! I will give this a go and see how it works. Would you have screenshots of your tracker? Just want to have an idea of what someone else is doing and compare notes. Thanks in advance!
Actually, your first instinct — using a linked record as a way to fake a global variable — is a good one. (By which I mean I’ve done it, too. 
) However, you don’t want to use the mileage value as the primary field of the linked record, as that would require you to re-link every record in your main table whenever you update your current mileage. (Your ‘not a number’ — 'NaN' — issue is minor type mismatch that’s easy to fix.)
If you want to continue along this path, here’s the best way:
You appear to have the proper tables defined: your main table ([Car Log]) and a table to hold your current mileage ([Car Mileage]).
Your [Car Mileage] table should have three fields defined:
{Name}.{Mileage}. Set it to your current mileage (86,500 for now).[Car Mileage] back to [Car Log], created automatically. 'Link to multiple records' should be enabled.There should be only a single record (row) in [Car Mileage]. What you call it is unimportant, as it will ordinarily remain hidden. Typically, I call it something simple like '.' (the period character).
Each record in [Car Log] must be linked to the single row in [Car Mileage] — much as you have now.
In [Car Log], instead of using the linked-record field itself, use a lookup field, configured to access the [Car Mileage] table and look up the {Mileage} field.
Your {How Long} formula should be
{Service Due}-VALUE({Current Mileage}&'')
To reduce clutter, you can hide {LinkToMileage}.
Whenever you wish to update current mileage, change it in your [Car Mileage] table. (That is, don’t create a new record; instead, change the value in the row with {Name} of '.'.)
I’ve changed the Vehicle field to a Linked Record field, and in the new table I have added a Rollup for the last mileage:
Thank you @Elias_Gomez_Sainz and @W_Vann_Hall.
Almost have this completed! Still have a few questions.
@W_Vann_Hall:
I followed your directions and want to make sure I did it right. I also wanted to see if I could simply auto populate the data versus having to select it manually.
I followed the directions and modified it a bit. Column 1 and 2 are self explanatory. Column 3 links to my “upcoming maintenance” table.
Column 3 links to “upcoming maintenance” table and appears as Cars. Then I followed the lookup suggestion which appears as “Current Mileage.” This is manually done and I was wondering if this could be auto populated?
The formula worked perfect and calculates how many miles until the service. I maintain 2 vehicles and have a few questions for @Elias_Gomez_Sainz regarding his setup.
I’m debating the best way to build the database. I’m very intrested in the way you split your car/moto categories. I was debating keeping everything together and using “tags” to keep track of things. How would I split my car information like you have done for car/bike?
So far I have:
This would track the current mileage of my vehicles and act as a major source of data for upcoming maintenance. This might open a can of worms with pulling data to the upcoming maintenance mileage tracking.
From there my log would be maintained by date and a “tag” used to identify what car had the work done to.
Then I would track upcoming work needed for each vehicle and how many miles left to the service. Just need to figure out how to link the data and create a formula that identify which vehcile is being reported.
You could Group By as in my first screenshot, or you could use Views to Filter the cars, and view only one per view, por instance.
Got it! Thanks…!
Also figured out the manual populating of mileage and how to track upcoming service for both vehicles.
Thanks @Elias_Gomez_Sainz and @W_Vann_Hall
Once the base has been set up, all you should need to do is to manually update {Car Mileage::Mileage} — that is, the {Mileage} field in the [Car Mileage] table — to reflect your then-current mileage. You won’t need to re-do your links each time.
Thank you very much! One last question. Is there a way to calculate year to date expenses paid for maintenance/repairs? I would need to pull cost data and sort it somehow from January 1 - December 31. Would also need to reset amount annually. Any ideas or maybe point me in the right direction.
There is — but you probably aren’t going to like it.
Actually, I haven’t examined your final base, yet, but you might be part of the way there, if you’re using all-to-one linked records.
The basic steps can be found in my Sales CRM Dashboard base from Airtable Universe. (Be gentle: It’s an early effort.) The process is [poorly] described in this reply, but examining the base will probably tell you the most. You can find some of the techniques also in use in my Black Mirror base — or, for that matter, probably in at least a fifth of the 100 or so sample bases I’ve posted. (I really should post a more-generic description and example some time. The technique is related to the one I describe here, but it’s much simpler to implement.)
Essentially, you first set up a conditional field using a formula that says, more or less, IF(Year={ThisYear},{ExpenditureAmount}). Then, using a many-to-one link, you rollup the conditional field using SUM(values) as an aggregation formula. Your need — to sum by year — is a lot less hassle than doing so by month or week.
I’m trying your response on how to create a formula to filter and calculate expenditures by year to date. Heres what I have so far.
Essentially, you first set up a conditional field using a formula that says, more or less, IF(Year={ThisYear},{ExpenditureAmount}).
I set up the formula column and recieved the following error.
I then added the following columns and the formula worked.
I’m not sure if I’m heading down the right path following this advice.
Then, using a many-to-one link, you rollup the conditional field using SUM(values) as an aggregation formula. Your need — to sum by year — is a lot less hassle than doing so by month or week.
Field 6 links to Maintenace Log.
Here individual records have to be selected to import in the column.
Field 7 is the rollup and contains the aggregation formula.
I’m not sure how to tie all this together and automate the data as much as possible. Hopefully I’m not too far off track. I also would like to report this data on the maintenance log if possible.
Thanks for the help!
Here’s how I’d configure your base to handle by-year summations. (Actually, I’ll toss a small monkey wrench into the works a little later, but for now, let’s stick to the goal of being able to calculate year-to-date expenditures.)
I’ll start with the assumption you want to sum all expenditures rather than keep a running total of each class of expenditure. Should you later decide that is what you want, you’d simply set up a ‘this year’s expense’ field for each class, following these same directions, instead of a single field collecting all classes.
It looks as if your expenditure records already have a date field called, not too surprisingly, {Date}. My suggested formula — IF(Year={ThisYear},{ExpenditureAmount}) — was actually pseudocode. What you want to do is compare the Year of each record with the current year, duplicating the expenditure amount on those records that match.
To do so, create a formula field called {Current-Year Expenditures}. Configure the formula as follows
IF(
IS_SAME(
TODAY(),
{Date},
'year'
),
{Cost}
)
You’ll want to perform the rollup from the [Vehicle] table — assuming you want expenditures per vehicle. Create a rollup field that follows the {Vehicle} linked record to rollup all instances of {Current-Year Expenditures} with an aggregation function of SUM(values).
Now, there’s one major problem with this arrangement: It works fine through December 31 — but on January 1, when you might start thinking of writing the total down for tax purposes or the like, the number evaporates as the base starts a new running total.
So why not keep two totals: one for last year and one for this? That way, you can track how well you’re doing in comparison with how well you did last year — and your year’s worth of data doesn’t disappear faster than your New Year’s Eve hangover. In addition to {Current-Year Expenditures}, create another formula field, {Last-Year Expenditures}, with this formula:
IF(
YEAR(TODAY())-YEAR({Date})=1,
{Cost}
)
From [Vehicle], roll up those values as last year’s total.
Thank you! I have most of the base done and truly appreciate the help!
Going back to this part of your suggestion:
You’ll want to perform the rollup from the [Vehicle] table — assuming you want expenditures per vehicle. Create a rollup field that follows the {Vehicle} linked record to rollup all instances of {Current-Year Expenditures} with an aggregation function of SUM(values).
I have created the rollup field, but I’m not given the option to select the option for {Current-Year Expenditures}. I’m setting this up in the Maintenance Log Table.
Any ideas or help pointing me in the right direction. Thanks in advance!
The rollup needs to be from the table that links to all of the maintenance log records – in this case, [Cars]. (I mistakenly called it [Vehicles].) Roll it up in the [Cars] table and then use another lookup or rollup to reference it from a different table, if necessary.
So, from [Cars] follow the link to [Maintenance Log] and roll up {Current-Year Expenditures} with an aggregation function of SUM(values). Do the same for {Last-Year Expenditures}.
