Power automate to Copy SharePoint Multi Choice values to Dataverse multi select Choices field.

[Anonymous]

Hi,

I am trying to migrate data from SharePoint list to Dataverse table. Since the column structure is different from SharePoint list and Dataverse table, I am writing power automate flow to copy data from SharePoint list to Dataverse table. I am facing difficulty in updating the Multi Choice column values of SharePoint to Dataverse Choices (multi select) field.

Need some help here.

[Anonymous]

Could you please help me on this ?

@grantjenkins

[grantjenkins]

Working on it now - will update you when I've got the solution for your 🙂

[grantjenkins · Accepted solution]

Got it working.

In Dataverse your Choices are stored as integers. With multiple Choices they are stored as an array of integers (1,3,5 for example).

For this example, I created a Dataverse Table called Flags that has the Name column and another column called Color which is a multiselect Choice column.

And the Color values for each option are:

I then have a SharePoint List called Flags that has the Title column and another column called Color which is a multiselect Choice column.

And the following data that we want to copy to Dataverse.

The full flow is below. I'll go into each of the actions.

The first step is to create an object that will allow us to map the SharePoint choices to the corresponding Dataverse choice values. If we select Green in SharePoint then that would be 1 in Dataverse, Blue would be 2, etc. It would be nice to dynamically generate the mapping, but would likely need to use an API called to get the schema which I haven't done here - maybe something for me to work on 🙂

In my example I have:

{
  "Blue": 2,
  "Green": 1,
  "Red": 0,
  "Purple": 3,
  "Orange": 4
}

I then use Get items to get all the items from my SharePoint List.

Apply to each iterates over each of the items, and for each one it gets the Colors selected and maps each one to the appropriate Dataverse value. This results in an array of integers. The expression used in the Select is:

outputs('Compose_Colors')?[item()?['Value']]

I then Join each value separated by a comma.

Finally, I Add a new row in my Dataverse Table passing in the output from Join.

After running the flow, I end up with the rows added successfully.

Hope that all makes sense. The key thing is to get the values of your Choice field in Dataverse, then map the SharePoint choices.

[Anonymous]

Thank you so much. I couldn't figure out this expression yesterday when i am trying the solution.

outputs('Compose_Colors')?[item()?['Value']]

Any recommendations/ tutorials for learning such powerful expressions? Thanks once again.

[Anonymous]

Hi,

Getting 'Edm Object passed should have the options selected' error and record is skipped copying to dataverse table, when the value in SharePoint multichoice field is empty. How to get around with this?

[grantjenkins]

Ah - I'll add a fix for that tomorrow and send it through (almost midnight where I live at the moment).

[grantjenkins · Accepted solution]

Directly after the Join, add a Compose with the following expression:

if(equals(body('Join'), ''), null, body('Join'))

Then use the output of the Compose for your Add a new row Color property.

[WendyLumsden]

Wow - thank you @grantjenkins !!  Exactly the answer I needed for trying to populate a Dataverse Multichoice Field with input from a Form.  Extremely helpful thank you

[WendyLumsden]

@Anonymous @grantjenkins 

I tried many options suggested on this Forum and only one solution worked for me in the case of a user submitting a MS Form with multi-select options (for Communication Channels in this case).  The requirement was to update a Dynamics solution table field or column with the all the options selected by the User.   

Here is the solution which ran successfully:

1. The first step was to replace all the labels selected by the user in the Form with the number values required for Dataverse:

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(outputs('Get_response_details')?'body/r7f3e.........29fe8'], 'Facebook','948170000'),'Linked In','948170001'),'Instagram','948170002'),'Twitter','948170003'),'Newsletter','948170004'),'Blog','948170005'),'Website','948170006'),'Television','948170007'),'Radio','948170008'),'Other','948170009'),'Newspaper','948170010')

and then to replace all the resultant inverted commas which can probably be done in the same step as above but I like to keep things simple for clients to maintain:

replace(replace(replace(outputs('Replace_input_labels_with_multichoice_values'),'"',''),'[',''),']','')

2. The next step was to create an array of the resultant numbers (creating an array will allow you to work with each number seperately:

split(outputs('Clean_up_the_result'),',')

 3. The third step is to convert the string numbers to integers, which are required for updating Dataverse.  I had previously tried Join expression and a variety of other alternatives but the only one that ran successfully for multiple options was to loop through the array and within the loop use Append to string function with int() to create the input used for updating Dataverse.   The string I was appending to was a variable which is used to update Dataverse/Dynamics

concat(int(items('Apply_to_each')),',')

Concat is used to add the comma after each integer or choice value. 
4. The final steps are simple - just remove the comma at the end of the resultant variable by using length of variable, subtracting 1 from length and using substring function to provide the variable used to update Dataverse.
My flow looks like this:

 Please mark this is as a solution if it works for you or is helpful 🙂

[grantjenkins]

@WendyLumsden This is how I would design the flow for your scenario.

I have a Dataverse Table with Name and Media (Multiselect Choices). The Media Choices are defined as so (to match yours):

And a Microsoft Form with just Name and Social options.

My full flow below (very similar to my original solution). I'll go into each of the options.

When a new response is submitted for a new form submission and Get response details get the form responses.

Compose Keys maps your Choices with the relevant Dataverse Choice Keys. If you add/remove a choice from your Form, then you can just remove/add from here making it easy to manage.

The JSON used in this instance is below.

{
  "Facebook": 948170000,
  "LinkedIn": 948170001,
  "Instagram": 948170002,
  "Twitter": 948170003,
  "Newsletter": 948170004,
  "Blog": 948170005,
  "Website": 948170006,
  "Television": 948170007,
  "Radio": 948170008,
  "Newspaper": 948170010,
  "Other": 948170009
}

Select Keys extracts out the Keys into an array.

The expression to convert the choices to an array is below.

//json() converts the choices to an actual array as they are returned as a string from MS Forms.

json(outputs('Get_response_details')?['body/r8e07e415c59d43969d5296d01ad39506'])

And the expression to map the Choice to its relevant Key is:

outputs('Compose_Keys')?[item()]

Join Keys joins each of the keys separated by a comma.

Compose Options is used to check if at least a single Choice was selected. If no Choices selected, then it will return Null. If your Microsoft Form Choices are set as Required, then you wouldn't need this step. The expression used is:

if(equals(body('Join_Keys'), ''), null, body('Join_Keys'))

And finally, we can Add a new row in our Dataverse Table.

[WendyLumsden]

Thank you - definitely more elegant solution 🙂
I am a rank beginner in PowerAutomate and not quite got my head around JSON and related commands so thank you for the encouragement to learn more!