Apostrophe issue in filter query

(0) Share

Report

Posted on by Prathameshpatka

319

Hi ,

I have a small issue with an apostrophe in email address which causes failure to run the filter query set on Get items.

can anyone suggest the best way to get this working ?

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report

Hi @Prathameshpatka,

Try escaping it with another apostrophe character.

To give you an example

Title eq 'Testing 'Test' - won't work

Title eq 'Testing ''Test' - should work

Was this reply helpful? Yes No
Prathameshpatka 319 on at

Like (0)

Report

@Expiscornovus

Can I use a replace expression to replace the single apostrophe with a double apostrophe as the email address is as a variable in the filter query as below . If so how can I get that done ?

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (3)

Report
Hi @Prathameshpatka,

Yes, that would be an option. You also have to escape the apostrophe characters in that expression as well, so you end up with something like this 😁

replace(variables('varA1Email'), '''', '''''')

Was this reply helpful? Yes No
Prathameshpatka 319 on at

Like (0)

Report

Hi @Expiscornovus

I am trying to do the below by initializing two variables as below :

can I then use a replace expression like this :
replace(variables('varA1Email'),variables('varApos'), variables('varApos2')) in the compose action to then set the variable varA1Emai to it's output or as you said to escape apostrophe characters I need to tweak the expression ??

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,189 Most Valuable Professional on at

Like (1)

Report

Hi @Prathameshpatka,

The approach with three variables should also work.

And you could use the expression also directly in the filter query field

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (2)

Report
I think it would be cleaner not to use any variables, but instead use something like this:

replace(variables('varA1Email'), decodeUriComponent('%27'), decodeUriComponent('%27%27'))

Which will replace a single apostrophe with two without the need for creating variables.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

1 people found this reply helpful.

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (1)

Report
Yes, that was my first response too. Only I used a lot of quote characters 😁

replace(variables('varA1Email'), '''', '''''')

That decodeUriComponent function is useful!

Was this reply helpful? Yes No
ms-mac Microsoft Employee on at

Like (0)

Report
I tried the above on a List Rows function for Dataverse. It came back empty with a 200 result.
I succeeded by replacing the apostrophe with a blank (eg, removing the apostrophe); then I got the function to list actual records instead of a 200.
replace(variables('varApostropheText'),decodeUriComponent('%27'), '')

Was this reply helpful? Yes No
ms-mac Microsoft Employee on at

Like (0)

Report

UPDATE: It worked when the record I was after did not include an apostrophe. It fails when the record does contain one. So, is there a way to get this to work for any variable? Whether or not it has an apostrophe?

Was this reply helpful? Yes No
ms-mac Microsoft Employee on at

Like (0)

Report

Disregard. Coder error (sigh)

Was this reply helpful? Yes No