Maintenance Log/Schedule Base

[Sdeck]

Hello, I am trying to build a base for our vehicles and equipment fleet. This base will hold the maintenance log and the maintenance service schedule for each vehicle. I am stuck with how to move forward in the set up process. I initially started with three tables, Vehicles/Equipment, Maintenance Log, and Maintenance Schedule. I want to be able to plug in formulas that will calculate the next service dates for various types of services (oil changes, fluid checks, tire rotation, etc.) but because we are working with all different types of equipment (heavy duty, UTVs, cars, tractors) I am having a hard time finding a way to systematically implement the same formulas for each piece of equipment. I want this to be easy to maintain for the user and not too complicated, but I am having a hard time figuring out how to link dates versus mileage. I hope this makes sense, but I would love some advice for how to even set up each table and what my primary fields should be for each, I've included snap shots of each table so far. I've added and deleted about four different formulas so far, that have worked but aren't exactly what I want. Any advice welcome!

[TheTimeSavingCo]

If you could provide a specific example of a calculation you'd like done I can see what I can do to help!  Your tables look good too.  I take it "Maintenance Schedule" displays the "program" of maintenance for each vehicle?  So once Kubota M7060-D accumulates 500 miles it'll need to have an oil change?

[Sdeck]

Yes, exactly. But each piece of equipment has approximately 8 different services that would be on different maintenance schedules. I would love to be able to have each record (piece of equipment) hold the entire maintenance log history and schedule through linked records. I want someone to be able to enter the mileage in the maintenance log table and have it link to the maintenance schedule to automatically update when the next oil change is needed based on the current mileage logged. Is that possible?

[TheTimeSavingCo]

Hm, yeah that's definitely possible.  I think I'd handle it at the creation of the Maintenance Log record.  e.g. when we're creating a "Maintenance Log" record for the Kubota M7060-D, we'll note the mileage at which there needs to be an oil change.

So if we're currently doing an oil change and it's at 300 miles, we note that the next mileage count to do an oil change at is 800 miles, and we'd stamp that into a field in the "Maintenance Schedule" table

We could then use a lookup field to pull the current mileage of that vehicle into the "Maintenance Schedule" and use a formula field to determine whether the current mileage is > 800, in which case we'll send an alert for there to be an oil change to happen. 

Assuming the mileage when that occurs is 850 or something, when we create a new "Maintenance Log" record for an oil change, we'll stamp "1350" into the Maintenance Schedule, does that make sense?