Help

Re: Car Maintenance database

4186 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jim_Bean
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Screen Shot 2018-07-13 at 8.51.04 AM.png

Hopefully someone can point me in the right direction. Thanks in advance.

28 Replies 28

What Tables do you have? Seems like you need the latest record: Find last entry

Would only need to pull data from this cell that will be frequently updated.

Screen Shot 2018-07-13 at 8.51.32 AM.png

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:

  • Vehicles (car and motorcicle)
  • Workshops
  • Tasks (as your Car Log, I think), with maintenance and repairs

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. :winking_face: ) 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:

  1. You appear to have the proper tables defined: your main table ([Car Log]) and a table to hold your current mileage ([Car Mileage]).

  2. Your [Car Mileage] table should have three fields defined:

    1. The primary field, by default called {Name}.
    2. A number field called {Mileage}. Set it to your current mileage (86,500 for now).
    3. The complementary link-record field from [Car Mileage] back to [Car Log], created automatically. 'Link to multiple records' should be enabled.
  3. 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).

  4. Each record in [Car Log] must be linked to the single row in [Car Mileage] — much as you have now.

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

  6. 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 '.'.)

It’s a work in progress :grinning_face_with_smiling_eyes:

Captura de pantalla 2018-07-13_11-13-04_p. m..png

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:

460eab589b94fb2eeffcbdaba3f2517707e2ad30.png

Jim_Bean
5 - Automation Enthusiast
5 - Automation Enthusiast

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.
Screen Shot 2018-07-13 at 6.34.27 PM.png

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?

Screen Shot 2018-07-13 at 6.36.48 PM.png

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.

Jim_Bean
5 - Automation Enthusiast
5 - Automation Enthusiast

@Elias_Gomez_Sainz

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.

Screen Shot 2018-07-13 at 6.45.54 PM.png

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

Screen Shot 2018-07-13 at 6.54.45 PM.png

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.