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

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

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

6 REPLIES 6
v-micsh-msft
Community Support
Community Support

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:

58.PNG

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

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?

 

file name with extension containing single apostrophe.png

 
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?

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
Helper II
Helper II

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?

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (91,205)