Form / Calendar Integrati

sahilhq · 06/27/19

Hi,

I'm working on developing an induction booking process that allows our team to book site induction through Smartsheets. Firstly, I intend to have a Smartsheet induction register that will include columns such as induction date, who the inductor is, requester, role, company, email address, etc. Any one who would like to book a site induction will be first required to fill in a Smartsheet induction request form and the content of the form will be quite similar to the induction register. Once the form is filled in, all the information gathered from the form will automatically be populated in the site induction register. Finally, I will integrate the schedule induction register sheet with Smartsheet calendar so that calendar can display important information from the sheet such as the no. of available spaces, venue, etc.

I have a couple of questions in order to make the steps described above to work:

1) Is it possible for the requester to be able to see the date that are available when they are the filling out the form? For example, dates that are not available are greyed out and are un-selectable and dates that are available are selectable and enter into the form when clicked. From another Smartsheet community discussion, I understand that a possible workaround would be to publish the calendar and the link to the form so that they could open the calendar and then the form. However, it would be amazing if we could let the requester see the available/unavailable dates without looking at the calendar. Is this possible?

2) How do I show in the calendar the available spaces for site induction? For e.g. the maximum no. of people that could have a site induction in one day is 25 and the total number of requesters we have received for a particular day is 22. How do I show in my calendar that there are three available spaces only?

3) Is it possible to have a different calendar Smartsheet rather than simply changing the view from grid to calendar? I would like a different calendar sheet that somehow manages to extract the key data from the Smartsheet induction register (no. of available slots, venue, etc.).

Andrée Starå · 06/28/19

Hi Sahil,

Answers to your questions.

Unfortunately, no.
You could use a filter that would only show those that are available.
Yes, probably. Can you describe what you need in more detail?

Hope that helps!

Have a fantastic Weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome · 06/28/19

I have a few ideas...

1. Exactly as you specify? No. But publishing both tho a dashboard would give you the ability to display them side by side as opposed to two separate windows.

2. A filter or conditional formatting to gray out the unavailable times/dates.

3. It is possible, but details will determine exactly how to set it up.

.

One of the ideas I am kind of thinking on is this:

Create a sheet. If there are 25 openings on a day, each day could have 25 rows.

You can lock the columns that you don't want the users to edit, switch it over to Calendar View, and then have people just double click on the appropriate day/time slot that they want to book and edit the row directly (except for the locked columns of course).

From there you can use conditional formatting to gray out the bar on the calendar and use an Automation to lock that row to keep people from being able to "steal" the spot once it's been booked.

.

Another way of doing something very similar to this would be to display the sheet as a calendar as "View Only" on a dashboard and have a form with just three fields on it (Email, Date, and Time Slot) that goes to a different sheet. You can use a checkbox column on the calendar sheet along with cross sheet references to check a row when someone requests it.

Then you can have an update request sent to the email address triggered from the row becoming checked in the Calendar sheet.

The update request will allow you to really drill down on which columns can be seen and edited.

The requester will get an email, fill out the Update Request, and submit.

Then you can use the conditional formatting and Automation to gray out and lock the row.

If someone submits a request for a date and time slot that has been grayed out and locked, they will not receive the update request.

You can include some basic instructions using a rich text widget on the dashboard letting them know to only request time slots that are available (not gray).

Another though just struck me on this as well... A helper column on the form sheet that flags any duplicate entries.

Set up an Automation to send an Update Request anytime that flag appears to the email address associated with it prompting them to select an available time slot.

sahilhq · 07/01/19

Hi,

Thanks for your suggestions. I quite like your idea of creating a dashboard that contains both the calendar and Smartsheet form. This way we just have to share one link with the requester rather than having two seperate links (one link for the form and one for the calendar). However, the Dashboard idea might not work since the Smartsheet form may be filled by non-smartsheet users so they will not be able to access the dashboard.

I might have not explained clearly what I am trying to achieve with the induction request process. I think if I explained the process chronolologically, it might be more clearer to you.

1) Create Induction Register. This register is automatically getting fed by the Induction Request Smartsheet FormThis register has been created. See attached image to understand layout of register.

