{"id":156,"date":"2022-01-06T10:27:58","date_gmt":"2022-01-06T10:27:58","guid":{"rendered":"https:\/\/blog.citrus-lime.com\/crmc\/?p=156"},"modified":"2022-01-06T10:27:59","modified_gmt":"2022-01-06T10:27:59","slug":"the-3-phases-for-using-multi-select-option-sets-in-flow-with-microsoft-forms","status":"publish","type":"post","link":"https:\/\/blog.citrus-lime.com\/crmc\/the-3-phases-for-using-multi-select-option-sets-in-flow-with-microsoft-forms\/","title":{"rendered":"The 3 Phases for Using Multi-Select Option Sets in Flow with Microsoft Forms"},"content":{"rendered":"\n
\"Power<\/figure><\/div>\n\n\n\n

Project – <\/strong>CRMCS have recently been working on a client project integrating Microsoft Forms and Dynamics 365 using Power Automate.<\/p>\n\n\n\n

Aim – <\/strong>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.<\/p>\n\n\n\n

Solution – <\/strong>For the solution to this, you\u2019ve 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\u2019ve detailed these three steps below:
<\/p>\n\n\n\n

1. Triggering the flow off the submission of a new form<\/strong><\/p>\n\n\n\n

This step is pretty straightforward. We need to first create a new cloud flow, and find the trigger step from Microsoft Forms \u2018When a new response is submitted\u2019 and select your form<\/p>\n\n\n\n

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 \u2013 when you open the form editor you can extract this from the string \u2018id=abdc0123\u2026\u2019 within the URL!<\/p>\n\n\n\n

Then we need another step from the Microsoft Forms steps, called \u2018get response details\u2019: again, input your form id and then select response id from the dynamic values in the first step<\/p>\n\n\n\n

\"1\"<\/a><\/figure>\n\n\n\n

2. Transforming the data from the form answers into the right format for Dynamics<\/strong><\/p>\n\n\n\n

This is the interesting bit! The \u2018get response details\u2019 step will output the form answers in a particular format, and Dynamics 365 will only accept input data in the \u2018create\/update a record (CDS)\u2019 step in a particular format \u2013 these formats don\u2019t always align.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Boolean<\/p><\/blockquote>\n\n\n\n

Output from Microsoft Form step: \u2018Yes\u2019\/\u2019No\u2019<\/p><\/blockquote>\n\n\n\n

Input required by CDS step: \u2018true\u2019\/\u2019false\u2019<\/p><\/blockquote>\n\n\n\n

Regular (single select) option set<\/p><\/blockquote>\n\n\n\n

Output from Microsoft Form step: \u2018Option one\u2019\/\u2019Option two\u2019 etc<\/p><\/blockquote>\n\n\n\n

Input required by CDS step: \u2018640000000\u2019\/\u2019640000001\u2019 etc (i.e. the dynamics 365 database value of the option set)<\/p><\/blockquote>\n\n\n\n

For these two field types, we just need to swap one value for another \u2013 e.g. swap Yes to true, or swap Option one for 640000000.<\/p>\n\n\n\n

We can do this by using the Data Operation action \u2018Compose\u2019. Add in a new Compose step, then input the answer you want from the Microsoft form step.<\/p>\n\n\n\n

Now add a new Compose step, and open the \u2018Expression\u2019 tab of the dynamic content panel.<\/p>\n\n\n\n

Now we want to use the syntax: replace(outputs(\u2018Compose_x\u2019),\u2019inputvalue\u2019,\u2019outputvalue\u2019)<\/em><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

\"2.1\"<\/a><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

Multi-select option set<\/p><\/blockquote>\n\n\n\n

Output from Microsoft Form step: \u2018[\u201cOption one\u201d,\u201cOption three\u201d,\u201cOption six\u201d]\u2019 etc<\/p><\/blockquote>\n\n\n\n

Input required by CDS step: \u2018640000000,640000002,640000005\u2019 etc<\/p><\/blockquote>\n\n\n\n

N.B. It\u2019s important here that the database values have to go in with only a comma between them, no spaces, and no other punctuation.<\/p>\n\n\n\n

Multi-select option sets are a bit more complicated to get into the correct format, it\u2019s 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.<\/p>\n\n\n\n

We can use the same functionality as before to replace the options: replace(outputs(\u2018Compose_x\u2019),\u2019inputvalue\u2019,\u2019outputvalue\u2019)<\/em><\/p>\n\n\n\n

Then we need to add in the compose steps replacing punctuation. For the brackets, we can just use replace(outputs(\u2018Compose_x\u2019),\u2019[\u2019,\u2019\u2019) <\/em>and the same for the closed bracket.<\/p>\n\n\n\n

For the quote marks, it\u2019s a bit more complicated still, as the compose function doesn\u2019t like it when you try to put a quote mark directly into the replace function.<\/p>\n\n\n\n

We therefore have to add an \u2018intiailize variable\u2019 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(\u2018Compose_x\u2019,variables(\u2018QuoteMark\u2019),\u2019\u2019)<\/em><\/p>\n\n\n\n

\"2.2\"<\/a><\/figure>\n\n\n\n

Once you\u2019ve 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.<\/p>\n\n\n\n

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 \u2013 these all get processed very quickly and shouldn\u2019t pose too much of an issue with flow performance, but they\u2019re 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.<\/p>\n\n\n\n

\"2.3\"<\/a><\/figure>\n\n\n\n

It\u2019s important to note that these are not the only fields that require a bit of thought when pushing into a CDS step, but they\u2019re 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 \u2018pluralrecordtype(recordguidid)\u2019 e.g. contacts(091-esfj-3917-49fn). We\u2019ve 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<\/a><\/p>\n\n\n\n

3. Creating a new record in Dynamics and inputting the field values.<\/strong><\/p>\n\n\n\n

Now that we\u2019ve transformed all of our form answers to the correct format, it\u2019s simple to create a record in dynamics and input these field values. Find the Common Data Service (current environment) and select the \u2018create a new row\u2019 flow action step.<\/p>\n\n\n\n

Here, select your entity type, and then input each relevant field from the dynamic values panel \u2013 either direct answers from the form, or the final outputs from the compose steps you set up before.<\/p>\n\n\n\n

\"3\"<\/a><\/figure>\n\n\n\n

One thing this doesn\u2019t really account for, is the option for adding a free text \u2018Other\u2019 option to your Microsoft form option select question. If you\u2019re allowing the user to just type in whatever they want as another option, then we can\u2019t use the replace function to detect this. I\u2019m sure there\u2019s a clever way around this, but I haven\u2019t figured it out yet. If you have a solution for this, please let me know! Otherwise, when I find a solution, I\u2019ll be updating this article.<\/p>\n\n\n\n

Now that we\u2019ve 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\u2019re 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.<\/p>\n\n\n\n

Further Reading >><\/strong><\/p>\n\n\n\n

If you want to read more about pulling data in from Microsoft Forms to Dynamics using flow, we\u2019ve published another article here: Using Power Automate to Integrate Microsoft Forms with Dynamics<\/a> 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!<\/p>\n","protected":false},"excerpt":{"rendered":"

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<\/p>\n","protected":false},"author":43,"featured_media":88,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_price":"","_stock":"","_tribe_ticket_header":"","_tribe_default_ticket_provider":"","_tribe_ticket_capacity":"0","_ticket_start_date":"","_ticket_end_date":"","_tribe_ticket_show_description":"","_tribe_ticket_show_not_going":false,"_tribe_ticket_use_global_stock":"","_tribe_ticket_global_stock_level":"","_global_stock_mode":"","_global_stock_cap":"","_tribe_rsvp_for_event":"","_tribe_ticket_going_count":"","_tribe_ticket_not_going_count":"","_tribe_tickets_list":"[]","_tribe_ticket_has_attendee_info_fields":false,"footnotes":""},"categories":[3],"tags":[],"class_list":{"0":"post-156","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-crm"},"featured_image_src":"https:\/\/blog.citrus-lime.com\/crmc\/wp-content\/uploads\/sites\/30\/2021\/12\/DocDriveSiteimageclouds-scaled.jpg","author_info":{"display_name":"jadesmith","author_link":"https:\/\/blog.citrus-lime.com\/crmc\/author\/jadesmith\/"},"_links":{"self":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/comments?post=156"}],"version-history":[{"count":1,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/156\/revisions"}],"predecessor-version":[{"id":157,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/156\/revisions\/157"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media\/88"}],"wp:attachment":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media?parent=156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/categories?post=156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/tags?post=156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}