Mapping Lookup values from Dynamics 365 Marketing Form Submissions with Power Automate

Following on from a previous post “Mapping Dynamics 365 Marketing Form Submissions to Custom Tables with Power Automate“ I covered how to handle lookups with Marketing Form Submissions when updating Dataverse records. But you also need to be able to get the human readable version without the record update.

This post shows you how to make the lookup human readable, so we can use it in other lovely automations where humans read stuff. We will turn things like this {"Id":"b580cd20-8e2a-ee11-bdf4-0022482578b5","LogicalName":"amey_country"} into to the actual label ‘Australia’. I’m going to assume you have got to the point of switching based on the ‘Marketing Form Field Type’ (this blog post) and you need to digest the lookup value from GUID into Text.

Parse the field value as JSON

First, we need to parse the marketing field response value of {"Id":"b580cd20-8e2a-ee11-bdf4-0022482578b5","LogicalName":"amey_country"} as JSON so that we can use the values for Id and LogicalName separately to find the name.

ParseLookupValues - Parse JSON
Content: items('ForEachFieldResponse')?['msdyncrm_fieldvalue']
Schema:

{
    "type": "object",
    "properties": {
        "Id": {
            "type": "string"
        },
        "LogicalName": {
            "type": "string"
        }
    }
}

Find the table plural name and primary attribute name

Now for a bit of Web API magic - you can use the ‘LogicalName’ from above, to find the correct pluralised name and primary name values. For example:

• LogicalName: amey_country

• LogicalCollectionName: amey_countries

• PrimaryNameAttribute: amey_name

These vary for each table so being able to grad this info without any hard coding per lookup is super handy!

GetEntityCollectionName - Invoke an HTTP Request
Method: GET
Url of the request: https://@{uriHost(items('ForEachFieldResponse')?['@odata.id'])}/api/data/v9.2/EntityDefinitions?$select=LogicalCollectionName,PrimaryNameAttribute&$filter=(LogicalName eq '@{body('ParseLookupValues')?['LogicalName']}')

To create a connection for this action see step 2a) here.

Get Dataverse Row using Dynamic Inputs

Using the outputs from the steps above we are able to get the correct dataverse row which has the human readable name.

GetName - Dataverse Get a row by ID
Table name: first(outputs('GetEntityCollectionName')?['body/value'])?['LogicalCollectionName']
Row ID: body('ParseLookupValues')?['Id']
Select columns: first(outputs('GetEntityCollectionName')?['body/value'])?['PrimaryNameAttribute']

Get the Name of the Row

And finally - the name of the row that the human can read - hurah!

PrimaryNameAttribute - Compose
Inputs: body/@{actions('GetName').inputs.parameters['$select']}

RecordName - Compose
Inputs: outputs('GetName')?[outputs('PrimaryNameAttribute')]

Append the result to your marketing form submission summary

Finally you can append the form field response to a variable which may hold a text summary of the form submission or maybe a JSON summary too.

Now what?

This blog post is intended as a stepping stone to be used as part of something else I have in the works (hint: exporting marketing form submissions to excel), but also super useful to extend your custom field mappings flow to create marketing form submission summaries too.

PS. sorry for ping ponging you to lots of different blog posts, I’m trying not to make these posts too long/boring!