Skip to main content
Community site session details

Community site session details

Session Id : x1gXVsk714mrXsp9Jz46MU
Power Automate - Building Flows
Answered

ODATA filter query with an if condition

Like (0) ShareShare
ReportReport
Posted on 20 Oct 2020 18:43:23 by 42

Hi,

I have a unique requirement where I have to apply a filter on "Get rows" from SQL Server based on a condition.

There are two columns - column A and column B.

  • If both variables passed to the flow are empty, no filter is applied.
  • If either one of the variables is not empty, the filter must be applied on that column.
  • If both variables are not empty, the filter must be applied on both columns. 

One option I see is to use multiple Conditions and query database based on the result but that means all subsequent actions must be replicated in all branches of conditions. That will make maintenance of flow difficult.

 

Is it possible to apply an If statement in an ODATA filter query? 

I tried the following expression shown in the screenshot but it is not valid.2020-10-20_13-39-51.png

Thank you!

 

  • Matren Profile Picture
    454 Super User 2024 Season 1 on 12 Jul 2022 at 15:05:04
    Re: ODATA filter query with an if condition

    Solved the issue using a very similar method, using conditions to determine if the variables are empty, and if they are not, using Append to a String to add filter conditions to a String that is used to filter the SharePoint Get Items. 

  • Matren Profile Picture
    454 Super User 2024 Season 1 on 07 Jul 2022 at 19:59:37
    Re: ODATA filter query with an if condition

    Hello @Paulie78@MsKautha

     

    I came across a very similar issue working for my organization. I am trying to filter a sharepoint list by passing user-entered values from a form in a PowerApp to PowerAutomate. The method you propose seems feasible for a low number of columns. However, I have over 60 columns in my app which users can enter text into any one of them to filter the list. Filtering the data table in power apps works fine, it automatically accomodates and does not filter for any column that is blank -- only the ones where the user has entered a text value. However, if I try to pass the values for these columns to power automate, it doesn't like any value that is blank and the power automate flow will not run. 

     

    To illustrate the point, your solution has 4 cases (both columns empty, both columns have values, column one has a value or column 2 has a value). However, the number of cases increases exponentially for more columns. Using 5 columns results in 32 (2^5) cases. 60 columns results in 2^60 cases! I can't even visualize that big of a number. This would require a massive If statement or Switch function. I'm thinking there has got to be a better method.  

     

    P.S. I've also cc'd @RezaDorrani since I've frequented his posts and perhaps this issue would serve as a good candidate for a future video.

  • Paulie78 Profile Picture
    8,416 Moderator on 20 Oct 2020 at 22:32:17
    alsoRe: ODATA filter query with an if condition

    That is a cool approach, that's the great thing about Power Automate, so many ways to solve the same problem. Just needs a bit of imagination.

     

    Well done.

     

    By the way, you can escape single quotes, so you could have used your original approach. But I think your new way is easier to comprehend.

  • Verified answer
    MsKautha Profile Picture
    42 on 20 Oct 2020 at 20:38:21
    Re: ODATA filter query with an if condition

    Hi @Paulie78 , I ended up composing the query before get rows action because that's the only way I could include a single quotes in the filter query. Here's the series of actions I used to make it work.

    'Application' and 'Manager' are Column 1 and 2, respectively. 

    2020-10-20_15-33-32.png

     

  • Paulie78 Profile Picture
    8,416 Moderator on 20 Oct 2020 at 19:48:39
    Re: ODATA filter query with an if condition

    Hi @MsKautha 

     

    It is possible to apply an if statement inside an odata query, but I would be tempted to create the odata query on a step before just to make the flow more readable. But in any case, this code would do what you want, either in the odata query or outside of it.:

     

    if(and(equals(empty(variables('variable1')), true), equals(empty(variables('variable2')), true)), 'Both Empty', 
    	if(and(equals(empty(variables('variable1')), false), equals(empty(variables('variable2')), false)), 'Neither Empty', 
    		if(equals(empty(variables('variable1')), false), 'variable1 has data', 'variable2 has data'
    		)
    	)
    )

     

    You could put that directly into your odata query and edit it to put your query where it output 'Both Empty', 'Neither Empty', etc.

     

    But if it were me I would put it in a compose action on the step before with a comment. Then add a switch control after based on the output of the compose to put the odata query in a string variable, and then just add that string into your query from the dynamic content.

     

    I just built a flow as described above and it worked fine.

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

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2

Loading complete