OData filter for lookup.

(1) Share

Report

Posted on by 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.

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Mira Ghaly 11,413 Moderator on at

Like (3)

Report
@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!

Was this reply helpful? Yes No
RoshanShrestha 21 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Mira Ghaly 11,413 Moderator on at

Like (2)

Report

@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.

Was this reply helpful? Yes No
RoshanShrestha 21 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Casper 41 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Mira Ghaly 11,413 Moderator on at

Like (0)

Report

@Decsus

can you please share how your tables looks like?

Was this reply helpful? Yes No
Casper 41 on at

Like (0)

Report

These are the two tables

Was this reply helpful? Yes No
Ian_UK 2 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
NathanDG 14 on at

Like (0)

Report

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!

Was this reply helpful? Yes No