Power automate Odata filter

[surgulati]

One of my Power Automate flow is connecting to Dataverse with tables

TableA

               TabACol1

               TabACol2

TableB

               TabBCol1

               TabBCol2

               TabbCol3

Flow Trigger

When a record is created or modified in the custom table A

One of the actions in this flow lists rows from table B. I want to limit the fetched rows from table B instead of applying the condition on each record in the loop. Looking to implement an Odata filter similar to following

(TabBCol1 eq ‘XYZ’ and TabACol1 eq ‘A’) or (TabBCol2 eq ‘ABC’ and TabACol1 eq ‘B’)

(nb: Based on the value of TabACol1 in the trigger record I want to check either column TabBCol1 or column TabBCol2 of table B

It seems the Power Automate ‘List All Rows’ action allows the Odata filer but that always looks for the column name of the table on the left side of “eq” operator.

Any suggestion to achieve this will be helpful as my Table B has a very large number of records and fetching all records and checking ion the loop may not be very efficient.

[StretchFredrik]

I only think that ODATA filters can filter based on the list itself, so you cannot incorporate listB in the odata filter for listA. You need to get all the items in that case and filter it afterwards via the filterarray step. 

[grantjenkins · Accepted solution]

Not sure if this is what you're looking for but think it will work.

I'm using the following two tables.

See full flow below. I'll go into each of the actions.

I'm assuming this is the trigger you are using for TableA.

List rows uses the following expression to build up the filter.

//You'll need to change the column names to match what you have in your tables

//'cra85_tabbcol2 eq ''NOMATCHES''' is used if no matches to ensure we don't get any results

if(
    equals(triggerOutputs()?['body/cra85_tabacol1'], 'A'),
    'cra85_tabbcol1 eq ''XYZ''',
    if(equals(triggerOutputs()?['body/cra85_tabacol1'], 'B'),
        'cra85_tabbcol2 eq ''ABC''',
        'cra85_tabbcol2 eq ''NOMATCHES'''
    )
)

I'm then using a Select to see the actual output from List rows.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.