Conditional Forecasting and Scheduling

[Caolan]

Don’t you hate it when you can do something on paper easily, but are struggling to make it work digitally!?! Wondering if someone can point me in the right direction.

Outcome:
I want to create a new Service Record (let’s call that record B), based on the mileage of a previous Service Record (let’s call that record A).

Context:
We’re using Airtable for to manage a small fleet of vehicles and trailers. We have tables for vehicles, mileage records, defect reporting, etc. That’s all fine.

We want to record at what mileage vehicle servicing is carried out, and create new servicing records based on previous mileage. From there we use the calendar view to schedule services on vehicles (so we know when trucks will be off the road, etc).

Servicing is slightly complex.

• There are two kinds of services. Let’s call them Service A (or a small service) and Service B (a big service).
• Different vehicles have different service schedules depending on manufacturer guidelines. Eg, one vehicle has a Service A every 7,500km and a Service B every 15,000km. (So that is an A, B, A, B, A, B schedule), another vehicle as a Service A every 7,000km and a Service B every 35,000km (so that’s an A, A, A, A, B schedule). We have a stand-alone table for the schedules linked to individual vehicle records.
• There some tolerances in scheduling (eg, up to 10% overdue is ok), so next service is always calculated as within X kms from previous service.

Example: We want to record that Truck 3 had Service A done at 150,382km. AND THEN spit out a new record that says the next service will be Service B and that will be due by 157,382km. (We then compare the current mileage with the due mileage… aka that service is flagged when the truck is within 1,000km of it’s next service).

Issue:
I’m having trouble thinking through how to get Airtable to recognise which service is next. Eg, if a truck is on an A, A, A, A, B schedule… and we’re up to the 4th A… that the next is a B schedule.

Do you have some ideas or a similar base that I could take some inspiration from?

[Wouter]

First idea: make a table with Autonumber, Service Schedule 1 (ABAB), Service Schedule 2 (AAAAB).
First record is 1,A,A
Then 2,B,A 3,A,A, 4,B,A 5,A,B

Use the numbering in the rest of your base, now you can refer to this table to calculate which service is needed.

[Wouter]

Tried this out and seems to work, its just the basic structure, the rest you should be able to build on top.

Table Schedule with fields Sequence, Sched1, Sched1 Next, Sched2, Sched2 Next
Make 10 records, fill Sequence with 1-10, Sched1:ABAB etc, Sched1 Next: BABA etc. (these are the next services for Sched1), same for Sched2 en Sched2 Next.
In table Trucks I have fields: Name (Truck1, Truck2…), Schedule (1 or 2).

In table Services the fields are: ID (1,2,3…), Truck, Date, Schedule (lookup from Trucks), Schedule sequence (link to Schedules). Next you make 2 helpcolumns, for schedule1 and schedule2, in the first you lookup wether for schedule1 the related sequence sais A or B. Same for schedule2. Then with an IF function on the field Schedule you pick the right one, so it sais that THIS service is A or B.
You do the same (so 2 helpcolumns and an IF column) for the next service.

I think this can work. In a field in Trucks you will have to manually put in after how many miles the next service is due. This is because in the table Schedules, the schedules are columns and not records, you cant make a field for that.