ODATA Filter Query with contains operator

(1) Share

Report

Posted on by sachinmishra

Having some issues with the ODATA query when using "List Rows Present in the Table" to build the flow.

I have an excel file that contains a few columns and one of the columns is "Username".

I am using the "List Rows Present in the Table" to lookup this row from the excel file based on an input that comes via another source.

I need to check this using the "contains" operator but not able to figure out the correct syntax to apply in the Filter Query field.

Tried a number of different ways but they all end up in errors.

How can I get this resolved?

The error I get when running the flow is the following:
Syntax error at position 17 in 'Username contains juarestrepo'.
inner exception: Syntax error at position 17 in 'Username contains juarestrepo'.
clientRequestId: 4de78408-ae00-49ed-81aa-d70bc5e3ab38

Attached is also the screenshot of the "Get List Rows Present" Screen Shot 2022-06-30 at 11.14.30 am.png

body":{"status":400,"message":"Syntax error at position 17 in 'Username contains juarestrepo'.\r\n inner exception: Syntax error at position 17 in 'Username contains juarestrepo'.\r\nclientRequestId: 4de78408-ae00-49ed-81aa-d70bc5e3ab38","error":{"message":"Syntax error at position 17 in 'Username contains juarestrepo'.\r\n inner exception: Syntax error at position 17 in 'Username contains juarestrepo'."},"source":"excelonline-wus.azconn-wus.p.azurewebsites.net"}

Categories:

General topics

I have the same question (0)

All responses (12)

Answers (1)

Shuvam-rpa 891 Super User 2024 Season 1 on at

Like (0)

Report

@sachinmishra did you try to use single quotes , like Username contains 'Output' ??

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
sachinmishra 4 on at

Like (0)

Report
Yes @Shuvam-rpa I tried Username contains 'Output....' and I get similar syntax error,
Here is the exact error with single quotes:
Syntax error at position 17 in 'Username contains 'jennwang''. inner exception: Syntax error at position 17 in 'Username contains 'jennwang''. clientRequestId: fb9135de-49da-4aa4-8134-6cdbd4829961

Was this reply helpful? Yes No
Shuvam-rpa 891 Super User 2024 Season 1 on at

Like (1)

Report

@sachinmishra I would recommend you to leave odata filter blank in list rows present in a table action and then after it create filter array action where you can filter the record using contains . please check below screenshot as reference

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
Shuvam-rpa 891 Super User 2024 Season 1 on at

Like (0)

Report

@sachinmishra If the above reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
Sachin_M 4 on at

Like (0)

Report

@Shuvam-rpa I will try your suggestion and that might work, will post the result here.
I am concerned about the size of data loaded as overtime this file will grow significantly in size so loading all rows and then filtering will this lead to any issue due to number of records?

Was this reply helpful? Yes No
kgreen8790 189 on at

Like (10)

Report

Here's the proper format for contains in Odata filter query: contains(Column_A, 'text to filter')
See screenshot for additional info.

Be sure to mark my response as "solution" if this has solved your issue.

Was this reply helpful? Yes No
JAWL 393 on at

Like (0)

Report

@kgreen8790 , appreciate your helpful answer. What if I want "not contains" operator?

not contains(Description,'User_Input') ?

Was this reply helpful? Yes No
Verified answer

JAWL 393 on at

Like (2)

Report

@kgreen8790 , it's okie, I just found the solution by trial and error. We can use

not contains(Description,'User_Input')

Was this reply helpful? Yes No
kgreen8790 189 on at

Like (0)

Report

Glad I could help

Was this reply helpful? Yes No
carlilelance 351 on at

Like (0)

Report

This should be the accepted solution.

Was this reply helpful? Yes No