Set Dataverse Lookup with Alternate Key in Power Automate
Power Automate
Apr 2, 2024 12:04 AM

Set Dataverse Lookup with Alternate Key in Power Automate

by HubSite 365 about Sean Astrakhan (Untethered 365)

Solutions Architect, YouTuber, Team Lead

Citizen DeveloperPower AutomateMicrosoft DataverseLearning Selection

Optimize Dataverse Lookup in Power Automate with Alternate Keys for more intuitive automations.

Key insights

  • Identify or create an Alternate Key for the Dataverse table to use a more meaningful identifier like an email or custom ID.
  • Use the "List rows" action to find the specific record using the Alternate Key by specifying the column and its value in the Filter Query.
  • Parse the output of the "List rows" action to extract the GUID of the desired record using a "Compose" or "Control" action.
  • Use the extracted GUID to set a Lookup field in another Dataverse record, thus linking records through more intuitive identifiers.
  • Best Practices include implementing error handling and optimizing performance for Alternate Key columns to manage flow efficiency and reliability.

Exploring Power Automate and Dataverse Integration

Microsoft's Power Automate is a powerful tool enabling users to create automated workflows between their favorite apps and services to synchronize files, get notifications, collect data, and more. One of its advanced functionalities includes working with Microsoft Dataverse, a cloud-based storage space for securely storing and managing data used by business applications. Dataverse allows the utilization of a rich data scheme composed of tables, columns, and relationships; thereby providing a cohesive and integrative data service for applications.

Utilizing an Alternate Key in place of the standard Global Unique Identifier (GUID) to set Dataverse Lookup fields in Power Automate simplifies the automation process. This method significantly enhances readability and maintainability of automation flows by allowing records to be referenced by more meaningful identifiers, such as email addresses or custom IDs. The process involves identifying or creating an Alternate Key, using the "List rows" action to find records based on this key, extracting the GUID from the action's output, and then using this GUID to set Lookup fields in Dataverse.

This integration not only fosters operational efficiency by making data more accessible and workflows more intuitive but also underscores the flexibility of Power Automate in accommodating various operational needs. Furthermore, adhering to best practices like error handling and performance optimization ensures that automation flows remain effective and reliable. Hence, the convergence of Power Automate and Dataverse through the use of Alternate Keys represents a significant step towards streamlining business processes and enhancing data management strategies.

Power Automate allows for setting Dataverse Lookup using an Alternate Key, which can be a more meaningful identifier like an email or custom ID, instead of the standard Global Unique Identifier (GUID). This process is beneficial for referencing records in a more intuitive manner. To implement this, one must ensure their target Dataverse table has an Alternate Key configured.

To find the specific record using an Alternate Key, one should use the "List rows" action in Power Automate. This involves adding the action to your flow, selecting the appropriate Dataverse table, and specifying your Alternate Key in the Filter Query field. This step is crucial for filtering out the desired record based on a more recognizable identifier.

After listing the rows based on the Alternate Key, the next step involves parsing the output to extract the GUID. This can be done by adding a "Compose" or "Control" action, like "Condition", and using an expression to extract the GUID from the returned list. This GUID is necessary for the subsequent steps in the process.

With the GUID in hand, you can use it to set a Lookup field in another Dataverse record. This involves adding a dataverse action to create or update records and setting the Lookup field using the GUID extracted in the previous step. This step is crucial for connecting and referencing between different records in Dataverse.

Best practices include implementing error handling after the "List rows" action to manage cases where no records are found or multiple records are returned. Additionally, to optimize performance, especially in frequently triggered flows, it's recommended to ensure that Alternate Key columns are optimized to avoid delays or timeouts.

This method effectively utilizes the flexibility of Alternate Keys in Dataverse to make automation flows within Power Automate more understandable and easier to maintain. By relying on more intuitive identifiers, the complexity of automation tasks is significantly reduced, enhancing the efficiency of processes and workflows involving Dataverse records.

Power Automate and Dataverse Integration

Integrating Power Automate with Dataverse brings about a significant enhancement in automating workflows and processes. The ability to set Dataverse Lookups using Alternate Keys, as highlighted by Sean Astrakhan from Power Automate, represents a breakthrough in making automation tasks less complex and more intuitive. This functionality opens up new possibilities for using meaningful identifiers such as emails or custom IDs instead of relying solely on GUIDs, simplifying the referencing of records in automation flows.

Moreover, the step-by-step guide provided helps users navigate through the necessary actions to implement this feature effectively. From ensuring the presence of an Alternate Key in the target Dataverse table to using this key in the List rows action and finally setting up the Lookup field with the extracted GUID, the process is streamlined and user-friendly. It is evident that with proper usage of similar tools, the automation of tasks becomes more efficient and easily manageable.

Additionally, the emphasis on best practices such as error handling and performance optimization further underscores the importance of a well-thought-out approach to automation. By adopting these tips, users can avoid common pitfalls and ensure that their workflows run smoothly and efficiently.

In conclusion, the integration of Power Automate with Dataverse using Alternate Keys not only simplifies workflow automation but also encourages a more structured and intuitive approach to handling records. This innovation is set to revolutionize how tasks are automated, making processes more accessible to users and contributing significantly to the overall effectiveness and efficiency of automated systems.


People also ask

How do you update the lookup field in Power automate Dataverse?

Regarding the process of updating the lookup field in Power Automate for Dataverse, I would refer to a specific scenario involving my favorite sport, cricket, to illustrate how we proceed with the update operation in practice.

How do you create an alternate key in Dataverse table?

The procedure to create an alternate key in a Dataverse table can be succinctly summarized as initiating the creation of the alternate key itself.

What is the alternate key relationship in Dataverse?

In Dataverse, alternate keys offer the capability to designate a column within a Dataverse table to act as a unique identifier or a unique combination of columns, which mirrors the functionality of a unique identifier utilized by an external data store. This facilitates the use of an alternate key for uniquely identifying a row in Dataverse as opposed to relying on the primary key.

How do I create a lookup column in Dataverse table?

When it comes to adding a lookup column in a Dataverse table, I would guide through the process in subsequent discussions. It's crucial to navigate to the appropriate section in the Dataverse where you can successfully initiate the creation of a lookup column.


Power Automate, Dataverse Lookup, Alternate Key, Use another column, GUID, Set Lookup Field, Dataverse Alternate Key, Custom Column Lookup