Staff Calendar - Populate Future Events

Lots of ways to go about it potentially and you'll get more ideas from others here, I personally like to choose a slightly harder path upfront with how to handle something like this because downstream it opens up other doors yet to be thought of. 

I like to set up a distinct table for something like 'Employee Days'. It would be a table that joins each employee to each day of the year as a unique record. It really only needs two fields - employee and date. You can preload this in your app since time and days can be dragged down in Excel and just upload the next couple of years. 

For the event route - same kind of concept - you could have like an 'Event Request' type form where a person could enter a 'request' for that employee with an event type - and say it applies for Monday and Wednesdays for Employee A, and it'll run from 1/1/24 to 6/30/24 and its a schedule for 9-5. Then another event request lets say for same employee, same Monday/Wednesday from 7/1/24 - 12/31/24 but 7-3.

With Pipelines - you could query for the 'Employee Days' that meet the criteria from the request - and for each Employee Day, create a child Employee Event record that represents their schedule. 

The managers in this case are more or less filling out a form to set a schedule, and if they have different versions like different entries for M/W/F versus T/R, no problem, just two requests. You can extend this for things like PTO, company holidays etc. 

If you wanted something simpler - you could do a similar setup to the Request form with the start/end of the event and the employee to target, and do a loop to import all the records by adding each day between the two dates and adding a new event record for that employee for each day that matched. I personally prefer the Employee Day setup because it allows for more reporting down the line on things like utilization, identifying gaps, tracking duplicates or overages etc. The setup is harder upfront but I've seen it provide a lot better functionality for reporting that you might think of down the road. 

Hey Aidon,

I've setup something like this for our construction company where different Team members have different recurring Schedules for us to book Events. Here's a simplified version of the architecture we've implemented that's held up well thus far!

People
    Contact info fields
    QB User field too (not all People have a QB User Account in our app)
    People have many Calendars, but in practice only one to help avoid double bookings
Calendars
    Related Person: Each Calendar belongs to one Person
    Related Schedule: Each Calendar belongs to one Schedule (or empty for non-recurring)
    Fields to customize name and display color on reports
    Calendars have many Events
Events
    Related Calendar: Each Event belongs to one Calendar
    Fields to track the start date/time and stop date/time
    Related Project: Each Event belongs to one Project (when booked)
Schedules
    Name describing the schedule for the Schedule record picker on the Calendar form
    (e.g. Weekdays)

    Schedules have many Schedule Items
Schedule Items
    Fields to track a day name, start time and stop time
    Formula fields dynamically build future dates
    Related Schedule: Each Schedule Item belongs to one Schedule

Then, we have a Pipeline set to run each each Sunday that creates the Calendar Event records. Basically, it's a nested loop where we start by searching for Calendars that are "subscribed" to Schedule (where the Related Schedule is set) which returns a found set of records where we "Should Create Availability." Next, we search through Schedule Items records based on the Related Schedule IDs found in the previous step and then create a Calendar Event for each found Schedule Item and relate them to the respective Calendar.

Worth noting, Calendar Events each contain unique composite key by gluing together the Related Calendar ID and the Calendar Event time (via Formula Field) to prevent double bookings, which the Pipeline honors by erroring out and skipping the Calendar Event creation, but it continues to run subsequent actions/steps. In other words, if someone manually created Calendar Event before they Pipeline runs, the Pipeline knows to skip creating that specific Calendar Event to help avoid potential double bookings.

There's a bit more flexibility where the Schedule also has a drop down where we can specify the "weeks out" the Calendar Events should be created when the Sunday Pipeline runs. For example, some Schedules we want Calendar Events to be created 3 weeks out, so when this next Sunday Pipeline runs on 11/18, it'll create Calendar Events Monday – Friday (12/11 – 12/15).

While Calendar Events may be manually created, we also have built a series of Formula URL buttons that will create Calendar Events at specific times (e.g. Friday 8am – 12pm). These are placed on the Calendar Form on an "Availability" tab to allow Users to quickly generate Events and we again leverage a dropdown to shift the weeks out.

On the Calendar Form, we have a couple embedded reports (via the Report Link field) to display the Calendar Events using the Calendar style report and followed by an itemized list of Calendar Events. The latter Calendar style report has drag and drop functionality to change the Calendar Event dates and times (similar to Google Calendar.)

If Quickbase offered "Dynamic Filters" on Calendar reports this system would be significantly better! I've been waiting patiently for this feature for years! If that's of value to you, you can up vote for this request "Add Dynamic Filters to Calendar Reports" (ID #123498).

I know that's quite a bit to digest, if that is unclear or you have any questions, perhaps I can share a demo app on the Quickbase Exchange where it may make more sense to see in action.