Dynamically Populate Polymorphic Lookup Value in Power Automate without using Condition Step

In my previous post, I have described how to populate Owner value dynamically in Power Automate. The trick I used in that post only works for the Owner lookup though and not for polymorphic lookups. And the reason why is because if you are using the Dataverse/Common Data Service (current environment) connector, there is only one input parameter for the Owner column unlike  for polymorphic lookups where there can be multiple input parameters (one for each possible table type).

In this post, I will explain how you can dynamically populate a polymorphic lookup column by setting the "Entity name" parameter with an expression value and set the column values with a JSON object in "Record Item" parameter. Let’s have a look at scenarios where we can use this trick.

Scenario 1: Clone a Case and populate the Customer lookup (which can be an Account or Contact) with the original case’s Customer value.

You'd thought you can just populate the respective columns for each type but you cannot simply populate the Customer lookup as in the screenshot below. The value will be of only one table type for each case and the create action will always fail with the error below.

{Table Name} With Id = xxxxxx Does Not Exist

For the above scenario, you can get away with using the Condition branch to check the table type (logical name) of the lookup value and populate the Customer lookup accordingly. How about the Regarding column of the activity entity? How about the Connected From/To column of the Connections?  It can get pretty laborious if you have a few columns to check.

Scenario 2: Create a follow-up task for the email and populate the Regarding column with the same Regarding value from the email. The Regarding value can be Account, Contact, Case or other table types.

Scenario 3: When a Case row is cloned, all the related Connections need to be cloned. The case can be connected to any table type in the system (Account, Contact, Case or other custom tables).

Now let’s use the trick in the scenarios I mentioned above.

Update Polymorphic Lookup Value

The trick to updating polymorphic lookups easily is to create a row (e.g. task, connection) without the value for the polymorphic lookup first, and then update the row to set the value for it later on.

🛈 Note

The Power Automate Experimental Features are enabled for the account that I use to build this flow, so the expression builder in the screenshots of this post is different from the classic one. You can either follow this blog post to enable Experimental Features in your Power Automate settings or you will have to figure out the equivalent option in the classic expression builder.

The steps below address Scenario #2 above and describes how to set the Regarding Lookup.

The first step is pretty straight forward. List the Emails based on certain filter criteria and create a new Task for each Email and leave the Regarding column empty.

For the "Update a Record" action to populate the Regarding value, select the "+ Add a custom item" (or "Enter custom value" in classic one) for (Table) "Entity name" parameter to enter the custom value instead of choosing from the list.

Note that the value to be entered here as custom value is EntitySetName (not logical name) which is the plural/collection version of the logical name (e.g. tasks). You can find out more detail for this in this post.

The EntitySetName cannot be entered directly in the custom value textbox. The flow designer will render the step as the table-specific action with the parameters from the entity. If you use the expression builder and enter as a string literal (e.g. 'tasks'), it works for that particular session but when the flow is closed and re-opened, the flow designer will render the step as the table-specific step (as in the screenshot below).

To make sure that the step is rendered as a generic step, use a string function in the expression builder (e.g. trim('tasks')  ) and the end result should be something like this screenshot with "Record Item" parameter and no column parameter from the table.

The Record Item parameter accepts the column name and values as a JSON object. This is the sample Record Item parameter value to set the Regarding column with one of the Accounts using hardcoded GUID.

{
	"regardingobjectid_account_task@odata.bind": "accounts(6e060750-ab16-eb11-a812-000d3a6aa8dc)"
}

The value has x3 parts which can vary depending on the Regarding value of the original record.

1. Single-valued navigation property (e.g. regardingobjectid_account_task)
2. EntitySetName (e.g. accounts)
3. Row GUID (e.g. 6e060750-ab16-eb11-a812-000d3a6aa8dc)

To make the values dynamically populate from the Regarding value of the Email, replace the following:

1. part of the single-valued navigation property with the logical name of the Regarding (Type)
2. EntitySetName needs to be built using an expression (more details below)
3. Regarding (Value) can be used for GUID

EntitySetName is not returned as part of the "Get a record" or "List records" actions and it cannot be easily derived from the logical name. Not all of the EntitySetName are properly suffixed plural version of the logical name. One of the options is to write an expression and return the correct EntitySetName based on the logical name (e.g. If logical name = "account", use "accounts", else if logical name = "contact", use "contacts", else if logical name = "incident", use "incidents").

if
(
    equals
    (
        items('Apply_to_each_Email')?['_regardingobjectid_value@Microsoft.Dynamics.CRM.lookuplogicalname'],
        'account'
    ),
    'accounts',
    if
    (
        equals
        (
            items('Apply_to_each_Email')?['_regardingobjectid_value@Microsoft.Dynamics.CRM.lookuplogicalname'],
            'contact'
        ),
        'contacts',
        if
		(
			equals
			(
				items('Apply_to_each_Email')?['_regardingobjectid_value@Microsoft.Dynamics.CRM.lookuplogicalname'],
				'incident'
			),
			'incidents',
			'ERROR'
		)
    )
)

