SharePoint Get Items oData Filter

[MCraig78]

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:

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

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!

[tom_riha · Accepted solution]

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

 

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>,'''','''''')