Following on from my previous post ‘Convert a spreadsheet or CSV file into a Marketing List in Dynamics 365 - Strings’, this post will expand the functionality to also be able to populate Lookup values, rather than just strings. To keep this as succinct as possible - if it’s blurry - I covered it in the previous post.

This is probably not the most efficient way to do this, I use a lot of compose steps that could be trimmed out but it makes it easier to read/build out/understand this way (I hope!).

A new array for headers that need transforming into lookups

LookupHeadersArray - Initialize Variable - Array

Populate LookupHeadersArray

Roll on down to your ForEachHeader loop. We are adding the steps to check the Attribute type (which we already pulled back in the Azure HTTP call for metadata earlier), then if the field is a Lookup or an Owner attribute - we will append some details to the LookupHeadersArray for later.

AttributeType - Compose

first(body('Filter_array'))?['AttributeType']

IsLookupOrOwner - Condition Control

outputs('AttributeType')

is equal to Lookup OR Owner

If Yes

AppendLookupHeaderARRAY - Append to array variable - LookupHeaderArray

{
  "originalHeaderName": "@{first(body('Filter_array'))?['LogicalName']}",
  "newHeaderName": "@{concat(first(body('Filter_array'))?['LogicalName'],'@odata.bind')}"
}

Check for Lookup & Owner values in JSON

Roll on down to your UpdateOrCreateRecord-AddToMarketingList loop. We are adding the steps to catch the current item in a compose step (you will see why later!), then check if there are any Lookups we need to fix, by checking in the LookupHeadersArray.

ITEM - Compose

item()

HasLookups - Condition Control

empty(variables('LookupHeadersArray'))

is equal to false

If Yes

UpdateLookups - Apply to each

variables('LookupHeadersArray')

Which table does the Lookup value come from?

To populate a Lookup or Owner via JSON in Dataverse the format has to look like this:

"ownerid@odata.bind":"/systemusers(1beb22c7-83ef-ec11-bb3d-000d3a1c66d8)"

Currently it looks something like this:

"ownerid":"AmeyHolden"

So we have got some data munching/string mashing to do!

This set of actions are all going to be inside the ‘UpdateLookups’ loop, looking at how we identify which table the Lookup value comes from (aka the Target), for example the Parent Contact for a lead, comes back from the Contact table

OriginalHeaderName - Compose

items('UpdateLookups')?['originalHeaderName']

OriginalHeaderValue - Compose (note here the magic wizardry to be able to dynamically refer to an objects properties!)

outputs('ITEM')?[outputs('OriginalHeaderName')]

GetTarget - Invoke an HTTP request with Azure AD

Method: GET

https://@{outputs('ODataURL')}/api/data/v9.2/EntityDefinitions(LogicalName='@{toLower(triggerBody()['text'])}')/Attributes(LogicalName='@{outputs('OriginalHeaderName')}')/Microsoft.Dynamics.CRM.LookupAttributeMetadata?$select=Targets

TargetName - Compose (there will only ever be one Target value for a regular lookup)

first(body('GetTarget')?['Targets'])

Dynamically formulate the search query

Stay inside the ‘UpdateLookups’ loop. Get the metadata we need to be able to Dynamically formulate the search query for the record, using the Primary name field and ensuring the record is active, or the owner is enabled.

GetTargetDetails - Invoke an HTTP request with Azure AD

Method: GET

https://@{outputs('ODataURL')}/api/data/v9.2/EntityDefinitions(LogicalName='@{outputs('TargetName')}')?$select=EntitySetName,PrimaryNameAttribute,PrimaryIdAttribute

FindRecordViaEntiySetName - List rows with Dataverse

TableName

concat(first(body('GetTarget')?['Targets']),'s')

FilterRows

@{body('GetTargetDetails')?['PrimaryNameAttribute']} eq '@{outputs('OriginalHeaderValue')}'@{if(equals(outputs('TargetName'), 'systemuser'), ' and isdisabled eq false', ' and statecode eq 0')}

Update or remove the lookup value

Staying within the ‘UpdateLookups’ loop again. IF we have successfully identified a row from the search above, we can update the JSON to the structure required ("ownerid@odata.bind":"/systemusers(1beb22c7-83ef-ec11-bb3d-000d3a1c66d8)") or we remove the item from the JSON to ensure the whole create step does not fail.

RecordFound - Condition Control

empty(body('FindRecordViaEntiySetName')?['value'])

is equal to false

If Yes

NewItem - Compose

"@{items('UpdateLookups')?['newHeaderName']}":"/@{body('GetTargetDetails')?['EntitySetName']}(@{first(outputs('FindRecordViaEntiySetName')?['body/value'])?[body('GetTargetDetails')?['PrimaryIdAttribute']]})"

OriginalItem - Compose

"@{outputs('OriginalHeaderName')}":"@{outputs('OriginalHeaderValue')}"

ReplaceOriginalWithNew - Compose

replace(string(variables('CreateContactOrLead')),outputs('OriginalItem'),outputs('NewItem'))

ReplaceLookupInCreateContactOrLead - Set Variable CreateContactOrLead

json(outputs('ReplaceOriginalWithNew'))

If No

RemoveLookupFromObject - Compose

removeProperty(variables('CreateContactOrLead'), outputs('OriginalHeaderName'))

RemoveLookupInCreateContactOrLead - Set Variable CreateContactOrLead

outputs('RemoveLookupFromObject')

I‘m Done

This then flows nicely back into the original flow with the updated variable CreateContactOrLead ready for its next steps. If you made it this far without saying ‘what the f*ck’ at least once it will be a miracle. The more I write of this blog, the more I see it as a lesson of when to stop digging and climb out your rabbit hole before it’s too late. Thanks for listening/reading/entertaining my madness

P.S Assumptions/Limitations

• Polymorphic lookups will only search on their first alphabetic table e.g. owner (but you could easily fix this up in the flow)

• Lookup search is by Primary Name only, if multiple are found with the same name, the first one returned will be chosen

• If a lookup value cannot be found by primary name it will be skipped and not captured as part of the creation (but the creation will not fail because of it)

• It will totally bomb out when the table multiple name is ‘ies’ rather than ‘s’ e.g. Opportunities (future Amey’s problem)