MuleSoft Salesforce Connector Release 7.2.0.201608251058 and fieldsToNull

Hot off the press, MuleSoft just released a new Salesforce connector “7.2.0.201608251058”. If you read carefully, that’s about 5 days ago. Normally, I’m not in a hurry to update new connector release. However, this time it may be worthwhile to get the new version if you need to use a particular feature: setting SFDC fields to null.

It may appear to be a trivial thing to set a field to null in SFDC. But if you check SFDC API (java) you will understand it’s quite tricky if you want to nullify a field. Essentially, for each field you want to nullify, you need to set the actual value to null, and you need to add that field name to “fieldsToNull” parameter as a java List only if the field actually has a null vlaue. I know it sounds like a tongue twister.

Let’s use an example, say I have two fields that may have null values: ‘ACTV_DT__c’, and ‘INAC_DT__c’. I have the following potential input scenarios:

1. {ACTV_DT__c: null, INAC_DT__c: null}
2. {ACTV_DT__c: null, INAC_DT__c: “2016-08-31”, fieldsToNull: [INAC_DT__c]}
3. {ACTV_DT__c: null, INAC_DT__c: “2016-08-31”, fieldsToNull: [ACTV_DT__c]

Example #1 would not nullify anything, even though both fields have null values, because the field names are not in the “fieldsToNull” list, so the null values will be ignored by SFDC.

Example #2, this is a tricky one, SFDC call will actually blow up. It throws exception like “A duplicate value was specified for field”, because “fieldsToNull” claims “INAC_DT__c” is null, but the actual field has a value. In other words, the null values and “fieldsToNull” need to agree with each other in order for the call to work. I don’t like this behavior. It makes life much harder when build the calling parameters.

Example #3, this one will set ACTV_DT__c to null.

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

Now, let’s check how MuleSoft Salesforce connector handles the null value fields. If you have an old connector, you are out of luck, it simply doesn’t support “fieldsToNull” list. If you have version “7.1.2.201606031824”, you can use “fieldsToNull” list, but you need to build the list on your own. For me, that turned into a project on its own. I’ll show what I had to do to make it work.

Now, if you are very lucky and you have updated to the latest version “7.2.0.201608251058”, this 5-day old new release added a new feature that automatically handles everything with a single checkbox “Can clear fields by updating filed value to null”. that’s it. 

You don’t have to worry about building a dynamic list of “fieldsToNull” that must be consistent with the actual null value fields. The details are all handled by the connector behind the scene.

Finally, i’ll show you how I did it with the previous version of SFDC connector. It doesn’t have a whole lot of practical values anymore due to the release of the new connector. But it does show a few advanced features of datasweave, such as “lookup” function.

Before the SFDC connector call, you normally use dataweave to map the fields and values, something like below.

%dw 1.0
%output application/java
---
   ...
   foo: 'this is foo',
  (fieldsToNull: lookup("buildNullFields", {'ACTV_DT__c': payload.ACTV_DT, 'INAC_DT__c': payload.INAC_DT}))  
    when (payload.ACTV_DT == null or payload.INAC_DT == null),
  bar: 'this is bar'
  ...

“foo” and “bar” fields are just normal fields. The special one here is the “fieldsToNull”. There are two things to pay attention here: 1. “fieldsToNull” will get return value from “lookup” call. 2. “fieldsToNull” will only appear in the final SFDC call if either ACTV_DT or INACT_DT is null, otherwise, “fieldsToNull” won’t even appear in the SFDC call.

The first argument of “lookup” is the flow name you are calling, the 2nd parameter is a map with name value pairs to be passed to the flow (as the payload for the flow).

Now, let’s create the flow (or sub-flow). I call it “buildNullFields”:

<flow name="buildNullFields">
  <set-variable variableName="fields" value="#[new ArrayList()]" doc:name="fields"/>
  <logger message="before build list pay=#[payload]" level="INFO" doc:name="Copy_of_Logger"/>
  <set-payload value="#[payload.entrySet()]" doc:name="Set Payload"/>
  <foreach doc:name="For Each">
    <choice doc:name="Choice">
      <when expression="#[payload.getValue() == null]">
        <set-variable variableName="tempAddStatus" value=" #[fields.add(payload.getKey())]" doc:name="fields"/>
      </when>
      <otherwise>
        <logger message="*** do nothing" level="DEBUG" doc:name="Logger"/>
      </otherwise>
    </choice>
  </foreach>
  <set-payload value="#[fields]"  doc:name="Set Payload"/>
  <logger message="fields=#[payload]" level="INFO" doc:name="Copy_of_Logger"/>
</flow>

This flow first instantiates an ArrayList (that’s what SFDC expects), then it loop through the input payload (as a map), if a value is null, then the corresponding key (field name) is added to the ArrayList. Finally, it returns the null value fields as an ArrayList to the caller (dataweave in this case).

Now you see the trouble I had to go through to make the fieldsToNull work. I recommend you update to the latest connector if want to the same feature without the extra troubles.