Avoiding Overbooking using Smartsheet Calendar/DataShuttle

Jen Castillo

Hello!

I am trying to create a booking system for a room with multiple stations.

Stations: 1 - 5.

Time slots: Morning, Afternoon, All Day

So far I have a dashboard with a calendar/form and separate sheet for the form submissions.

With the calendar, people are able to see other bookings, but ideally, I want to use DataShuttle to create a dynamic drop down in the form so that no one can choose a workstation and time slot that is already booked- either in the same time slot as them or if it was previously selected as "All Day."

Originally I had helper columns that would check a box if the station/date/time slot were the same. Then I was going to set up an automation to reach back out to the submitter.

But then I realized that it wouldn't work if someone has booked the station All Day so I've been playing with a few different attempts.

If anyone has any other solutions, I am open to any! Like I said before, I would like to use DataShuttle to only populate drop down options for the Stations/Time Slots based on the booking date and availability.

Is this possible with Smartsheet?

Thank you!

James Keuning

The problem you are going to encounter is how you will sync the Booking Date and Time Slot fields on your form. To accomplish what you want to accomplish, your dropdown field with need to have Station, Date, and Time Slot in one field. It can be done, but the other problem you will have is that shuttles only run every 15 minutes, so depending on how eager our people are, you might be doing a lot of reach backs. And the problem with the reachback is that your user will not be able to edit their original entry, so those conflicts will need to be manually fixed by you.

If I was solving this problem, I would use a report. Use a series of formulas to update one field - AVAILABLE. If the space and time slot are available, the room will show on the report. On other words, picking AM or picking PM will each or both make an All Day appointment UNAVAILABLE. And likewise, picking All Day will make AM and PM both unavailable. As soon as the user enters their selection and updates the report, the selections will be reflected in the report if another user opens the report. You report is filtered to show records where AVAILABLE = TRUE.

Jen Castillo

Hi @James Keuning ! Thanks for sharing your thoughts! Your solution sounds great and I'd love to try but I'm a little lost still.

It's a good number of people that will book through this, but we don't expect high traffic usage. (Maybe a few entries a day, max.) The secretary we're making this for has no issue making manual adjustments if need be, we just want to take as much of the initial work off her as possible.

What formulas would you suggest? I'm not sure how I would set up a Station/Date/Time Slot drop down field without having to manually create a list of available dates? Apologies if I've misunderstood.