I love these little wins that truly highlight the power of Office 365!
Here’s my use case: in the Boston office of Slalom, we have an annual Learning Day that everyone is required to attend. It’s a fun day, and we do learn some great things from it. Every consultant and leader has to register and can select their preferred sessions for each time block. This can be difficult using disparate systems like a survey tool, Excel, emails, etc. Since I was on the committee this year, I decided to leverage the tools I had readily available to us: Office 365.
Communications is Key, using Modern Communications Site in SharePoint
Right off the bat, I went in and created a new communications site. I hit up unsplash.com for some awesome free imagery and built out the site. Traditionally, we relied on emails for sharing all of this content. I, personally, am tired of digging through emails to find content, so a SharePoint site I made:
It was simple to create all of the pages to contain the content we needed to share. The Learning Day team provided the content and with some copy/pasting activities and a little creativity, we had a brand new site done in about an hour.
Learning Day Registrations with Microsoft Forms
When our people go to register, we need to collect their name and preferred sessions to attend. To register for Learning Day, I put together a nice survey in Forms, as seen here:
I cut out some sections to save space.
With this survey, our people could simply select their preference by dragging and dropping their preferences in order. We also collected food and dietary restrictions cause we’re just that nice.
We don’t have to ask for their name since Forms already knows who the person is. It’s really hard to match data from what someone types in, like Dave Lozzi, when the data source has David Lozzi. Rob or Robert, James or Jim, and so on. Nightmare… Forms saved the day by automatically collecting their O365 profile name, and then easily pulling their email, manager, etc.
This form has a condition in it, if the person isn’t attending, they’ll jump to the last question to provide an explanation. This day is required for all of us, so not attending needed a really good excuse.
I embedded the survey into a SharePoint page, and we linked everyone to the SharePoint page, not the form directly. This encouraged people to explore the site, and even provide feedback on our survey page! This was a nice unexpected benefit of using SharePoint.
The form was incredibly simple to make. The only downside was the formatting. As you see above, we included the session description in the answer so they could know what the session is. Forms doesn’t give us any actual formatting options like HTML or Markdown in these fields, it’s just plain text.
Getting the Survey Results into SharePoint with Microsoft Flow
Since Forms is so tightly integrated within Office 365, this next piece was easy. We know Forms can write to an Excel file, but I wanted this in a SharePoint list.
Keeping the results in Excel would’ve had the 4 or 5 answers per question (the drag and drop questions) stored in a single cell in Excel. Then we’d have to break down those values to know how to schedule the day. Why add more work to ourselves. Instead, we stored the results in a SharePoint list, one answer per column, which helped quickly clarify the results, and allow for further integration opportunities.
How did we do this? Microsoft Flow of course!
more details on the Flow, specifically ranking questions, can be found here.
This flow fires every time a form is submitted. We break up the multiple choice answers and save them as individual columns in the SharePoint list.
We also send a beautifully formatted email confirming their registration and linking to our website to view their registration details.
Since it’s mandatory, if the person says they’re not attending, then the email confirms and CC’s their manager. Might sound harsh, but anyone not attending has to get it cleared by their manager, so we help facilitate the discussions.
Confirmations of Registration with PowerApps and SharePoint
After the people register, in that confirmation email we sent previously, there’s a link to a page in SharePoint where they can see what they signed up for.
No, that’s not it.
I didn’t like this list view since there were so many columns. I looked a little into using a custom list view but decided it was faster using PowerApps. Also, using PowerApps opens the door for us to leverage this data outside of SharePoint if we decided to have an event day app.
The PowerApp is a single screen app, which grabs the current user’s data from the SharePoint list and displays it in a slightly more friendly manner than the list view. This was incredibly simple to make, I think I spent more time on fonts and colors than actually building it (yes, sad, I know).
Would I do this again?
Absolutely! Given this event is once a year, the level of work above is quite reasonable. I would continue using these components of Office 365 in the same manner. If this was a recurring or frequent event, this might be too much work to do each and every time. Instead, I’d look to build a solution that we can just enter in event details and have the apps and such reflect the latest event instead of a highly custom experience like this one.
I am a fan of Office 365, these wins make it all the more important for companies to maximize the investment they are making in Office 365. Don’t be intimidated by the large list of capabilities, instead use them!
great article, but the picture of Flow is unreadable even if zoommed
David, emails sent. I tried Filter array in my own flow and it turned out to be infinite loop…will try others later.
Thank you for posting, really nice.
I am working on a flow and I am new to it. Not sure if you can offer some help. My source is from Forms, and destination is SharePoint List. The flow should check the email, first name and last name from Forms with existing data in List before actions accordingly. Currently I am using Filter Array, somehow the flow gets first name and last name with time stamp which makes all records unique…trying to solve the issue
Are you using a Forms action to get the Form details? Is your form set up for anonymous access?
Thank you David for your reply. The Forms should be set up for anyone with the link can respond (I will double check the settings later).
The first step is a Forms action but we have a step inserted to get approval from line manager, so it is Get Item from previous step when checking the values with existing data in List.
Let’s try this. Email me a screen shot of your flow and I can take a peek. david.lozzi at Slalom.com
Awesome post David, more details on the flow and the power app would be fantastic.
You got it! Coming soon
Some more detail here http://davidlozzi.com/2018/11/06/microsoft-forms-ranking-questions-to-individual-sharepoint-fields-with-flow/
Wow – looks great David and a great usecase. Would love to see more details on the Flow and PowerApp side of things :-)
Sure thing! I’ll get it together over the next few of days
More details on the Flow here http://davidlozzi.com/2018/11/06/microsoft-forms-ranking-questions-to-individual-sharepoint-fields-with-flow/