Car Maintenance database

[Jim_Bean]

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.

[Elias_Gomez_Sai]

Jim_Bean:

From there my log would be maintained by date and a “tag” used to identify what car had the work done to.

You have a Cars table, so change the Select to a Linked Record field.

[Elias_Gomez_Sai]

Jim_Bean:

How would I split my car information like you have done for car/bike?

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.

[Jim_Bean]

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

[W_Vann_Hall]

Jim_Bean:

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?

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.

[Jim_Bean]

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.

[W_Vann_Hall]

There is — but you probably aren’t going to like it. :winking_face:

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.

[Jim_Bean]

@W_Vann_Hall

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!

[W_Vann_Hall]

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.

[Jim_Bean]

@W_Vann_Hall

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!

[W_Vann_Hall]

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}.