cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Cant get filter query functions working

Hi Gurus,

I'm trying to filter the results returned by Get Items. I have a list with 3 columns I want to check if they equal passed in values.

Without filters, all the records are returned successfully.

I tried some simple functions and they didn't work. The first column is "Title" and I tried the filter: length(Title) gt 1 and got an error 400.

I tried filter: Title eq 'Category 1' and that worked. I tried filter: startswith(Title, 'Cat') and that worked. filter: (startswith(Title, 'Cat') and startswith(group, 'Gro')) and that worked.

 

To account for real-world data, I wanted tolower() both the contents of the columns returned and the data I want to compare because I don't know what the capitalization of the incoming data and it doesn't matter. So I tried filter:
(tolower(Title) eq 'category 1' and tolower(group) eq 'group 1' and tolower(setting) eq 'setting label 1')

and that didn't work. error 400. 

 

The final issue I'm not entirely sure how to deal with is the column 'group' may legitimately be a blank string. So I'm not sure if I can pass a blank string to tolower(). Which is why I tried to use length(Title) gt 1 above, but that failed and I'm not sure why.

 

so I want to search list items for a match on 3 columns, Title + group + setting and possible values may be like:
('Category 2' + 'Group 2' + 'Setting Label 2') or ('Category 3' + '' + 'Setting Label 3').

 

Any advice is appreciated!

filter-query.pnglist-details.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Cant get filter query functions working

 

Hi @EmilioatUplyft ,

 

Please remove () and change AND to and.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
Highlighted
Dual Super User
Dual Super User

Re: Cant get filter query functions working

Hi @EmilioatUplyft 

 

just try

Title eq 'category 1' and group eq 'group 1' and setting eq 'setting label 1'

 

This should work for all cases

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

Highlighted
Community Support
Community Support

Re: Cant get filter query functions working

 

Hi @EmilioatUplyft ,

 

Note that the type of the field being referenced here needs to be Text or Number. We can't use Choice or Look up column.

In addition, it seems that the use of tolower() function is not supported here, please check this blog and get more details about Filter Query.

https://veenstra.me.uk/2018/11/12/microsoft-flow-filter-queries-in-sharepoint-get-items/

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Cant get filter query functions working

Thank you very much for providing some suggestions. I haven't figured out the correct syntax yet, but when I do I'll post again.

Highlighted
Community Support
Community Support

Re: Cant get filter query functions working

 

Hi @EmilioatUplyft ,

 

Are there any updates? If you need any assistance, please provide more details of the error.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Cant get filter query functions working

Thank you kindly for your assistance @v-bacao-msft !

 

I evolved the test case a bit, but still same use case.

1) I have a Sharepoint List that I need to search for matches on two columns (image_0)

2) the data I'm searching for comes as a single entry JSON object

3) I can get the strings out of the JSON object into variables (image_1)

4) I'm unable to write the Filter Query to search the columns. (image_2)

 

The column names are "category" and "setting"

Sharepoint List column structureSharepoint List column structureunable to write Filter Query to search Itemsunable to write Filter Query to search ItemsSuccessfully pulling string out of JSONSuccessfully pulling string out of JSON

 

{
"status": 400,
"message": "The expression \"(category eq Work Profile Settings)\" is not valid.\r\nclientRequestId: 253b152c-8f2f-4834-8d69-69b7abb91e81\r\nserviceRequestId: 253b152c-8f2f-4834-8d69-69b7abb91e81"
}

Error Code and contents of Filter QueryError Code and contents of Filter Query

Highlighted
Community Support
Community Support

Re: Cant get filter query functions working

 

Hi @EmilioatUplyft ,

 

Please remove () contained in Filter Query and add the single quotes outside the Inputs of the get Category string.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Cant get filter query functions working

Thank you so much @v-bacao-msft 

Unfortunately, same error. please see attached.error returnederror returnedremove brackets and single quotesremove brackets and single quotes

Highlighted
Community Support
Community Support

Re: Cant get filter query functions working

 

Hi @EmilioatUplyft ,

 

It is strange. What is the type of category column?

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Cant get filter query functions working

I'm pretty sure its a string, they're all string. I renamed the default first column, from Title to category. So I assumed it was a string. I don't see a way of verifying the variable type, or changing it.

 

Highlighted
Community Support
Community Support

Re: Cant get filter query functions working

 

Please try to change category to Title, please do another test. If you don't configure Filter Query, look at the specific field name displayed in the Body of Get item.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Cant get filter query functions working

@v-bacao-msft Thank you! You nailed it. Despite renaming the column, it retains its original name.

 

Now the final step I need to work is searching on the second column. I just tried:

1) Title eq 'variable' AND setting eq 'variable'

2) (Title eq 'variable' AND setting eq 'variable')

 

and both failed. Any suggestions there?

Thank you kindly,try to include a second column: failstry to include a second column: failschange reference to Title: workschange reference to Title: works

Highlighted
Community Support
Community Support

Re: Cant get filter query functions working

 

Hi @EmilioatUplyft ,

 

Please remove () and change AND to and.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helper II
Helper II

Re: Cant get filter query functions working

Thank you so much @v-bacao-msft !

Got it returning matching items. :))

I thought there were supposed to be brackets around multiple statements? When do you use round brackets?

Highlighted
Community Support
Community Support

Re: Cant get filter query functions working

 

Hi @EmilioatUplyft ,

 

Multiple conditions are generally used directly or/and, no need to use ().

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (7,019)