The expression will be a lot longer if you need to cater to more table types. This "Update a Record" step will dynamically populate the Regarding value without having to branch out with multiple condition steps. It is the same approach for the Customer lookup column but there will be only two types of tables (Account and Contact).

Create Row with Polymorphic Lookup Value

If you are proficient in JSON and expression, you can apply the same approach for the "Create a record" step by populating all column values to Record Item parameter with a JSON object. In that way, you can save one API call for not having to update the polymorphic lookup value with an additional step. You can also copy the JSON object parameters from the "Create a Record" step by clicking on the "Peek code" menu item, copy the parameters starting with "item/" and replace "item/" from the parameter name.

Here is how the final "Create a record" step with JSON parameter will look like.

Create Connection with Dynamic Connected To Lookup Value

The following flow is for Scenario 3 to clone the Connections related to the Case and link with the new Case. The Connected To value can be Account, Contact or Case.

Similar to the Regarding lookup scenario, the Connection can be created by setting the value of Connected To lookup dynamically using the following JSON.

{
  "record1id_@{items('Apply_to_each_Connection')?['_record1id_value@Microsoft.Dynamics.CRM.lookuplogicalname']}@odata.bind": "incidents(@{variables('New Case ID')})",
  "record2id_@{items('Apply_to_each_Connection')?['_record2id_value@Microsoft.Dynamics.CRM.lookuplogicalname']}@odata.bind": "@{if
(
    equals
    (
        items('Apply_to_each_Connection')?['_record2id_value@Microsoft.Dynamics.CRM.lookuplogicalname'],
        'account'
    ),
    'accounts',
    if
    (
        equals
        (
            items('Apply_to_each_Connection')?['_record2id_value@Microsoft.Dynamics.CRM.lookuplogicalname'],
            'contact'
        ),
        'contacts',
        if
		(
			equals
			(
				items('Apply_to_each_Connection')?['_record2id_value@Microsoft.Dynamics.CRM.lookuplogicalname'],
				'incident'
			),
			'incidents',
			'ERROR'
		)
    )
)}(@{items('Apply_to_each_Connection')?['_record2id_value']})",
  "record1roleid@odata.bind": "@{if(
    empty(items('Apply_to_each_Connection')?['_record1roleid_value']),
    '',
    concat(
        'connectionroles(',
        items('Apply_to_each_Connection')?['_record1roleid_value'],
        ')'
    )
)}",
  "record2roleid@odata.bind": "@{if(
    empty(items('Apply_to_each_Connection')?['_record2roleid_value']),
    '',
    concat(
        'connectionroles(',
        items('Apply_to_each_Connection')?['_record2roleid_value'],
        ')'
    )
)}"
}

🔗 Set Parameters of the Action Steps which does not Render Properly

Sometimes, the input parameters for certain action steps are not rendered properly in the flow designer. E.g. If the WinQuote action is selected in the Perform an unbound action step, it shows some columns from the Quote Close Activity instead of one single QuoteClose parameter as specified in the documentation. (probably because the flow designer is not able to render the QuoteClose object type properly)

In that scenario, the solution would be choosing a custom value for Action Name and enter trim('WinQuote') . Then, Action Parameters will become one single multiline textbox and populate it with a JSON object.

1. QuoteClose: JSON object with column values for Quote Close Activity 
2. Status: 4 (for Won statuscode)

Above screenshot is how the action will look like and the following is the sample JSON object.

{
  "QuoteClose": {
            "subject": "Quote Won (Won) - @{outputs('Get_a_record')?['body/quotenumber']}",
            "category": 1,
            "quoteid@odata.bind": "quotes(@{outputs('Get_a_record')?['body/quoteid']})",
            "quotenumber": "@{outputs('Get_a_record')?['body/quotenumber']}",
            "revision": @{outputs('Get_a_record')?['body/revisionnumber']},
            "statuscode": 2
        },
  "Status": 4
}

You can find out more details in my next post on how to perform bound/unbound action with EntityType parameters.

Summary

By setting the "Entity name" parameter with an expression value which is not recognisable by the flow designer, the input parameters of Create/Update actions of Dataverse/CDS (current environment) connector change from the column names to one single "Record Item" JSON object parameter. For "Record Item" JSON object, you can specify the entity name of the lookup value dynamically using an expression to populate the polymorphic lookups dynamically. There are also many other things that you can achieve with this generic Create/Update step with "Record Item" parameter such as

• Creating multiple types of table dynamically with one single step (e.g. create different types of activities conditionally) by providing the "Entity name" with expression
• Populating different number of columns dynamically by add/removeProperty of JSON object based on the condition

Stay tuned for my next post where I will explain how to dynamically set the array item value of the table (e.g. Activity Parties of Activity/Email/Task table) with JSON object array.