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!