Mulesoft Salesforce Integration: Using Upsert and Query

This article will explain how to create a Mule API that can UPSERT (INSERT and UPDATE) and RETRIEVE contacts information from Salesforce CRM using Salesforce Connector.

1. Define the RAML and Generate the RAML Flows:

POST: /contacts — Resource that will upsert contacts in Salesforce.

GET: /contacts — Resource that will get all contacts from Salesforce.

GET: /contacts/{id} — Resource that will get a contact information by Id.

#%RAML 0.8
title: Salesforce Contacts API

/contacts:
  post: 
    body: 
      application/json:
    responses: 
      200:
        body: 
          application/json:
  get:
    responses: 
      200:
        body: 
          application/json:
  /{id}:
    get:
      responses: 
        200:
          body: 
            application/json:

Once you're done defining the RAML, right-click to that file and Select Mule > Generate Flows From REST API.

2. Mule Flow Implementation

Create new Mule XML File (mule-salesforce-service.xml) and place all flow implementation here.

2.1 Get Contacts Resource Implementation

GET: /contacts — Resource that will get all contacts from Salesforce

GET: /contacts/{id} — Resource that will get a contact information by ID

Create a Private Flow with the name pf-main-get-contacts. This flow will handle the above 2 resources. The Choice with a condition #[flowVars.id != null], if true, then Salesforce Get Contact By Id will be executed, otherwise, Salesforce Get All Contact. Also, note that the flowVars.id value will come from the resource /contact/{id} define in the RAML file.

Salesforce Configuration:

Provide your Salesforce Username, Password, Security Token, and the Authorization URL in the Salesforce Configuration.

   <sfdc:config name="Salesforce__Basic_Authentication"
username="********" password="*********"
securityToken="********" doc:name="Salesforce: Basic Authentication"
url="https://test.salesforce.com/services/Soap/u/39.0" />

Salesforce Get Contact By Id (Salesforce Connector Using Query Operation):

Salesforce Get Contacts (Salesforce Connector Using Query Operation):

pf-main-get-contacts Flow XML Code:

The Salesforce Connector always return an Object Data Type(Collection), so make sure the Transform Message(Dataweave) after the Salesforce Connectoralways have <dw:input-payloadmimeType="application/java"/> to avoid issues and dataweave warning in the logs.

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:sfdc="http://www.mulesoft.org/schema/mule/sfdc" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
xmlns:spring="http://www.springframework.org/schema/beans" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/sfdc http://www.mulesoft.org/schema/mule/sfdc/current/mule-sfdc.xsd
http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd">

<flow name="pf-main-get-contacts">
        <choice doc:name="Choice flowVars.id?">
            <when expression="#[flowVars.id != null]">
                <sfdc:query config-ref="Salesforce__Basic_Authentication" 
                            query="dsql:SELECT Id,LastName,FirstName,Phone,Email FROM Contact WHERE Id = '#[flowVars.id]'" doc:name="Salesforce Get Contact By Id"/>
                <dw:transform-message doc:name="Transform Message To JSON">
                    <dw:set-payload><![CDATA[%dw 1.0
%output application/json
---
{
correlationId: flowVars.correlationId,
record: (payload map $) default null
}
]]></dw:set-payload>
                </dw:transform-message>
            </when>
            <otherwise>
                <sfdc:query config-ref="Salesforce__Basic_Authentication" 
                            query="dsql:SELECT Id,LastName,FirstName,Phone,Email FROM Contact" doc:name="Salesforce Get All Contacts"/>
                <set-variable variableName="totalRecords" value="#[payload.size()]" doc:name="Variable Set totalRecords"/>
                <dw:transform-message doc:name="Transform Message To JSON">
                    <dw:input-payload mimeType="application/java"/>
                    <dw:set-payload><![CDATA[%dw 1.0
%output application/json
---
{
correlationId: flowVars.correlationId,
reacordSize: flowVars.totalRecords,
records: payload map $
}
]]></dw:set-payload>
                </dw:transform-message>
            </otherwise>
        </choice>

    </flow> 
</mule>

2.2 Upsert Contacts Resource Implementation

POST: /contacts — Resource that will upsert contacts in Salesforce.

The Transform Message Set Salesforce Payload converts the JSON request to an Object Data Type(Collection).

The Transform Message To JSON converts the returned payload(Collection Object Data Type) from Salesforce connector to a JSON format with MIME Type application/json. It also removes unnecessary fields from Salesforce Connector to make the response readable to the user.

Salesforce Upsert Contact (Salesforce Connector Using Upsert Operation)

Set Upsert as the Operation and the Id as the External Id Field Name. This Id will be used by the Salesforce Data Loader to relate or associate the records to each other during Upsert Operation. For instance, it will associate the records from the JSON request submitted to the Mule API and to the records existing in the Salesforce CRM. If the Id from the JSON request is existing on the Salesforce CRM, it will do an UPDATE, if not, then it will do an INSERT.

pf-main-upsert-contacts Flow XML Code:

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:tracking="http://www.mulesoft.org/schema/mule/ee/tracking" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" xmlns:sfdc="http://www.mulesoft.org/schema/mule/sfdc" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
xmlns:spring="http://www.springframework.org/schema/beans" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/sfdc http://www.mulesoft.org/schema/mule/sfdc/current/mule-sfdc.xsd
http://www.mulesoft.org/schema/mule/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd
http://www.mulesoft.org/schema/mule/ee/tracking http://www.mulesoft.org/schema/mule/ee/tracking/current/mule-tracking-ee.xsd">

    <flow name="pf-main-upsert-contacts">
        <dw:transform-message doc:name="Transform Message Set Saleforce Payload">
            <dw:input-payload mimeType="application/json"/>
            <dw:set-payload><![CDATA[%dw 1.0
%output application/java
---
payload]]></dw:set-payload>
        </dw:transform-message>
        <sfdc:upsert config-ref="Salesforce__Basic_Authentication" 
                     externalIdFieldName="Id" type="Contact" 
                     doc:name="Salesforce Upsert Contacts">
            <sfdc:objects ref="#[payload]"/>
        </sfdc:upsert>
        <logger message=" #['\nSalesforce Return Data: '+payload]" level="INFO" doc:name="Logger Payload"/>
        <dw:transform-message doc:name="Transform Message To JSON">
            <dw:set-payload><![CDATA[%dw 1.0
%output application/json
---
{
correlationId: flowVars.correlationId,
operation: "UPSERT",
status: "COMPLETED",
processedRecords:payload map {
recordId: $.id,
isSuccess: $.success,
operation: "CREATE" when $.created otherwise "UPDATE",
errors: $.errors map (error,index) -> {
error: error.message,
erroredFields: error.fields
}
}

}

]]></dw:set-payload>
        </dw:transform-message>
    </flow>
</mule>

Reference the created Flow Implementation in the corresponding RAML generated flows.

Test Result:

POST: /contacts/

Insert 2 records. The API will response the status and operation (CREATE or UPDATE) for each record processed.

POST: /contacts/

Insert 1 record and Update existing record.

GET: /contacts/

GET /contacts/{id}