How to Filter Rows by Lookup Columns in Power Automate

When working with Microsoft Dataverse in Power Automate, you often need to filter data based on relationships, such as those defined by lookup columns. Here’s a straightforward guide to correctly format your OData filter queries for lookup columns.

Proper Syntax for Lookup Column Filters

To filter rows by a lookup column in Power Automate’s "List rows" action, the filter expression must reference the GUID of the related entity. The correct syntax for this involves using the name of the lookup column followed by a specific suffix and the equality operator.

Syntax Pattern:

_lookupfieldname_value eq GUID

Here, _lookupfieldname is the logical name of the lookup column, and GUID is the unique identifier of the record in the related entity.

Example:

Suppose you have a lookup column named new_customerid in an "Invoices" entity that points to a "Customers" entity. To filter for invoices related to a specific customer with a GUID of 12345678-1234-1234-1234-123456789012, your OData filter expression would look like this:

new_customerid_value eq '12345678-1234-1234-1234-123456789012'

Another example:

Tips for Success

• Ensure the lookup column name used in your filter matches exactly with its logical name in Dataverse.
• Always use single quotes around the GUID in the filter expression.
• Verify the GUID to ensure it corresponds to a valid record in the related entity.

By following these guidelines, you can effectively filter rows based on lookup column values in Power Automate, simplifying data management and enhancing automation workflows.