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

ampscriptmarketing-cloudmarketing-cloud-connect

I can successfully remove a single value from Salesforce using this function:

UpdateSingleSalesforceObject(
'Lead', @id, 
'fieldsToNull', 'FirstField'
)

Sadly I'm at a loss on how to expand this code snippet to remove values from multiple fields with a single function call and would appreciate any suggestions on how to achieve this without repeating the function.

Here's what I tried:

Attempt 1:

Assuming all fields after fieldsToNull will be removed:

UpdateSingleSalesforceObject(
'Lead', @id, 
'fieldsToNull', 'FirstField', 'AnotherField'
)

Error from a SSJS try…catch:

The function call includes an invalid number of repeating parameters. Repeating parameters must be included in full sets.

Comment:

Clearly the last parameter is being treated as an attempt to update another field which works when we add another parameter with the value after this, but that's not what I want to achieve.

Attempt 2:

Assuming all fields listed in the parameter after fieldsToNull will be removed:

UpdateSingleSalesforceObject(
'Lead', @id, 
'fieldsToNull', 'FirstField, AnotherField'
)

Error from a SSJS try…catch:

Call to update the salesforceobject Lead ID = 00Q(…) failed! Error in the application.\nAPI Fault: Salesforce.com Fault thrown.\n\tException Type:InvalidFieldFault\n\tException Code:INVALID_FIELD\n\tException Message:No such column 'Email, PendingEmail__c' on entity 'Lead'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name.

Comment:

Well, this is clearly not read as an array of strings. '["FirstField", "AnotherField"]' and "['FirstField', 'AnotherField']" also failed.

Attempt 3:

Repeating fieldsToNull pairs:

UpdateSingleSalesforceObject(
'Lead', @id, 
'fieldsToNull', 'FirstField, 
'fieldsToNull', 'AnotherField'
)

Error from a SSJS try…catch:

Call to update the salesforceobject Lead ID = 00Q(…) failed! Item has already been added. Key in dictionary: 'fieldsToNull' Key being added: 'fieldsToNull'

Comment:

Clearly 'fieldsToNull' can't be used twice as it fails to build the API payload with two identical keys

Best Answer

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

Related Topic