Lookups and Owners: Spreadsheet or CSV into Dynamics 365 JSON
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=TargetsTargetName - 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,PrimaryIdAttributeFindRecordViaEntiySetName - 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)