2) Create Induction Request Form. This form is filled up by the requester requesting for an induction. This form has also been created. See attached image to understand layout of form.

3) Create an induction calendar report. This calendar report has NOT been created yet. This is where I would like more assistance. I would like the calendar report to display the number of available spaces. If there are 25 openings in a day and 23 have been booked then I would like the calendar to display that there are 2 spaces available only. Do I set up an additional column that basically checks the no. of booking requests per day? What will the formula look like for this?

In terms of what I would like the different parties to see and do:

- Those people booking for an induction only need to view the calendar to see available slots and be able to fill the induction request form. I don't want to provide them access to the induction register.

- Me and my team need to have access to all three - calendar, form and register.

KX Induction Register].PNG

KX Induction Request Form.PNG

Paul Newcome · 07/01/19

Ok. Well to populate a calendar with the available slots remaining, we can use a pretty basic sheet. They key is going to be the Primary Column. I'll rename it "Available Slots" for this example. I also have the following columns on this sheet:

"Date": Date type column with each possible day in it going down the rows.

"Total Slots": Text/Number type column where you enter the total number of slots for that day.

“槽”:我们使用交叉表引用COUNTIFS formula to count how many times that particular day appears in the Date column of the Register.

=COUNTIFS({Register Dates},[email protected])

.

Then in the "Available Slots" column (needs to be the primary column), we can use this...

=[Total Slots]@row- [Slots Used]@row+ " slots remaining"

.

This will automatically calculate the number of remaining slots for each day, and display that with the ending text.

See screenshots for visual example.

Note: I added in some conditional formatting to the Task Bar just as a visual reference for how many slots are available per date when looking at the calendar view...

sahilhq · 07/01/19

That's amazing! Now my calendar report is also set up, thanks to you!!

Now that I have both the site induction request Smartsheet Form and site induction calendar report set up, I am ready to share them with my project team. Do you know the best way to present both? Ideally, I would like to share one link where those that would like to book can first view the calendar and then fill the form based upon the no. of available spaces they see in the calendar. Do you think doing a dashboard is the best option? If so, how do non-smartsheets users view the dashboard?

Paul Newcome · 07/01/19

You can publish the dashboard and send out that published link. With the form link on the dashboard in a Web URL widget, they will be able to view that alongside the calendar (how you put the calendar on the dashboard is up to you).

Send out the published link to the dashboard itself, and you should be squared away.

Paul Newcome · 07/01/19

Note to above comment:

Even though the dashboard is published, users will still be able to interact with the form.

sahilhq · 08/12/19

Hi Paul,

See below snapshot 1 of Smartsheet calendar and form that I created from your suggestions. I would like to make my calendar more 'smarter'. Currently the calendar is only able to display the number of slots available. Is there any way to get my calendar to show more information like who the inductor is, venue, etc. per day without actually double clicking on any of the dates in the calendar? At the moment, only once I double click on this do I get to see all of the information. If you open Snapshot 2 you'll understand better what I mean.

Further details.PNG

Calendar and Form.PNG

Paul Newcome · 08/12/19

The calendar view will only display what is in the Primary Column of the sheet. To have more information displayed, you would need to combine the data from the appropriate cells into a string and have that string residing in the Primary Column.

sahilhq · 08/13/19

Thanks for the response. The primarily column does have more information displayed than just the number of available slots (see below screenshot as an example). Anyway we can expand each date in the calendar to display all the information? Also, what do you mean by bringing the cells into a string?

Andrée Starå · 08/13/19

It's possible to show more information but not everything.

In your example, above you could probably show what's before Hornsey. You would have to show it without the form on the side because it's changing the size of the sheet and the information in the calendar.

字符串=连接在一起

Hope that helps!

Travis Spears · 03/26/21

Can you create a Appointment Schedule with Time Slots?

Annie Prescott · 08/11/21

Ok. Well to populate a calendar with the available slots remaining, we can use a pretty basic sheet. They key is going to be the Primary Column. I'll rename it \"Available Slots\" for this example. I also have the following columns on this sheet:<\/p>\n

