Using a value with a single quote as an Odata filter query element

[BenGrady]

Hello,

I am attempting to Get Items from a SharePoint list where the value of a particular column is equal to the Department of the user who created the item that initiated the Flow. The problem is that many of our departments include a single quote in them (e.g. Men's Guest Services). When the Odata query hits that single quote it throws an error because single quotes need to be escaped with a second single quote. Unfortunately there is currently no way to do string replacement in Flow to the best of my knowledge. 

Any ideas on what to do (short of changing all our department names)?

Thanks,

Ben

[v-micsh-msft]

Hi @BenGrady,

Single quote in OData Query need to present in pairs.

Which means if your string contains one single quote, we need to "double" it.

For example, the string we would like to use under Filter Query with the field Department is:

Men's Guest Services

Then the formula should be:

Department eq 'Men''s Guest Services'

Check the following screenshots:

Regards,

Michael

[BenGrady]

Hi @v-micsh-msft,

Thank you for your reponse!

The issue I am running into is that the Department field is a dynamic value. That means that I would need to have a string manipulation procedure in order to insert that extra single quote. Do you know of a string manipulation for dynamic values in Flow?

Thanks,

Ben

[automaton]

@BenGrady  assuming you got to the bottom of this but if not you should be able to use the replace() function for this. 

To replace a single quote in an dynamic OData Query String value where the example field is Department.

replace(items('Apply_to_each')?['Department']?['Value'],'''','''''')

It looks a bit funky but the '''' in the second part of the replace is required to escape the ' in the input string to the replace function. So essentially this is matching on '. And then similarly, the '''''' is replacing the single quote with two single quotes. It should work as you expect.

Hope this helps for the future.

[niclasdahl]

I have a similar problem. I have a flow that uses the 'Get Files' action with a filter.  purpose is to check if a file already exist in a folder, if it does it should fetch the properties of that file.

Problem is a that i use the dynamic content 'file name with extension' to filter, and if the file name contains a single apostrophe, it will cause the filter to fail, and say that the expression isnät valid.... since i can't go about changing the name of all files that have a single apostrophe in them, is there another workaround for this?

The above will result in this:

"message": "The expression \"Title eq 'XXX0000-0-xxxx'x xx xxxx.pdf'\" is not valid.\r\nclientRequestId:...

Is it possible to circumvent this issue by putting the dynamic content within brackets or using quotations instead of single apostrophes?

[automaton]

Hi @niclasdahl ,

As mentioned above, try using an expression like this to escape the quotes in the merged filename, instead of a direct merge of the filename:

replace(triggerBody()?['{FilenameWithExtension}'],'''','''''')

 Hope this helps.

[Symira]

I need to query records from CDs entity that have an quote sign "'" in a field. In Sql I could easily do a LIKE filter. EG.
Select * from my table where filter field LIKE ' %'' %'.

How to do that in a Flow?

[SamuelWhite]

Sorry to reply to a really old thread, but I think this is the same problem as originally posted.   I'm struggling with the syntax of the OData Query String.

Two SharePoint lists.

Technology Coordinators

Dev IT RFE Tracking

Each of these have a choice field for a field name Department.

What I'm trying to do:

When an item changes in Coordinators, edit all the items in Tracking List where the Department field in Coordinators equals the Department field in Tracking List.

This worked great for all the Department values, except for Clerk's Office (because of the single quote). That led me to this post and trying to modify the ODATA Query String. 

I've tried a number of variations, but they all come back as invalid.  Example:

Department eq replace(items('Apply_to_each')?['Department']?['Value'],'''','''''')

Error: Action 'Get_items' failed

The expression "Department eq replace(items('Apply_to_each')?['Department']?['Value'],'''','''''')" is not valid. Creating query failed.

Any help is appreciated.

Thanks

[BenGrady]

Hi Samuel, what I ended up doing was setting a variable equal to the author's department and then using a switch statement to make decisions about what to do based on that department value. Really you could use two variables in this case, set one variable equal to the Department field in Coordinators and another equal to the Department field in the tracking list then compare the two.

Here I'm just using a coalesce statement so that if they don't have a department it will be set to a certain value.

[SamuelWhite]

I was able to get this working finally.  At first I tried the intermediate step of using a variable and that did work, but I reduced it down to this.

I had to put the following expression into the Odata filter field with the Expression editor.

replace(triggerOutputs()?['body/Department/Value'],'''','''''')

It looks like this in Get items

My previous syntax was way off, apparently or maybe this is just another way to express the same thing.  At least it is working now...