The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms

   Words by CRM Consultancy

   on 25/02/2021 12:31:00

Power Apps | Innovative Business Apps | Kick ICT

Project - CRMCS have recently been working on a client project integrating Microsoft Forms and Dynamics 365 using Power Automate.

Aim - The aim of the project was for a new form submission to automatically create a new record in Dynamics 365 with all the answers from the form put into fields on the record, and then for the client to be able to view, filter, and report in Dynamics on those records.

Solution - For the solution to this, you’ve got three main steps to build up in Power Automate: triggering the flow off the submission of a new form, transforming the data from the answers into the correct format to input, and then creating a new record with all these details in Dynamics 365. I’ve detailed these three steps below:

1. Triggering the flow off the submission of a new form

This step is pretty straightforward. We need to first create a new cloud flow, and find the trigger step from Microsoft Forms ‘When a new response is submitted’ and select your form

N.B. if someone else created the form and shared it with you, you may not be able to select the form in the dropdown, but you can insert the form id manually – when you open the form editor you can extract this from the string ‘id=abdc0123…’ within the URL!

Then we need another step from the Microsoft Forms steps, called ‘get response details’: again, input your form id and then select response id from the dynamic values in the first step

1

2. Transforming the data from the form answers into the right format for Dynamics

This is the interesting bit! The ‘get response details’ step will output the form answers in a particular format, and Dynamics 365 will only accept input data in the ‘create/update a record (CDS)’ step in a particular format – these formats don’t always align.

Any string or number fields will work fine without any manipulation (provided there are no restrictions on length/decimals etc) but things like Booleans or option sets will need to be changed.

Boolean

Output from Microsoft Form step: ‘Yes’/’No’

Input required by CDS step: ‘true’/’false’

Regular (single select) option set

Output from Microsoft Form step: ‘Option one’/’Option two’ etc

Input required by CDS step: ‘640000000’/’640000001’ etc (i.e. the dynamics 365 database value of the option set)

For these two field types, we just need to swap one value for another – e.g. swap Yes to true, or swap Option one for 640000000.

We can do this by using the Data Operation action ‘Compose’. Add in a new Compose step, then input the answer you want from the Microsoft form step.

Now add a new Compose step, and open the ‘Expression’ tab of the dynamic content panel.

Now we want to use the syntax: replace(outputs(‘Compose_x’),’inputvalue’,’outputvalue’)

Where x is the number of the compose step you are replacing from, the inputvalue is Yes or Option one, and outputvalue is true or 640000000.

2.1

Repeatedly add compose steps until you have replaced all possible option set answers for values, ensuring that you are always referencing the compose step directly before in the replace function.

Multi-select option set

Output from Microsoft Form step: ‘[“Option one”,“Option three”,“Option six”]’ etc

Input required by CDS step: ‘640000000,640000002,640000005’ etc

N.B. It’s important here that the database values have to go in with only a comma between them, no spaces, and no other punctuation.

Multi-select option sets are a bit more complicated to get into the correct format, it’s not just a direct replace one value for another. We need to remove all the surrounding punctuation, aside from the commas, as well as replacing the values.

We can use the same functionality as before to replace the options: replace(outputs(‘Compose_x’),’inputvalue’,’outputvalue’)

