cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

SharePoint Get Items oData Filter

Hello everyone:

 

I have a flow like the below:

1) Get all items from a SPO list (list a)

2) Iterate through the returned items from "list a"

3) Perform a filter get items on "list b" comparing several columns between "list a" and "list b"

4) Flow in to a conditional

 

Quick flow overview:

 

Flow0.jpg

 

The title field contains values like: "New York's greatest stuff" in "list a" and I need to compare the title field between "list a" and "list b". The issue is when the "get item 2" runs, the filter fail with an error:

 

The expression "Title eq 'New York's Greatest Stuff'" is not valid. Creating query failed.
clientRequestId: 0f6281e8-93de-47ee-9cf5-234278d309ee
serviceRequestId: 0f6281e8-93de-47ee-9cf5-234278d309ee 

 

It seems the apostrophe in the title name is throwing it out. I had thought about doing a filter like:

 

equals(items('Apply_to_each')?['Title'],item()?['Title']) eq true (as shown below):

 

Flow1.jpg

 

But I get the following error:

 

Column 'True' does not exist. It may have been deleted by another user.
clientRequestId: a79b682b-b0e5-41d9-a7f2-76a0976da7ed
serviceRequestId: a79b682b-b0e5-41d9-a7f2-76a0976da7ed

 

Is there a way on the Get items 2 filter to do what I am trying to do? Wrap the column in an expression to perform an equals or strip out the apostrophe so I can compare strings? It seems whatever the function evaluates to, the filter assumes it is looking at column name.

 

Does that make sense?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Hello @MCraig78 ,

I think the Filter Query can process only filters in format <column name> <operator> '<value>'.

But if the problem are apostrophes in the <value>, you can preprocess the data by replacing ' with '' (one apostrophe with 2 apostrophes), two apostrophes are taken as an apostrophe inside the <value>, one apostrophe is closing the <value>.

 image.png

So instead of the <value> directly, you can use replace expression with a ton of apostrophes, this will search for ' in the <value> and replace them with '', which can be then used in the Filter Query.

replace(<value>,'''','''''')

image.png

 

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ ... and if you like my response, you might like also the tips and solutions on my Power Automate related blog ]

View solution in original post

1 REPLY 1
Super User II
Super User II

Hello @MCraig78 ,

I think the Filter Query can process only filters in format <column name> <operator> '<value>'.

But if the problem are apostrophes in the <value>, you can preprocess the data by replacing ' with '' (one apostrophe with 2 apostrophes), two apostrophes are taken as an apostrophe inside the <value>, one apostrophe is closing the <value>.

 image.png

So instead of the <value> directly, you can use replace expression with a ton of apostrophes, this will search for ' in the <value> and replace them with '', which can be then used in the Filter Query.

replace(<value>,'''','''''')

image.png

 

 



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ ... and if you like my response, you might like also the tips and solutions on my Power Automate related blog ]

View solution in original post

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (28,946)