cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EmilioatUplyft
Level: Powered On

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
Community Support Team
Community Support Team

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

 

Community Support Team
Community Support Team

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.
EmilioatUplyft
Level: Powered On

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 Team
Community Support Team

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.
EmilioatUplyft
Level: Powered On

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"

search_list_0.pngSharepoint List column structuresearch_list_2.pngunable to write Filter Query to search Itemssearch_list_1.pngSuccessfully 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"
}

search_list_3.pngError Code and contents of Filter Query

Community Support Team
Community Support Team

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.
EmilioatUplyft
Level: Powered On

Re: Cant get filter query functions working

Thank you so much @v-bacao-msft 

Unfortunately, same error. please see attached.error_1.pngerror returnededit_1.pngremove brackets and single quotes

Community Support Team
Community Support Team

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.
EmilioatUplyft
Level: Powered On

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.

 

Community Support Team
Community Support Team

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.
EmilioatUplyft
Level: Powered On

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,edit_3.pngtry to include a second column: failsedit_2.pngchange reference to Title: works

Community Support Team
Community Support Team

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

EmilioatUplyft
Level: Powered On

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?

Community Support Team
Community Support Team

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
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,779)