Dynamic content not available in expression for power automate

[dms117]

Hello,

I'm trying to create a flow to transfer data from SQL Server to Dataverse.  One of the issues is that some of the names have single quotes that will break the add record step.  To try to account for this I wish to use a replace on the data.  However I cannot get this to work.  Here is the flow overview:

When I try to set the variable in the Apply to each loop I can choose the First Name field from the SQL Records I wish to use:

When I use the Expression tab to start creating the replace() function, the selections are no longer available:

This wouldn't be a problem, but none of the constructs I try to enter by text manually in the expression field will resolve.  Every one gives an invalid expression error.  Can someone please help me on this?  I am so frustrated having to come here for these simple questions that should just work out of the box.  I spend more time searching for these trivial answers than I do writing code and generating solutions with this technology.  It is extremely disheartening.  

Thanks in advance to any wise souls that actually "get" this stuff...

[renatopc84_ch]

Hi @dms117 

try to insert the dynamic content "FirstName", then select it and copy it in notepad.

Then you should be able to insert it again in the replace(). You have to remove the additional characters that will be added @ { } when you do the copy paste 

[dms117]

Thanks @renatopc84_ch,

I tried that multiple times and it just didn't work at all.  I have no doubt there's a simple typo or something that's involved but the message of invalid expression doesn't really help narrow it down for me.  I'll continue poking away at it, character by character, until I figure it out.  For example, if I enter "replace()" in the expression field, that does not give me an error.  It's useless of course, but I'll keep adding to it until it fails, and maybe that will help clue me in.

[renatopc84_ch]

@dms117 

Can you please paste here the text of the FirstName?

Which text do you have to replace with the replace function?

To understand what is the final result that you want to achieve? 

[dms117]

Sure,

The text when the FirstName is available is items('Apply_to_each')?['FirstName']

I want to replace a single-quote in a name - like if someone is name O'Hara - with an escaped single quote (\') to ensure the Add row step in my flow can actually insert the data.

Thanks!

[renatopc84_ch]

Hi @dms117 

perfect, if I understood it right, it should work so:

replace(items('Apply_to_each')?['FirstName'],Char(39),"\")

Char(39) is the replacement for the single quote and Char(34) for the double quote

[dms117 · Accepted solution]

Hi @renatopc84_ch ,

I agree, that structure should work.  But when I copy and paste your string into the Expression tab to build it for the Set Variable step, it gives me an invalid expression message and will not save.

I believe the problem is the double quotes.  I created a variable called quote that has the value of a single quote and changed my expression to this:

replace(items('Apply_to_each')?['FirstName'],variables('quote'),'\''')

This was accepted and seems to work.  Thanks for taking the time to work with me on this, I really appreciate it!

Regards,
Dave