How Do I Set a Lookup in the CDS/Dataverse Connector? You Don’t!

Last year, Microsoft released a new Common Data Service Connector aptly suffixed with ‘(Current Environment)’. Though the connector gave us some cool new features, many found that the connector had fundamentally changed an important feature of Dataverse/CDS: Setting lookups. Setting a lookup in Power Automate was simple: Have a GUID? Put the GUID in the lookup column. Now, the/entityset(key) formula is our new burden (see image below for a variant).

Technically, the correct way to set a lookup.

Well, what’s wrong with the new syntax?

This shifting syntax causes another problem yet another problem:  The parentheses must contain a value. So, in order to effectively use this method you will need to write logic that populates a null in that column or create a parallel action where you are not populating that column. If you don’t do this, your action will fail. This method creates unnecessary layers of logic and branching that make for a messier Power Automate Flow.

Here’s the solution:

The release of the ‘(Current Environment)’ edition of the CDS connector gave us the effective new action called Relate rows. So, instead of creating conditions or switches, you will use one of these.

The better way to set a lookup.

This action has four selectable attributes:

• Table name – The table that the lookup field points to. For primary contact, this would be the Contacts table.
• Row ID – The primary key/GUID of the row with the lookup column.
• Relationship – The system or custom generated connection between two tables.
• Relate with – The Odata Id of the Child record found in the Power Automate dynamic content pane. For example, the create row, update row, and retrieve row actions will provide this value.

Now, using this action means you are signing up for a couple of things. First, you have to add an action for every lookup you want to populate. And second, you have to populate 4 attributes for every 1 lookup.

In my opinion, this action creates a more approachable Power Automate design in return for some of your Flow’s vertical landscape and a few more clicks.

If you aren’t convinced, be on the lookout for part 2 of this blog. In which, we will walk through some best practices around the relate rows action that show much simpler it can be.

Taylor Becktold

Taylor Becktold is a senior associate on the Application Development and Integration team at RSM. Taylor is certified in Dynamics 365 Sales (formerly CRM) and specializes in Microsoft Power Automate, Power Apps, and the KingswaySoft + SSIS integration toolset.