Cant get filter query functions working

[EmilioatUplyft]

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!

[RezaDorrani]

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

[v-bacao-msft]

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,

[EmilioatUplyft]

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.

[v-bacao-msft]

Hi @EmilioatUplyft ,

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

Best Regards,

[EmilioatUplyft]

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 structureunable to write Filter Query to search ItemsSuccessfully 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 Query

[v-bacao-msft]

Hi @EmilioatUplyft ,

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

Best Regards,

[EmilioatUplyft]

Thank you so much @v-bacao-msft 

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

[v-bacao-msft]

Hi @EmilioatUplyft ,

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

Best Regards,

[EmilioatUplyft]

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.

[v-bacao-msft]

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,

[EmilioatUplyft]

@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: failschange reference to Title: works

[v-bacao-msft · Accepted solution]

Hi @EmilioatUplyft ,

Please remove () and change AND to and.

Best Regards,

[EmilioatUplyft]

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?

[v-bacao-msft]

Hi @EmilioatUplyft ,

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

Best Regards,