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:
- Scheduled for a single day; job is completed same day
- Scheduled for multiple consecutive days; example: Start work Monday, finish Wednesday - 3 day installation
- 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,
Hi Ryan, I’m not sure I entirely follow, but a related table like you mentioned seems like the way to go
Work Orders and
Installation Dates as your tables, where
Customers would be linked to
Work Orders and
Installation Dates would be linked to
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
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
MAX(values) in the
Work Orders table, which would give you
21 Nov and
29 Nov respectively
Does that make sense?
#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:
- manually select the installation type (single day, multiple consecutive, or multiple non-consecutive days)
- 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: