Retrieving Data from Salesforce Using the MuleSoft Connector

MuleSoft’s Anypoint Connector for Salesforce enables users to connect to Salesforce APIs and gives access to all Salesforce entities. The connector supports all create, read, upsert, delete, and bulk operations across salesforce objects. In this blog, we will focus on three common scenarios for retrieving data using the Salesforce connector.

Case 1 will explore using pagination to retrieve potentially large amounts of data 

Case 2 will explore Salesforce SOSL Search to retrieve data based on text matches

Case 3 will look into the bulk job operation and how to retrieve data from a bulk insert

Case 1: Query and Pagination of Large Amounts of Data

It’s best to consider a pagination strategy when querying a large number of records while keeping the query performant. The OFFSET clause on a SOQL query allows you to return results on multiple pages making it an efficient way to handle large results sets. I will demonstrate how to implement pagination with a SOQL query in Salesforce using offset and limit in the sample below.

Consider a backend system that can only take 4 records at a time. You would need to paginate records coming from Salesforce in the following fashion:

Page 1:

SELECT Id,Name,createdDate FROM Account LIMIT 4

Page 2:

SELECT Id,Name,createdDate FROM Account LIMIT 4 OFFSET 4

Subsequent pages will follow

SELECT Id, Name,createdDate FROM Account OFFSET 4*(n-1) LIMIT 4

 

This can be accomplished using the Query operation of the Salesforce module and adjusting the query to take both LIMIT and OFFSET as parameters.

 

Case 2:  Salesforce SOSL Search

Salesforce SOSL search is similar to a search bar where results are returned based on a text match. In addition, Salesforce SOSL allows you to query multiple objects at the same time.

In this sample, I will demonstrate SOSL using the Search operation of the Salesforce connector. Consider a search where you need to retrieve data from two separate objects based on a text. Your search string should look similar to the following:

FIND { :records } IN ALL FIELDS RETURNING Account(Name), Lead(Company)

The parameter “records” refers to the string fragment you are searching for. In this example, we are searching for Accounts or Leads containing the words “Uni” or “Corp”. 

%dw 2.0 output application/java — ‘”Uni”‘ ++ ‘ OR ‘ ++ ‘”Corp”‘

 

After formatting the results, you should be able to see both Accounts and Leads containing the search string. You can also see from which object the result came.

Case 3:  Bulk API v2

Not every Salesforce operation is in real-time. Sometimes records are inserted in bulk via Bulk API v2. A limitation of the bulk operation is that the calling application does not know in real-time whether or not the records were inserted successfully, failed, or if some were inserted while others failed. As a result, in order to retrieve records from a bulk operation, you must first get the job state to ensure it is completed. Then, you can proceed with retrieving the individual results from the operation. In this example, I will create a bulk insert to set up the data, query its status and retrieve the job results. 

Bulk insert is done using the “Create job bulk API v2” operation. You specify the object, and operation and provide the elements to insert. We will not focus on the bulk insert. All we need to know is that the “Create job” will give a job ID in its payload. 

 

In order to get the results, first we need to check the state of the job. Values can be Open, UploadComplete, InProgress, Aborted, JobComplete and Failed. Additional details on what each state means can be found in the Salesforce documentation here. In most cases, a poller is implemented to check until the state is set to  “JobCompleted”. Additionally, we would want to do a check for the other final states, such as Failed and Aborted in order to handle all possible batch result statuses.

Once a job is completed, results from the bulk must be retrieved on two separate operations

1. Retrieve failed results
2. Retrieve successful results

Depending on the requirement, you might need to process each set of results differently. For the purposes of this example, we are simply combining the results.Most of the time, we would only want to retrieve failed results as it is assumed the rest of the records on that batch were successfully processed. Salesforce allows you to retrieve both sets of results separately if needed.

The result sends back the original record as well as an id and a created flag.

The Salesforce Connector enables MuleSoft apps to easily interact with Salesforce objects. More often than not, you would run into scenarios dealing with large amounts of data, bulk operations, or fuzzy searches. 

If you need assistance with other scenarios or have questions about the Salesforce Connector contact sales@avioconsulting.com.