OData filter for lookup.

[RoshanShrestha]

Hello,

Is it possible to filter the rows in power automate with lookup values? The value must be from the lookup table.

Following are the screenshots of the two tables from the dataverse.

table to filter.the lookup table.

From the lookup column of the 'rsp_table', I want to filter the orders with the specific table which is represented by rsp_count in the rsp_table.

[Mira_Ghaly]

@RoshanShrestha 

First you need to get the lookup table value filtered by count using List Rows and ODATA Filter similar to the below

rsp_count eq 'Your Value'

Then use the first value from the returned array to get the value from the table to filter

Steps similar to below:

1. Get the value from the lookup table based on count

2. Use the first returned item from your list ..it should return only 1 record I assume and use it to filter the Table to filter as below:

Right Hand Side Expression:

first(outputs('List_rows')?['body/value'])['cr2d2_lookuptableid']

Please note how to use the lookup column name in the ODATA Filter it should be in the format _NAME_Value

Please reach out if this is unclear or you need more help with that!

[RoshanShrestha]

This solution took a extra process or step. What I was looking for is filtering the required table with lookup table with other column value at single step if it's possible.

Example: filter list rows with given query as _cdr2lookupcolumn_othercolumn.

Here, cdr2lookupcolumn is the lookup column for the required table and other column is the column of that lookup table.

[Mira_Ghaly · Accepted solution]

@RoshanShrestha 

According to my knowledge this needs to be done in 2 steps as shown; It is not possible to achieve this in one step.

[RoshanShrestha]

Thank you so much! I was just looking if there is an alternative.

[Decsus]

I have a similar problem.

The error

Could not find a property named '_cj_employee_value_' on type 'Microsoft.Dynamics.CRM.cj_gmaemployeesalary'.

I get the ID from the trigger (when row is modified) from Tabel Building Employee

I then want to filter for all the rows in Employee Salary that have that id, cj_Employee is the lookup table.

I have used both this name 

and the logical name, thus both

_cj_employee_value_

and

_cj_Employee_value_

but I keep getting this

cj_gmaemployeesalaryid is the Unique identifier

[Mira_Ghaly]

@Decsus 

can you please share how your tables looks like?

[Decsus]

These are the two tables

[Ian_UK]

I do this by using an XML query.

Then you need to populate the GUID and Name from the 2nd table.

This means you first need a 'get a Row' to the 2nd table to get the Name of the lookup value.

The XML query can be got very easily from a view in the user interface with a suitable filter applied as an example. Then replace the example filter ID and Name with the ones you need.

[NathanDG]

Hi @Ian_UK ,

Working with an OData Filter will work in one-step as you initially asked. This is what is needed to be done :

1. Locate your lookup field in a request body (I used a List Rows action to analyse the body)

My LookUp column is "ActivitiesData" :

What we want is the "_ca2div_activitiesdata_value"

2. Change your OData filter

If the LookUp field is empty, that value will be null, so we can do an easy filter for this like this :

_ca2div_activitiesdata_value eq null

to only retrieve the rows that have an empty lookup field 🙂

If it worked for you please mark this as the solution so it can help others.

Have a great day!