Forum Discussion

AidonOlligschla's avatar
AidonOlligschla
Qrew Cadet
7 months ago

Staff Calendar - Populate Future Events

Hello everyone and Happy Friday!

I am trying to get some of our departments to shift away from creating schedules in spreadsheets and into Quickbase.  I've got a framework built up between two related tables so far: Staff Members and Calendar Events.  Where I'm getting stuck is how I can add functionality that would make this more convenient than copying and pasting cells in a spreadsheet.

Specifically, I'd like to create the following:

  • Ability to duplicate days for a staff member for a custom timeframe in the future.  Example: Duplicate this calendar event for Joe Schmoe every Monday-Friday through the end of the year.

This would give my coworkers an easy way to draft up a framework for the year and then adjust accordingly for PTO, Days Off, and other specifics.

Any ideas how I could add this functionality?  By way of pipeline?  URL Formula?



------------------------------
Aidon Olligschlager |
Flight Operations Technical Support
Pentastar Aviation
Waterford MI
aolligschlager@pentastaraviation.com | 248-202-9587
------------------------------

3 Replies

  • 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. 



    ------------------------------
    Chayce Duncan
    ------------------------------
  • 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.



    ------------------------------
    Brian Seymour
    ------------------------------
    • AidonOlligschla's avatar
      AidonOlligschla
      Qrew Cadet

      Brian,

      Would love to see the app in action if possible.  Let me know if this is something we can arrange together.  You can reach me directly at aolligschlager@pentastar.aero.

      Looking forward to speaking with you.

      Best,



      ------------------------------
      Aidon Olligschlager |
      Flight Operations Technical Support
      Pentastar Aviation
      Waterford MI
      aolligschlager@pentastaraviation.com | 248-202-9587
      ------------------------------