Using $expand Query in Power Automate for Retrieving Specific Fields from Related Entities

 Using $expand Query in Power Automate for Retrieving Specific Fields from Related Entities

Introduction

In Power Automate, fetching data from related entities in Microsoft Dataverse can sometimes be challenging, especially when you need specific fields from a lookup entity. This article introduces the $expand query option, which allows you to efficiently retrieve related entity data. We'll focus on a scenario where you need to fetch specific fields from a Contact entity that is related to a Program Application entity.

Understanding $expand Query

The $expand query option in Power Automate's "Get a row by ID" action lets you retrieve related entity data. It's particularly useful when dealing with lookup fields.

Fetching Specific Fields

To fetch specific fields from a related entity:

1. Use Schema Names: Always use the schema name of the lookup field in your $expand query.
2. Syntax for Multiple Fields: Specify multiple fields from the related entity by listing them in the $select clause of your expand query. For example, to retrieve contactid, firstname, and lastname from a Contact entity, your expand query would look like: foundry_Contact($select=contactid,firstname,lastname)
3. Optimizing Data Retrieval: To improve performance and limit the data payload, use the $select clause to fetch only necessary fields. This prevents the retrieval of all fields from the related entity, which can be less efficient.

Example Scenario

Consider a scenario where you have a Program Application entity with a lookup to a Contact entity. You need to retrieve the contactid and name from the related Contact entity:

• Use the expand query like this: foundry_Contact($select=contactid,firstname,lastname,emailaddress1)
• This query fetches only the specified fields from the related Contact record.

The names of fields of contact start with "Contact ...", like "Contact First Name", etc.

• 
  
  
  

Conclusion

The $expand query in Power Automate is a powerful tool for efficiently retrieving data from related entities. By using schema names and specifying the fields you need with the $select clause, you can optimize your data retrieval processes in your automated workflows.