Choices: Spreadsheet or CSV into Dynamics 365 JSON
The final nail in the coffin of the ‘Convert a spreadsheet or CSV file into a Marketing List in Dynamics 365’ saga this post is short and sweet, it shows you how to update the monster CSV converting flow to also handle Choices (Option Sets) as part of the Contact or Lead creation. Thankfully converting Choice labels e.g. Lead Source ‘Web’ into it’s relevant integer value ‘8’ was a previous topic I covered here.
The pattern we use here is very similar to what we used for lookup and owner resolving steps in my previous post.
A new array for headers that need transforming
ChoiceHeadersArray Initialize Variable - Array ChoiceHeadersArray
Populate ChoiceHeadersArray
Roll on down to your ForEachHeader loop. Adjust the AttributeType logic to use a switch instead of a condition action, then if the field is a ‘Picklist’ attribute - we will append some details to the LookupHeadersArray for later.
AttributTypeSwitch - Switch Control
On
outputs('AttributeType')Case: Picklist
AppendChoiceHeaderARRAY - Append to array variable - ChoiceHeaderArray
first(body('Filter_array'))?['LogicalName']Case: Lookup & Case: Owner
AppendLookupHeaderARRAY - Append to array variable - LookupHeaderArray
{
"originalHeaderName": "@{first(body('Filter_array'))?['LogicalName']}",
"newHeaderName": "@{concat(first(body('Filter_array'))?['LogicalName'],'@odata.bind')}"
}
Check for Choice values in JSON
Roll on down to your UpdateOrCreateRecord-AddToMarketingList loop to check if there are any Choices we need to fix, by checking in the ChoiceHeadersArray.
HasChoices - Condition Control
empty(variables('ChoiceHeadersArray'))If Yes
UpdateChoices - Apply to each
Select an output from previous steps
variables('ChoiceHeadersArray')
Translate choice labels into integers
To populate a Choice value via JSON in Dataverse the format needs to be the integer value rather than the label. This set of actions are all going to be inside the ‘UpdateChoices’ loop which run for each choice column your imported file contains. You will see that here I am using a child flow called ‘Get Int from Option Set Label’ which I created using the technique in this blog. You can also just put the steps straight in here if you are not too sure about child flows.
OriginalHeaderNameChoice - Compose
item()OriginalHeaderValueChoice - Compose
outputs('ITEM')?[outputs('OriginalHeaderNameChoice')]Get Int from Option Set Label - Run a child flow Get Int from Option Set Label
Label
outputs('OriginalHeaderValueChoice')LogicalNameColumn
outputs('OriginalHeaderNameChoice')LogicalNameTable
tolower(triggerBody()['text'])LabelIntFound - Condition Control
int(body('Get_Int_from_Option_Set_Label')?['choiceint'])is greater than or equal to 0
Update or remove the choice value
Staying within the ‘UpdateChoices’ loop again. If we have successfully translated the label into an integer, we can update the JSON, or we remove the item from the JSON to ensure the whole create step does not fail.
If Yes
OriginalItemChoice - Compose
"@{outputs('OriginalHeaderNameChoice')}":"@{outputs('OriginalHeaderValueChoice')}"NewItemChoice - Compose
"@{outputs('OriginalHeaderNameChoice')}":@{body('Get_Int_from_Option_Set_Label')?['choiceint']}ReplaceOriginalWithNewChoice - Compose
replace(string(variables('CreateContactOrLead')), outputs('OriginalItemChoice'), outputs('NewItemChoice'))ReplaceChoiceInCreateContactOrLead - Set Variable CreateContactOrLead
json(outputs('ReplaceOriginalWithNewChoice'))If No
RemoveChoiceFromObject - Compose
removeProperty(variables('CreateContactOrLead'), outputs('OriginalHeaderNameChoice'))RemoveChoiceInCreateContactOrLead - Set Variable CreateContactOrLead
outputs('RemoveChoiceFromObject')Congratulations, your mission is complete
This part also flows nicely back into the original flow with the updated variable CreateContactOrLead ready for its next steps. I’m going to leave this flow based monstrosity to rest in peace now. Heady spreadsheet exterminating!