Staff Calendar - Populate Future Events

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.