<\/p>\n

\"Date\": Date type column with each possible day in it going down the rows.<\/p>\n

\"Total Slots\": Text\/Number type column where you enter the total number of slots for that day.<\/p>\n

\"Slots Used\": We use a cross sheet reference in a COUNTIFS formula to count how many times that particular day appears in the Date column of the Register.<\/p>\n

<\/p>\n

=COUNTIFS({Register Dates}, Date@row)<\/p>\n

.<\/p>\n

Then in the \"Available Slots\" column (needs to be the primary column), we can use this...<\/p>\n

<\/p>\n

=[Total Slots]@row<\/a> - [Slots Used]@row<\/a> + \" slots remaining\"<\/p>\n

.<\/p>\n

This will automatically calculate the number of remaining slots for each day, and display that with the ending text.<\/p>\n

<\/p>\n

See screenshots for visual example.<\/p>\n

<\/p>\n

Note: I added in some conditional formatting to the Task Bar just as a visual reference for how many slots are available per date when looking at the calendar view...<\/p>\n

$\"Comm.PNG\"$ <\/img><\/p>

$\"Comm1.PNG\"$ <\/img><\/p>","bodyRaw":"

Ok. Well to populate a calendar with the available slots remaining, we can use a pretty basic sheet. They key is going to be the Primary Column. I'll rename it \"Available Slots\" for this example. I also have the following columns on this sheet:<\/p>\n\n

<\/p>\n\n

\"Date\": Date type column with each possible day in it going down the rows.<\/p>\n\n

\"Total Slots\": Text\/Number type column where you enter the total number of slots for that day.<\/p>\n\n

\"Slots Used\": We use a cross sheet reference in a COUNTIFS formula to count how many times that particular day appears in the Date column of the Register.<\/p>\n\n

<\/p>\n\n

=COUNTIFS({Register Dates}, Date@row)<\/p>\n\n

.<\/p>\n\n

Then in the \"Available Slots\" column (needs to be the primary column), we can use this...<\/p>\n\n

<\/p>\n\n

=[Total Slots]@row - [Slots Used]@row + \" slots remaining\"<\/p>\n\n

.<\/p>\n\n

This will automatically calculate the number of remaining slots for each day, and display that with the ending text.<\/p>\n\n

<\/p>\n\n

See screenshots for visual example.<\/p>\n\n

<\/p>\n\n

Note: I added in some conditional formatting to the Task Bar just as a visual reference for how many slots are available per date when looking at the calendar view...<\/p>\n

$\"Comm.PNG\"$ <\/p>

$\"Comm1.PNG\"$ <\/p>","format":"html","dateInserted":"2019-07-01T09:00:53+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-27T11:59:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/183091#Comment_183091","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/183091#Comment_183091

Just wanted to say thank you for creating content that is super user friendly. I've read many of your responses on different topics for my smartsheet builds and have been able to apply your workarounds to meet my business needs.

Paul Newcome · 08/12/21

@Travis SpearsAre you able to provide more detail?

@Annie PrescottI'm glad my explanations are easy to follow. I try to explain the "why" and "how" so that they can be adapted to fit other applications as well as to try to teach so that others can become more proficient.

Ryan_N_KC · 09/14/21

@Paul Newcome我一直遵循你的线程从7/1/19如何to build a basic scheduling system based on a form request entry and showing available slots remaining. I've built my sheet to mimic what you had shown, but am a bit lost on why I can't get my "slots used" column to work. Part of my thought is I believe I'm missing something entirely, like a form request or something to import data in. I'm wanting to create a scheduling system for our shipping department. Form would be used by customer to schedule a pick up time, each day of the work week would have a number of dock appointments. I'm trying to populated the requests from the form into the sheet, which then on the form when an appointment has been reserved it reduces the availability for any day/time slot. I'd like to build my form where it will ask the following:

1) customer

2) customer PO #

3) Carrier

4) Pick up request date----once you choose date, it will pull up available time slots remaining

Is this possible somehow? I'm fairly new to Smartsheet myself...

Form / Calendar Integrati

Comments

Categories