Mapping Option Sets/Choice Labels from Dynamics 365 Marketing Form Submissions to Custom Tables with Power Automate
Following on from a previous post “Mapping Dynamics 365 Marketing Form Submissions to Custom Tables with Power Automate“, I covered option sets, but only how to get the value of the option set/choice e.g. 100006, not the actual label ‘Vegemite’. I used a previous blog post of mine “Set Dataverse Choices in Power Automate dynamically - without a switch statement!“ and kind of reverse engineered it. Rather than using the label to find the value, we are going to use the value to find the label, and then add it on to the form submission response string for parsing later.
PS fluffy intro and background reading is in this post, no socks will be put on centipedes here - I also realised I could strip out a lot of extra unnecessary actions from the previous method which is soooooo satisfying!
TLDR: skip to ‘Option Set Labels Please‘
Get the form submission
Using the Dataverse trigger ‘When a row is added, modified or deleted‘ we can get the flow to run when the form is submitted and ready to process
I have filtered here to specific form by name but this could easily be updated to consider other forms either by name or GUID or any other defining attribute. I have also filtered by status to ensure I only pick up successful (192350002) or finished (192350003) submissions.
Get the field submissions
Using the Dataverse action ‘List rows‘ we can first filter to get only the submission related to the current response and also to expand to get some important information about the marketing fields which we will use later on - don’t forget the expand, it is important!
Filter -> (_msdyncrm_formsubmissionid_value eq '@{triggerOutputs()?['body/msdyncrm_marketingformsubmissionid']}')
Expand Query -> msdyncrm_marketingformfieldid($select=msdyncrm_prototype_attribute,msdyncrm_marketingformfieldtype,msdyncrm_contactmapping,msdyncrm_leadmapping,msdyncrm_marketingformfieldtype)
Create your own ‘Submitted Values’ string
So we want to turn the individual field submissions into JSON as a single item which can then have each attribute mapped to our custom table. Although we are technically creating an array, create a String variable and set the initial value to [{.
Oh magical ‘Marketing Form Field Type’
We want to loop through each ‘Marketing field submission’, but they need to be handled different depending on their ‘Marketing Form Field Type’ (which your lovely expand query got for you). This post will focus on Option set (3), since we have already nailed Lookup (11), Multi-select option set (10) is a small extension on the single option set but I’m going to save that for another day (when I remember where on earth I have done it before so I can reuse it!) and the rest don’t need any special handling beyond a simple key value pair string. But first - error handling.
Skip the blank responses
Lets ensure we only ‘handle’ the fields that have a response captured, because if its blank it will cause our flow to fail and not all fields on the form will always be mandatory. Add a condition step that checks if the ‘Field Value’ is blank, if its blank we will simply append the Field Name and Field Value as a key value pair to our ‘Response String’ which will show that there was no data provided.
"@{items('Apply_to_each')?['msdyncrm_fieldname']}" : "@{items('Apply_to_each')?['msdyncrm_fieldvalue']}",
Switch it up
In the ‘No’ branch of your condition action, create a switch statement based on the ‘Marketing Form Field Type’, but just in case this comes back blank, lets make sure our Switch doesn’t cry (fail) by using coalesce and defaulting the value to ‘NULL’ so that the default action will proceed. Then add a case for Lookup (11) and Option Set Choice (3). Previous post covers what you need for Lookup so lets go get the Option Set magic.
coalesce(items('Apply_to_each')?['msdyncrm_marketingformfieldid/msdyncrm_marketingformfieldtype'],'NULL')Default the Default
In the Default case of the switch, add an action ‘Append to String Variable’ which appends the Field name and value as a key value pair, this is perfect for most other field types such as text, numbers and dates.
Option Set Labels Please
Five magic steps to turn a humble yet meaningless integer into a word that actually means something. It could technically be 3 steps by creating the TableLogicalName and FieldLogicalName directly in the Invoke an HTTP request action, but I’m not here to show off and boggle your brain too much, so I won’t 😉
STEP 1 & 2: Find the ‘logical name’ for the table and field
These two humble if() statements are sheer magic. We use the Marketing Form data fields to be able to determine the table and the field name which the option set relates to. I could write a whole article explaining why, but I would rather go for a run so if you really want to know - contact me!
//TABLE LOGICAL NAME
if(not(empty(items('Apply_to_each')?['msdyncrm_marketingformfieldid/msdyncrm_leadmapping'])),'lead',
if(not(empty(items('Apply_to_each')?['msdyncrm_marketingformfieldid/msdyncrm_contactmapping'])),'contact',
if(not(empty(items('Apply_to_each')?['msdyncrm_marketingformfieldid/msdyncrm_prototype_attribute'])),items('Apply_to_each')?['msdyncrm_marketingformfieldid/msdyncrm_prototype_entity'],'')))//FIELD LOGICAL NAME
if(equals(outputs('TableLogicalName'),'lead'),items('Apply_to_each')?['msdyncrm_marketingformfieldid/msdyncrm_leadmapping'],
if(equals(outputs('TableLogicalName'),'contact'),items('Apply_to_each')?['msdyncrm_marketingformfieldid/msdyncrm_contactmapping'],
items('Apply_to_each')?['msdyncrm_marketingformfieldid/msdyncrm_prototype_attribute']))STEP 3: Find the option set ‘metadata’
The answer to translating the integer ‘15’ into a label ‘Equipment Rental and Leasing’ is hidden deep in a land far far away called ‘Metadata’. Don’t be scared, just read my other blog post for more details if you get stuck! Add a ‘Invoke a HTTP request’ action, Method: GET and URL of the request is pasted below. You can see we are using the FieldLogicalName and TableLogicalName outputs in here, plus URIHost to determine the URL of the dynamics/power platform instance you are working in e.g. ameyholden.crm6.dynamics.com.
uriHost(items('Apply_to_each')?['@odata.id'])STEP 4: Filter the metadata
The previous action returns all the metadata for the field, so all the possible options in the option set have come back. Add a Filter Array action, then using the body() condition below we can pass in just the Option Set ‘Options’ rather than the full output from the previous step (note this will not be available as an item to select from the ‘Dynamic Content’ picker but I promise the expression works. The filter is just looking to find a match for the option set value in the metadata, using the number we got back in the marketing for submission ‘15’.
//FROM
body('Invoke_an_HTTP_request')?['OptionSet']?['Options']
//VALUE in metadata
item()?['Value']
//VALUE from form submission
int(items('Apply_to_each')?['msdyncrm_fieldvalue'])STEP 5: Append, Append, Append!
The option set label value is at your fingertips. Append to your existing string variable as below. The field name value here is optional, useful for mapping to create records later in your flow but in the case of showing the user the human readable version the ‘Label-UserLocalizedLabel-Label Label Label Label Label Label is the one you want.
"@{items('Apply_to_each')?['msdyncrm_fieldname']}" : "@{body('FilterOptionSet')?[0]?['Label']?['UserLocalizedLabel']?['Label']}",
"@{items('Apply_to_each')?['msdyncrm_fieldname']} Value" : "@{items('Apply_to_each')?['msdyncrm_fieldvalue']}",
Close the string variable
Finally just outside of your condition but inside the ‘For Each’ loop and our final ‘Append to string’ action that will be used to close out the string variable with }] to complete the JSON formatting.
Now what?
Jump back in to my last post at ‘Parse the Submitted Values’ to continue through the mapping and record updating things, maybe set up a nice note to add to the timeline that shows the form response summary in a human readable way, or maybe just give yourself a pat on the back and call it a day. You do you.