Team Calendar

stuartr

Hello. I wanted to ask some advice on how best to collect information from team members to understand where they will be working for the following week (selectable from wfh, planned leave, offsite, office, etc).

The goal is to create a shared team calendar where each member of the team could see where everyone else was on any given day the following week. The entry in the calender would simply be a string - HR persons name followed by where they will be (eg Monday 1st might contain John Smith - wfh, Jane Doe - Office etc).

I dont know the best way to structure/capture the date info. I was thinking of using a simple form (I dont think UR would work as we would want a historical record of where the team was and a UR would overwrite this each week). but I would need to capture their whereabouts for 5 days of the week, not just 1. And the calendar view only allows you to select one field to display dates so I cant create 5 dates fields named monday date tuesday date etc as this wont display in the calendar view.

I suppose I could prepopulate the base sheet with their names and dates complete for them to select whereabouts for 5 lines but thats not really elegant and Im sure there must be something better. We dont have the calendar app but would consider if this was something that it could support?

Any help/suggestion appreciated. Thankyou

Julie Fortney

I'm a Calendar App fan myself, but like the regular Calendar View on a sheet it only supports one start and one end date per row.

Do you by chance have Bridge? If so, you could send the start and end dates entered in multiple columns on an entry sheet to your master calendar sheet as individual rows.

Otherwise, I'd try to make your form entry process as smooth as possible. A few tips that might help:

1. Change your form settings to reload the same form for another entry so your user can quickly submit their entries for the week:
2. Use query strings to create a unique url for each user to prepopulate their name in the form. Here's a help article: https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values
3. Create an automation to send the form link to each user weekly to remind them to submit their entries.
4. Create a report filtered by Current User so they can quickly glance at its Calendar View to see if they missed any entries for that week. (e.g. If someone submits a few entries for the week, then glances at the Calendar View, they could easily spot if they accidentally forgot to submit one for Tuesday.)

stuartr

Thanks Julie.

For 1) I was already using this approach, but only for logging next weeks time (i.e. all 5 days M-F on the one form along with the week commencing date), then reloading after submission to allow for the 2nd/following weeks time.

I think your idea is to use a simple form and submit it 5 times in a row (reloading the same form another 4 times) in order to create 5 separate lines in the base sheet (did I understand correctly).

I think this approach will also allow the entries to be viewed in the shared calendar wouldnt it (but they would have to then select a different start/from date on each days entry (M-F) which might lead to errors

2) This looks great, Ill give it a go as it will save time :-) - do you think I could use something similar to add the correct default date in the from date on the form (it would need to increment date + 1 each day, which means the only fields that the team would then need to select would be their whereabouts (eg office, wfh, on leave)

3) Yes I would do that (I have also created recurring outlook calendar event containing a link to the form to remind people) - I also use bitly to rename the form link so its clear from the URL what the URL is.

4) Yes - good idea.

Thanks very much for your response. I already have another approach based on reports but I want to use your suggestion as a seperate option and see what the team thinks.

Julie Fortney

@stuartr

1) correct!

2) While you can use query strings to pre-populate a date, the form url contains a static date, so you can't reload the same form with a dynamically-updating date.

stuartr

Hi again. So I tried adding a default value via the URL and got it to succesfully return detail into the sheet. I did have a couple more questions in the hope that you have used this yourself.

a) to avoid spaces in URL I added 20% between first and last name John20%Smith as the help article instructed. Unfortunately that is then the value that defaults into the sheet. Its not a show stopper and I can run a formula to strip it out but is their a neater way to return John Smith into the sheet?

b) I also though that I could achieve the same as the above by adding the contact name field onto the form, setting a default value of eg John Smith, then hiding the field. This URL is then the one I share with John and I create others in the same way for the rest of the team. Doesnt this acieve the same as a) above?

c) On submission of form, if I chose to use reload the same form again, it loads the original form URL without my ?John20%Smith in the link. So it only prepopulates the name in the first submission not the rest. It also opens a new tab for each of the days of the week, which looks a little messy. However if you use the send to a URL option, you can paste the modified URL containing John20%Smith and it prepopulates all susbequent submissions (as well as teh original submission). In addition this option all happens in one tab, so is a lot cleaner experience.

Julie Fortney

a) So close! It's "%20"

b) It does. The benefit of using query strings instead is that you have just one form to manage. If you ever want to change the form, you have to open up multiple forms to make the same change.

c) Ah, you're correct, subsequent submissions lose the query strings. The send to URL option is helpful, except since you're using just one form, I think it would load the John Smith version for all users, regardless of the url of their original entry.

If it was me, unless you have a ton of users, I would just maintain multiple versions of the same form so you can plug in the default values.

stuartr

a) doh. Thanks

b) makes sense

c) I would actually customise a bunch of URLs unique to each user and then use this in the on submission of this form option - I guess this works fine until they cant find the link and ask to borrow someone elses URL, or I have to create a new starter who joins the team

I have under 20 users for this so will probably go with default value in the form rather than change the URLs. But it was good to learn that new skill. Thanks so much for your advice. So😀 helpful.

Julie Fortney

@stuartr You bet - these are the best kinds of Community conversations, where we throw around ideas and learn from each other!

stuartr

hi again. fyi this progressed nicely. I used the default name hidden to create custom forms for all 12 of the team, and used bitly to rename them so they know they have the correct link. So they just input the date and their whereabouts. The form then reloads for the rest of the week...

I used a formula someone v helpfully posted to generate "week commencing" and this is now used to group entries in a report. I used conditional fomatting on task bar to make entries easy to see in the calendar view.

I also created a string on the primary to link name and whereabouts together so that it is visible in the calendar view.