Salesforce wsc: upsert null fields

The salesforce upsert() call  is brilliant for interfacing legacy systems with Salesforce, especially if the data you are going to replicate into already contains some sort of unique identification.

• It will create records that does not exist, and update records that does exist, given the unique id.
• It allows updating up to 200 records in one batch returning create-/update status for each individual item.
• It’s fast – can easily push more than 100,000 records/hour, depending on object type.

Basically the upsert() operation is nice and easy to use; it will happily set or change almost any field in your Salesforce based CRM solution.

But, how do you null (or “blank”) previously set fields?

It may come as a surprise, but the upsert() operation does not change fields to null or blank, even if blanks, nulls or empty objects are specified. And I don’t think the api documentation is entirely clear about this, or how to actually do it.

The api documentation for the similar update() operation () has a section specifically for Resetting Values to null.

According to this, in order to reset values to null, one must add the name of the field to reset to null to the list of names in the fieldsToNull array in the sObject.

A similar section is not on the documentation for the upsert() operation, but both update() and upsert() are mentioned in the documentation for the fieldsToNull field…

And the good news is that this mechanism does actually work with the upsert() operation.

Assume you have added 2 custom fields to the Salesforce Account object:

• Account_Manager__c: a User object referencing the Account Manager for the Account.
• Customer_Code__c: the unique identification of the Customer in the legacy system, used as External ID field on the upsert() operation.

The following code snippet creates or updates an Account in Salesforce with reference to the user with username=”test-test@testmail.com” as Account Manager:

ConnectorConfig config = new ConnectorConfig();
config.setAuthEndpoint("https://login.salesforce.com/services/Soap/c/26.0");
// Insert username here
config.setUsername("jesperudby@testmail.com");
// Insert password + key here
config.setPassword("password" + "QKlNGRgHdxE2Qx5KLdHTIP9Q" );
 
config.setPrettyPrintXml(true);
config.setTraceMessage(true);
 
EnterpriseConnection con = new EnterpriseConnection(config);
 
Account acc = new Account();
acc.setCustomer_Code__c("00001");
acc.setName("Test Account 00001");
 
// Set Account Manager
User user = new User();
user.setUsername("test-test@testmail.com");
acc.setAccount_Manager__r(user);
 
con.upsert("Customer_Code__c", new Account[] { acc });

Now, in order to remove the reference to the Account Manager entirely, the following piece of code does just that:

ConnectorConfig config = new ConnectorConfig();
config.setAuthEndpoint("https://login.salesforce.com/services/Soap/c/26.0");
// Insert username here
config.setUsername("jesperudby@testmail.com");
// Insert password + key here
config.setPassword("password" + "QKlNGRgHdxE2Qx5KLdHTIP9Q" );
 
config.setPrettyPrintXml(true);
config.setTraceMessage(true);
 
EnterpriseConnection con = new EnterpriseConnection(config);
 
Account acc = new Account();
acc.setCustomer_Code__c("00001");
acc.setName("Test Account 00001 no mgr");
 
acc.setFieldsToNull(new String[]{"Account_Manager__c"});
 
con.upsert("Customer_Code__c", new Account[] { acc });

Specifying the Account_Manager__c field name in the fieldsToNull array effectively instructs the upsert() operation to reset the Account Manager field to null.

Remember to test your code – if you mistype a field name the upsert() call will not fail on individual records where the wrong attribute name is specified. It will throw an InvalidFieldFault failing the entire batch…