Booking System in Smartsheets Forms

DanUoL
DanUoL
edited 01/18/24 in Smartsheet Basics

Hello everyone,

I'm a relatively new user to Smartsheets and this is my first post.

I have a question please about Smartsheets forms. I work as a videographer and I'm trying to write a form that allows people to hire out equipment (say for the sake of argument we only have one equipment type at the moment at that is 'Kit01').

I would like to know a few things (and if they're even possible):

  • How would i create it so that there are no double bookings? For example, ColleagueA has booked out Kit01 from 18/01/24 to 25/01/24 and ColleagueB (who doesn;t know this) wants to book out Kit01 from 19/01/24 to 23/01/24 - how can I ensure that ColleagueB isn't able to make this booking because of the date clash? Is it possible to get an error message or something?
  • How would I be able to include a maximum hire time (say 7 days per booking) for the equipment? I.e if ColleagueA wanted to book Kit01 from 18/01/24 to 18/02/24 is it possible to reduce this so that the maximum range would be 18/01/24 to 25/01/24? The idea being that if it is necessary to book for longer, then just do multiple smaller bookings rather than one long one.
  • When ColleagueA opens up the Smartsheets form - is it possible that the form prepopulates with ColleagueA's credentials (i.e email address)? This would ensure that ColleagueA doesn't accidentally book out equipment for ColleagueB.

I hope that makes sense and thank you in advance for the support. Cheers! Dan

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭

    Hi @DanUoL,

    Here are the answers:

    • You can do this by having a column that returns "Approved" or "Denied" on the reservation of Kit01 based off it being booked in that date range. I have done this with a pass we have on one of my sheets. You would need to check the date range of the request for any other Approved status date ranges on the kit. If there is no overlap from the request date on your current row it would be approved. Then you set up an automation to notify those who are denied. You could include the link so they could go put in another request for a different date range. (I kept a sheet for my pass that I then presented on a calendar report so people could see when it was not available as well.)
    • The easiest is to tell them not to book it more than whatever time frame, and tell them that anything that violates that time frame would be denied. Most people would then follow the recommendation so as not to be denied. Otherwise you could have a column with a formula populate the max return date if the requested return date is past the approved range.
    • It is not possible to pre-populate forms in Smartsheet.

    I hope this helps. I realize answer 1 was particularly long, so please reply if it is unclear or you would like additional help trying to set it up!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Hi Zachary,

    Thanks so much for getting back to me.

    Ok, I'm not entirely sure how to construct this if i'm honest! I created a column called Approved/Denied that had a conditional IF statement saying that if the duration (i.e end date - start date) was greater than '7' then return "Denied" in the Approved/Denied column.

    But obviously this is only go to Approve or Deny the request based on the input date range, not seeing whether or not there are clashes with dates in other requests. So yeah I think you're right - might be easier just to have a verbal agreement that we can't book kit for longer than 7 days.

    Any help in ideas for the formula would be massively appreciated! Thank you!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @DanUoL

    I hope you're well and safe!

    To add to Zachary's excellent advice/answer.

    I would like to know a few things (and if they're even possible):

    • How would i create it so that there are no double bookings? For example, ColleagueA has booked out Kit01 from 18/01/24 to 25/01/24 and ColleagueB (who doesn;t know this) wants to book out Kit01 from 19/01/24 to 23/01/24 - how can I ensure that ColleagueB isn't able to make this booking because of the date clash? Is it possible to get an error message or something?

    [Andrée Starå] Another way would be to use a Dashboard with the form on one side and a Report showing what's available or booked.

    • How would I be able to include a maximum hire time (say 7 days per booking) for the equipment? I.e if ColleagueA wanted to book Kit01 from 18/01/24 to 18/02/24 is it possible to reduce this so that the maximum range would be 18/01/24 to 25/01/24? The idea being that if it is necessary to book for longer, then just do multiple smaller bookings rather than one long one.

    [Andrée Starå] To add. You could have an Update Request trigger automatically if the duration is longer than what's ok and ask them to update.

    • When ColleagueA opens up the Smartsheets form - is it possible that the form prepopulates with ColleagueA's credentials (i.e email address)? This would ensure that ColleagueA doesn't accidentally book out equipment for ColleagueB.

    [Andrée Starå] You can set the form so they have to be logged in to see it, and then you can catch their email automatically. You could also pre-populate the form using the URL-Query feature, but then you would have to have unique links for the different submitters.

    Make sense?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi Andree,

    Apologies for the delayed reply and a massive thanks for reaching out. Apologies - but I'm not familiar with your recommended solutions. I think I may need to do a bit of Smartsheets training first!

    Thanks again,

    Dan