Reading MS Forms and Gaining ‘Approval’

Using Microsoft Flow for process automation – part 1.

Recently I’ve been using Microsoft power automate tools otherwise known as flow to automate some of the processes that I have to do on a regular basis. I’m hoping that this account may be useful to somebody else who’s thinking of using this technology.

As a small business owner I’m always looking for ways to increase my productivity, and hence increase our value to our customers. The two seem totally linked in my mind. If, as a manager, I spend my whole time doing ‘Admin’ when do I get a chance to plan for the future. Especially at the moment, any manager not thinking hard about the ‘Post Covid era’.. well they should be!

When we first started up as an agency, we simply carried on with much the same business processes as I had developed as a sole trader. Like many offices, we Initially based our ‘system’ around emails. Emails were flying backwards and forwards everywhere and after a couple of years of sustained growth, it became apparent that the most common means of communication wasn’t necessarily the best medium available. The inbox just got more and more full..

The hunt was on to look for another way, and in doing so, reduce the administrative burden. After experimenting with an off the shelf CRM for a year or so, we decided that the only way forward was to build our own system.

if you have Microsoft Office you probably already have free account for power automate, Here’s a decent link to a explanation of how to set the account up and how to access it. Basically if you go to your Office Admin centre it should be listed amongst your available apps.. in this blog we will be using Outlook, Power automate, Forms, SharePoint, Teams and Planner.

The MS office apps available.

On our website we have basically three ways for our customers to contact us:

  • calling an 0800 number
  • direct email and,
  • filling out a form.

When people contact us by one of these means, an email is always generated by the existing systems and sent to my inbox.  I would then  allocate the leads manually to team members via email. So during lockdown while business was going to be very quiet, now was a great time to take the time to automate automate automate.

The first thing we did was swap the various forms on our website for Microsoft Forms. This turned out to be very easy.. When you create a new form you are given the option for generating a link or for embedding the form in a normal webpage. The information that visitors now enter into the forms can be easily handled with Flow in many ways.

I wanted to be given the option of not publishing all leads automatically, sometimes I want to allocate a job to a particular individual or group, In Flow this is called an Approval and you can set it up so that when a form is filled in, the information presented is put into a message and sent to your mobile phone where you can accept or reject it.

Send approval request when a MS form is filled in by site visitor

If there is more than one form response we need to put the whole procedure in a loop so that each request is handled. Notice that we then test if the Approval was ‘Approved’ and if so we will take the left hand branch labelled ‘If Yes’ so far so good.

The 'Yes' option for our MS Flow

For us the next consideration was where and how we were going to store our response data, then how we were going to best disseminate that information to our various teams.

After some consideration we chose to use a SharePoint list. Initially I wanted to use a  MySQL database, but after some research it appears that connecting Flow to MySQL was not very straightforward, MS haven’t currently published a connector. Staying within Microsoft is likely to be the best way to use Flow, although there are loads of connectors to many other databases. Anyway, we already have SharePoint as part of our Office 365 subscription, time to use it.

It’s easy to pass Forms data to SharePoint, but we needed each record to have a unique LeadID. This can be done in various ways in SharePoint, but they all turned out to have their issues. So in the end we decided to use a repository called CDS or Common Data Service to generate the LeadID.

CDS is a repository in that it creates a unique ID, so we used it to generate a new record for each form entry and then store the information in our SharePoint List. Why not leave it in CDS you ask? It looks like CDS is really intended for people using Dynamics 365, we’re not, and anyway Dynamics is a bit too big for us just now..

So we create a record in CDS and then after some processing we use that LeadID as a projectID when creating a new SharePoint item.. I can FEEL the criticisms of this approach already, it is a bit messy but after a day or so of hunting around this turns out to be the best scenario for our purpose.

passing CDS id to a SharePoint List item

Next time we will look at how we actually do this, the options we have after these steps, and why we chose to go with MS Teams.

Automating tasks with excel (1)

Cut and Fill

With Construction Twin we want to model the foundations  and the cut and fill process of a construction project.  Every square meter of the site

Read More »

Need Help With Excel?

The Excel Experts are here to help with your Excel tasks big or small, including spreadsheet help, consulting, reporting, VBA, automation, and Power Bi. Let us help you take your Excel skills to the next level with our experience in delivering solutions.

    Max. file limit is 20mb

    Recommended For You

    The Excel 2023 Book

    Master Microsoft Excel from scratch in less than 7 minutes a day!