Then we need to add in the compose steps replacing punctuation. For the brackets, we can just use replace(outputs(‘Compose_x’),’[’,’’) and the same for the closed bracket.

For the quote marks, it’s a bit more complicated still, as the compose function doesn’t like it when you try to put a quote mark directly into the replace function.

We therefore have to add an ‘intiailize variable’ flow step, specify string variable, and define this as the quote mark. Then we can add a new compose step and use the syntax: replace(outputs(‘Compose_x’,variables(‘QuoteMark’),’’)

2.2

Once you’ve added these three compose steps for the punctuation, and each compose step for the various possible options, your final output for the last compose step should appear as 640000000,640000002,640000005.

In this flow, I had multiple Boolean, option sets and multi-select option sets to be dealing with. I did end up with a lot of compose steps in my flow – these all get processed very quickly and shouldn’t pose too much of an issue with flow performance, but they’re a bit tricky to keep track of. To deal with this, I just renamed the last replace compose step for each form question with the name of the Dynamics 365 field I wanted to insert it into. This way the next stage is easier to manage.

2.3

It’s important to note that these are not the only fields that require a bit of thought when pushing into a CDS step, but they’re the ones we can use this specific functionality to deal with. Lookup fields are a bit of a confusing one, they require the input to be in the format ‘pluralrecordtype(recordguidid)’ e.g. contacts(091-esfj-3917-49fn). We’ve discussed in further detail how to input a lookup field in a CDS step in Microsoft flow in a separate article here: Building a New Scheduled Process Using Flow

3. Creating a new record in Dynamics and inputting the field values.

Now that we’ve transformed all of our form answers to the correct format, it’s simple to create a record in dynamics and input these field values. Find the Common Data Service (current environment) and select the ‘create a new row’ flow action step.

Here, select your entity type, and then input each relevant field from the dynamic values panel – either direct answers from the form, or the final outputs from the compose steps you set up before.

3

One thing this doesn’t really account for, is the option for adding a free text ‘Other’ option to your Microsoft form option select question. If you’re allowing the user to just type in whatever they want as another option, then we can’t use the replace function to detect this. I’m sure there’s a clever way around this, but I haven’t figured it out yet. If you have a solution for this, please let me know! Otherwise, when I find a solution, I’ll be updating this article.

Now that we’ve completed these three steps, we can test our flow by manually submitting a form, and looking at the outcome of the flow run. When troubleshooting with flow, you can view the output of each compose step along the way, so if you’re getting an error in Dynamics, you can work backwards and figure out at what point the flow went wrong. You can use this for any number of fields to map from your Form to Dynamics, using the same flow, and you can add further flow steps to do other functionality too, for example to notify a user by email when a new Form is submitted.


Further Reading >>

If you want to read more about pulling data in from Microsoft Forms to Dynamics using flow, we’ve published another article here: Using Power Automate to Integrate Microsoft Forms with Dynamics This goes into detail on how to search the database for duplicate values, and how to update this record if found, and create a new record if not!

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

HOW TO: Manage Your Dynamics 365 Database Size (Video Included) Dynamics 365 Marketing vs ClickDimensions It’s time to pause, reflect and acknowledge a new era of inclusivity and collaboration. Part 2 - How to get the most from a Technology Expert – Asset Management Hub Property & Asset Management Hub Part 1 – Balancing CRM and Asset Management Scopes - Asset Management Hub Creating a Multi-Lingual PowerApps Portal How to Set Up a Microsoft Teams Site Using DocDrive365 Microsoft Teams - Adding a Microsoft Teams URL to a Dynamics Appointment Dynamics 365 Marketing – Customer Voice Survey Not Appearing In Emails? Using SQL Management Studio to connect to the Dynamics DB Calling a Power Platform AI Builder Model via oData How to use DocDrive365 to integrate permissions between Business Units in Dynamics with Sites in SharePoint Getting started with the Power Platform AI Builder. Power Apps Portal Information Hub DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Part 5 - Power Apps Portals: How To Connect Azure B2C With Linked-In Part 4 – Power Apps Portals: Styling Azure B2C for Power Apps Portals The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms Part 3 – PowerApps Portals: Azure B2C and Power Apps Portals – User Flow for Signup and Signin Part 2 - Power Apps Portals: New Application Registration in Azure B2C for our Power Apps Portal Part 1 – Power Apps Portals: Creating a New Azure AD B2C Tenant The Automation Bot: Launching Contextual Flow from Teams Creating a New Bot for Teams Debugging your Teams Bot using Ngrok
Contact Us

Want expert advice or a demo?

Get in touch now and see how we can help your business grow.

  • Name
  • Email Address
  • Phone Number
 
Close

Understanding Your Challenges

Our strong understanding of CRM and emerging technologies within the Microsoft environment means we deliver the right solutions for you.

Proven Real-World Solutions

As a leader in the field of Dynamics solutions, our pedigree developing and delivering real-world solutions is unsurpassed.

Long Term Support

We provide support beyond our design, implementation and 'go-live' delivery using Sprints and continual updates to our AppSource apps.

CRMCS | Design by Thinktank Marketing | Citrus-Lime Limited

To improve your experience today and in the future, this site uses cookies. Read our full Privacy Policy & Cookie information here I Understand