Skip to main content

Notifications

Community site session details

Community site session details

Session Id : RxAA+H59vN8bMgrw71c3D1
Power Automate - Building Flows
Answered

OData filter for lookup.

Like (1) ShareShare
ReportReport
Posted on 30 Jan 2022 08:00:07 by 21

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.table to filter.the lookup table.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.

  • NathanDG Profile Picture
    14 on 06 Feb 2024 at 14:45:05
    Re: OData filter for lookup.

    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" :

    NathanDG_0-1707230583682.png

    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!

     

  • Ian_UK Profile Picture
    2 on 23 Jan 2023 at 16:00:07
    Re: OData filter for lookup.

    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.

  • Casper Profile Picture
    41 on 06 Oct 2022 at 08:24:51
    Re: OData filter for lookup.

    These are the two tables

    Decsus_1-1665044646065.png

    Decsus_2-1665044669399.png

     

  • Mira Ghaly Profile Picture
    11,409 Super User 2025 Season 1 on 06 Oct 2022 at 07:53:45
    Re: OData filter for lookup.

    @Decsus 

     

    can you please share how your tables looks like?

     

  • Casper Profile Picture
    41 on 06 Oct 2022 at 07:24:24
    Re: OData filter for lookup.

    I have a similar problem.

     

    The error

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

     

    Decsus_0-1665040699220.png

    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 

    Decsus_1-1665040946071.png

    and the logical name, thus both

    _cj_employee_value_

    and

    _cj_Employee_value_

    but I keep getting this

     

    cj_gmaemployeesalaryid is the Unique identifier

     

  • RoshanShrestha Profile Picture
    21 on 31 Jan 2022 at 04:13:44
    Re: OData filter for lookup.

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

  • Verified answer
    Mira Ghaly Profile Picture
    11,409 Super User 2025 Season 1 on 31 Jan 2022 at 03:57:52
    Re: OData filter for lookup.

    @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 Profile Picture
    21 on 31 Jan 2022 at 03:00:20
    Re: OData filter for lookup.

    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 Profile Picture
    11,409 Super User 2025 Season 1 on 30 Jan 2022 at 10:06:22
    Re: OData filter for lookup.

    @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

    Mira_Ghaly_2-1643537025550.png

     

    Mira_Ghaly_0-1643536808874.png

     

    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:

     

    Mira_Ghaly_1-1643536917073.png

    Mira_Ghaly_3-1643537086842.png

    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!

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,745 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard