[SalesForce] updating field to null with UpdateSingleSalesforceObject AMPscript function

I need to update the Email field in the Salesforce Contact Object to null.

If I use the following code, the email field in the Contact Object is updated to sam@sample.com:

%%[
var @personid 
set @personid = Lookup('Ent.All Services','PersonID','Id', _subscriberkey)

UpdateSingleSalesforceObject('Contact',@personid,'Email','sam@sample.com')
]%%

However, I can't figure out how to update this to a null value. I have tried:

UpdateSingleSalesforceObject('Contact',@personid,'Email',null)

And:

UpdateSingleSalesforceObject('Contact',@personid,'Email','')

But this does not update the email address.

I understand that the UpdateSingleSalesforceObject() function is a wrapper for the Salesforce API and according to this documentation you need to include a fieldsToNull array, but this is not an option in the AMPscript function.

Does anyone know a solution to this?

Best Answer

I understand your dilemma. I have a solution, albeit a total hack.

Create a workflow in Salesforce ORG to set email to null when a specific value is assigned.
Modify AMPScript code to assign a dummy value in scenarios where email should be null.

Screenshot of custom workflow in Sales Cloud:

AMPscript example:

%%[  
 UpdateSingleSalesforceObject('Contact',@personid,'Email','delete@email.co')
]%%

Related Solutions

[SalesForce] IsNull AMPscript function

The IsNull AMPscript function only works with Data Extension fields, where if the field does not contain a value, then the function will evaluate to True.

For example, In the example below, there is no value for the "First Name" Data Extension field.

%%[
var @field
set @field = Lookup("Test DE","First Name","ID","ABC123")
]%%

%%=IsNull(@field)=%%

The function will return True if the field, irrespective of its type (text, number, boolean, date, etc), does not contain a value.

Removing values from multiple fields with a single UpdateSingleSalesforceObject() function call

You can combine multiple fields into one call if you conform to their respective data types' quirks:

IF EMPTY(@feld) THEN
    SET @empty = " "
ENDIF
UpdateSingleSalesforceObject("Contact", @contactId,
"myfield1", @empty, 
"myfield2", @empty)
]%%

the single space turns into an an empty text field. You can do multiples in one update call here.

Doesn't work for picklists, you'll have to use an "undefined" value here.

Doesn't work for dates, suggestion set it to “01/01/1900" (and potentially catch that with a flow).

Doesn't work for numbers, suggestion, use "-1" (and potentially catch that with a flow).

Best Answer

Related Solutions

[SalesForce] IsNull AMPscript function

Removing values from multiple fields with a single UpdateSingleSalesforceObject() function call

Related Topic