1

I have a very basic retrieval of a Salesforce record based on email address in AMPScript that works 99.9% of the time

SET @retrieveContact = RetrieveSalesforceObjects('Contact', 'Id,Email', 'Email', '=', @email)

However, today I discovered that if someone inputs an email address that has an apostrophe, RetrieveSalesforceObjects will crash. This is because AMPScript puts single quote rather than double quotes around variable strings. Here is the error that returns when someone submits an email address with an apostrophe (this is for a form). I am modifying the email address for her privacy.

{"message":"Call to retrieve records for salesforceobject Contact failed! Error in the application.
API Fault: Salesforce.com Fault thrown.
Exception Type:MalformedQueryFault
Exception Code:MALFORMED_QUERY
Exception Message:
Contact WHERE Email = 'firstname.o'[email protected]'
^
ERROR at Row:1:Column:53
unexpected token: bryant
At row:1 and column: 53","description":"ExactTarget.OMM.FunctionExecutionException: Call to retrieve records for salesforceobject Contact failed! Error in the application.
API Fault: Salesforce.com Fault thrown.
Exception Type:MalformedQueryFault
Exception Code:MALFORMED_QUERY
Exception Message:
Contact WHERE Email = 'firstname.o'[email protected]'
^
ERROR at Row:1:Column:53
unexpected token: bryant
At row:1 and column: 53
Error Code: RETRIEVESFOJBECTS_FUNC_ERROR
- from Jint"}

I have been trying to come up with a resolution to this issue but my only idea so far has been to just force email validation on all forms that leverage AMPScript to not accept email addresses with apostrophes. However, I would ideally like to find a workaround that allows email addresses with apostrophes.

Improve this question
2
  • Is this even a valid email address? If not you should change it in your CRM and make sure you will not have emails like this in first place
    – Johannes Schapdick
    Commented Sep 6, 2019 at 12:32
  • Yes, apparently apostrophes in email address are a real thing, especially in Ireland. I can change it in my CRM, but that will not stop people from submitting their email address on the form. I can set up validation to prevent email addresses with apostrophes, but then I am blocking legitimate email addresses.
    – Lesley
    Commented Sep 6, 2019 at 20:32

2 Answers 2

Reset to default
2

You should be able to escape the single quote with a \. Try this:

SET @email = "firstname.o'[email protected]"
IF IndexOf(@email,"'")>0 THEN
  SET @email = Concat(Substring(@email,1,Subtract(IndexOf(@email,"'"),1)),
                      "\",
                      Substring(@email,IndexOf(@email,"'"),Length(@email))
                      )
ENDIF

SET @retrieveContact = RetrieveSalesforceObjects('Contact', 'Id,Email', 'Email', '=', @email)

Above would probably have issues if there was more than one single quote.

Improve this answer
1

This is a known issue: 'RetrieveSalesforceObjects() & Salesforce Activities within Journey Builder does not escape apostrophes'

Workaround Use the Replace() AMPScript function to remove single quotes.

Don't use single quotes in attribute values.

Use Synchronized Sources and the fields from these Synchronized Data Extensions in AMPScript rather than using AMPScript to retrieve the values from Sales Cloud.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .