Desk reservation form

mobukuro

Hello,

How do I create a form that will allow a user to reserve a desk for a particular day and remove that desk from a list of available desks?

Within the same form, I will also need to allow for recurring dates? For example: If an employee comes to the office every Tuesday and Thursday, they could reserve that desk for those days weekly from now till the rest of the year.

Thanks

Jeff Reisman

@mobukuro

I built something similar to this, for assigning one of six IDs to temporary workers. You'll need some helper columns.

I set up the sheet with the temp IDs at the top. This is where their status of available or taken is shown. The TEMP 1, TEMP 2, etc would be your desk locations.

Main columns were:

Temp ID, Status, Temp Employee Name, Start Date, End Date.

Helper columns:

Temp ID Available, Total, Blank End Date, Known End Date, Name in Use

On rows below, users would select a Temp ID that was listed as available at the top. They'd enter their name, and a start date, and if known, an end date.

Helper formulas:

Temp ID Available: =COUNTIFS([Temp ID]:[Temp ID], [Temp ID]@row, Total:Total, >0)

Blank End Date: =COUNTIF([End Date]@row, "")

Known End Date: =IF([End Date]@row > TODAY(-1), 1, 0)

Total: =IF(SUM([Blank End Date]@row + [Known End Date]@row) > 0, 1, 0)

Name In Use: =IF(Total@row > 0, [Temp Employee Name]@row, "")

Now, in the Status column for the rows at the top (where you list the desk locations):

=IF([Temp ID Available]@row > 0, "Temp ID In Use", "Available")

What this is doing is checking to see if any rows with that Temp ID have a blank end date or a known end date that is greater than or equal to today, and if so, it marks that Temp ID as In Use. I used conditional formatting to mark those rows red.

Jeff Reisman

@mobukuro Unfortunately not. However, there is a workaround. You can create a Dashboard/Portal, with an embedded report showing the desk statuses so they can see what's available, and an embedded form for making their reservation. I added an extra helper column on my sheet, a checkbox called "Report", and only checked it for the Temp ID rows. I created a Report that filtered by the 'Report' box being checked. You could probably find a way to display the current reserved user's start and end date for each desk.

Instead of launching the form to enter a new desk reservation, they launch the "Desk Reservation Dashboard" to see what's available and make their reservation.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/335607#Comment_335607

You don't have to manually share the dashboard access, just the "published" link.

On the dashboard, under the File menu, select "Publish". Click the slider to turn on the Read Only - Full version of the dashboard.

Select either of the top two Access Control options. You can set it to allow anyone with the link to view the dashboard, or only users in your org. The publish URL is shown below in the same popup box:

This works because when your Form is open to anyone in your company, that access passes through on the dashboard. The user can enter new records into the form without a problem. (I just tested this with my unlicensed personal account that's outside my org. No permissions at all on the underlying sheet or the dashboard, the form is open to anyone, and I could enter a new record using the form embedded in the Read Only - Full published dashboard.)

Jeff Reisman

@mobukuro

I built something similar to this, for assigning one of six IDs to temporary workers. You'll need some helper columns.

I set up the sheet with the temp IDs at the top. This is where their status of available or taken is shown. The TEMP 1, TEMP 2, etc would be your desk locations.

Main columns were:

Temp ID, Status, Temp Employee Name, Start Date, End Date.

Helper columns:

Temp ID Available, Total, Blank End Date, Known End Date, Name in Use

On rows below, users would select a Temp ID that was listed as available at the top. They'd enter their name, and a start date, and if known, an end date.

Helper formulas:

Temp ID Available: =COUNTIFS([Temp ID]:[Temp ID], [Temp ID]@row, Total:Total, >0)

Blank End Date: =COUNTIF([End Date]@row, "")

Known End Date: =IF([End Date]@row > TODAY(-1), 1, 0)

Total: =IF(SUM([Blank End Date]@row + [Known End Date]@row) > 0, 1, 0)

Name In Use: =IF(Total@row > 0, [Temp Employee Name]@row, "")

Now, in the Status column for the rows at the top (where you list the desk locations):

=IF([Temp ID Available]@row > 0, "Temp ID In Use", "Available")

What this is doing is checking to see if any rows with that Temp ID have a blank end date or a known end date that is greater than or equal to today, and if so, it marks that Temp ID as In Use. I used conditional formatting to mark those rows red.

mobukuro

Hello,

Thanks for the above and it works; however, is there a way to show the status on the form?

For example: The selection of the unavailable desk for a particular date only would be greyed out or unavailable if selected.

Thank.

Jeff Reisman

@mobukuro Unfortunately not. However, there is a workaround. You can create a Dashboard/Portal, with an embedded report showing the desk statuses so they can see what's available, and an embedded form for making their reservation. I added an extra helper column on my sheet, a checkbox called "Report", and only checked it for the Temp ID rows. I created a Report that filtered by the 'Report' box being checked. You could probably find a way to display the current reserved user's start and end date for each desk.

Instead of launching the form to enter a new desk reservation, they launch the "Desk Reservation Dashboard" to see what's available and make their reservation.

mobukuro

Hello,

I've tried that workaround but then I have to grant access to over 100+ staff members to access the dashboard vs to where anyone in the company could access the form.

Thanks for the help.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/335607#Comment_335607

You don't have to manually share the dashboard access, just the "published" link.

On the dashboard, under the File menu, select "Publish". Click the slider to turn on the Read Only - Full version of the dashboard.

Select either of the top two Access Control options. You can set it to allow anyone with the link to view the dashboard, or only users in your org. The publish URL is shown below in the same popup box:

This works because when your Form is open to anyone in your company, that access passes through on the dashboard. The user can enter new records into the form without a problem. (I just tested this with my unlicensed personal account that's outside my org. No permissions at all on the underlying sheet or the dashboard, the form is open to anyone, and I could enter a new record using the form embedded in the Read Only - Full published dashboard.)

mobukuro

This is awesome! Thanks!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/335615#Comment_335615

Happy to help!