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.
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!
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.
These are the two tables
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
Thank you so much! I was just looking if there is an alternative.
According to my knowledge this needs to be done in 2 steps as shown; It is not possible to achieve this in one step.
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.
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!
WarrenBelz
146,745
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,091
Most Valuable Professional