Dataverse Add a New Row action from an Excel table, dynamic content is not possible for Choice columns

(0) Share

Report

Posted on by Mchallinor

I am building a flow that 1) Lists rows from a table in Excel, 2) for each row, Add a row in a Dataverse table.

However, the data output from "List rows present in a table" cannot be added to the Choice column on the Dataverse.

In the screenshot the Choice fields have a dropdown to select the choice, however, I need to add the dynamic content from the output of the 'List rows' step.

How can I "Enter a Custom Value" using the dynamic content from the List Rows Present in a Table output?

Additional Information.

The data output from the "List Rows Present in a Table" step contains the text/name of the choice in the Dataverse.
But I'm now assuming that for the "Add new row" step in Dataverse I'll need the Value (enum) of the choice to add it.

Taking a peek at the code, I see that it would look something like :
"item/ms_itemstatus": 100000001
I'm not sure of a custom expression that I could use? (Perhaps an 'if statement').

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

grantjenkins 11,063 Moderator on at

Like (1)

Report
If the choices in Dataverse weren't going to change, I'd probably just add a mapping object in the flow where you could use the name from Excel and return the Id of the Choice required for Dataverse. See an example below.

I have the following Excel Table.

And the following Dataverse Table.

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

Colors is a Compose that is my mapping table with the name of the color and the Id in Dataverse.

{ "Red": 1, "Green": 2, "Blue": 3 }

List rows present in a table retrieves my rows from Excel.

Apply to each iterates over each of the rows.

Add a new row uses the following expression to map the color name to Id.

outputs('Colors')?[item()?['Color']]

After running my flow, I should have my new rows added.

Was this reply helpful? Yes No
Mchallinor 9 on at

Like (0)

Report

Hi Grant,

Thanks for your help. That has achieved exactly what I needed.

FYI, I worked on a different method that also worked.
First, initialise a string variable before "apply to each row"
Then during the "apply to each row" Set the string variable with the outputs from the Excel table.

Using your Name and Color example it would have looked something like this:

Then when adding the row to the Dataverse, for the Choice column, you can use an If Statement like this:
if(equals(variables('strColor'),'Red'),string(1),if(equals(variables('strColor'),'Green'),string(2),if(equals(variables('strColor'),'Blue'),string(3),string(0))))

But I accept your solution is cleaner and easier to maintain the array of colors (in the compose step) than having to edit the custom expression. The If Statement is ugly in my solution.

Many thanks for your help.

Was this reply helpful? Yes No