Scheduling job installations using Calendar in airtable

[Ryan_McCluskey1]

Hi Airtable friends,

I’m trying to design a calendar & job scheduling functionality into our existing airtable base. Our small company currently uses google calendar to schedule customer installations. I’m not clear on how to set up the functionality we need and hope maybe someone here can steer me in the right direction. Or show me a base that does what I’m looking to do.

We have a base with contracts that include a variety of related information, with the unique customer contact information in a related table: Customers and Work Orders

When we schedule a work order for installation, there are 3 possibilities:

1. Scheduled for a single day; job is completed same day
2. Scheduled for multiple consecutive days; example: Start work Monday, finish Wednesday - 3 day installation
3. Scheduled for multiple non-consecutive days: Start work Monday, work Tuesday, return to finish following Wednesday.

#3 is the scenario where I’m stumped. Each work order is a single record, so a Start Date and an End Date does not account for scenario 3 or variations of multiple disparate days.

I have considered maybe a related table that creates a record for each installation date, but I’m unclear on how that would work on the calendar side.

Any suggestions or directions would be incredibly helpful! Thanks in advance,

[TheTimeSavingCo]

Hi Ryan, I’m not sure I entirely follow, but a related table like you mentioned seems like the way to go

You’d have Customers, Work Orders and Installation Dates as your tables, where Customers would be linked to Work Orders and Installation Dates would be linked to Work Orders

From there, you could have multiple Installation Dates records linked to a single Work Orders table, and use rollup fields to get the overall start and end date of each Work Order

For example, let’s say Work Order 1 has two installation dates, 21-22 Nov, followed by 28-29 Nov

This would be represented by two Installation Date records with said Start Date and End Date values linked to Work Order 1

You’d then have rollup fields with the formulas MIN(values) and MAX(values) in the Work Orders table, which would give you 21 Nov and 29 Nov respectively

Does that make sense?

[ladracy]

Hey Ryan,

#3 is going to be tricky to automate because there is no standard logic to determine the next iteration of days however you can still automate and streamline your workflow by creating a ‘Days’ table.

Your ‘Days’ table will be a reference table that will link to your 'Customer’s table.

The user experience will start on the ‘Customers’ table where you:

1. manually select the installation type (single day, multiple consecutive, or multiple non-consecutive days)
2. manually select which days

This will trigger an automation to create individual records for each work order on your ‘Work Orders’ table. From there you can create calendar, timeline, and week of view for your work orders.

See gif below: