public

Using Salesforce Search instead of a query using Mule 4

SOSL and SOQLOne of the most used actions when we work with Salesforce integrations is the use of query. It allow us to pull information from any table and even

8 months ago

Latest Post What tools do I use as Mulesoft developer by Edgar Moran public

SOSL and SOQL

One of the most used actions when we work with Salesforce integrations is the use of query. It allow us to pull information from any table and even doing some subqueries and pulling relationship data, but also, there’s one action we as developers don’t use very often (and sometimes even we don’t know this operation is available for us) it is Salesforce Object Search Language (SOSL).

Based on the Force.com Developer documentation:

A SOQL query is the equivalent of a SELECT SQL statement and searches the org database. SOSL is a programmatic way of performing a text-based search against the search index.

Whether you use SOQL or SOSL depends on whether you know which objects or fields you want to search, plus other considerations.

Use SOQL when you know which objects the data resides in, and you want to:

Use SOSL when you don’t know which object or field the data resides in, and you want to:

Here some differences from SOSL and SOQL

Taken from Force.com Developer documentation

Maybe at this point, you might be asking yourself how this is helpful and how I can use it, well let’s think about a scenario.

Scenario

The general idea is to be able to process User information coming from any source and use the information to be able to validate if a Contact or Lead already exist in the platform using an specific external Id field. Based on the result we should be able to update a contact , lead or create a brand new Lead record.


Implementation

I will create a pretty simple application to demonstrate how we can accomplish this. The Mule application would be created in Mule 4 and I will set a few records in a DataWeave component just like an input.

Input-data-flow

This flow contains an scheduler, basically to manually trigger the integration for demonstration. Then we set the incoming Payload in a DataWeave component just like this:

<script src="https://gist.github.com/emoran/7046fc33dd01ac664880ca4414e6e0c8.js"></script>

Also there’s a variable called “originalPayload”, this one will be used to filter the information out, once we get Salesforce information.

In the next DW component (Preparing Search Request) we just convert all the external id values from the original response to a plain String value concatenated by OR making this an understandable payload value for the Salesforce search, the code looks like this:


Salesforce-search-flow

This flow will be on charge of make the search call into Salesforce, grouping the response and creating the variables we need to filter the originalPayload with the existing records.

In the Salesforce search, wi will pass the next sentence

FIND { :ids } IN ALL FIELDS RETURNING Contact(Id,external_id__c,Email), Lead(Id,external_id__c,email)

Where basically the :ids parameter is the previous String we created separating the Id’s with OR. In this search we are asking to search records from Contacts and leads searching in all fields and when returns the information we tell what fields we need from each object.



Mapping the search response component just creates a map of the salesforce results (payload.searchRecords), after this, we will group the information by type. We will use this script:

%dw 2.0
output application/java
---
(payload groupBy ((value, index) -> value."type"))

And in the same component I’m creating a variable called: salesforceResponseMap which contains a key-value map we can access using a value to get the full record.

%dw 2.0
output application/java
---
{
(payload map {
(($.external_id__c):$) if $.Id != null
})
}

Collect by type is a different variable, it allows to separate the records from contacts and leads we found and we set the id as the main key in order to filter in next components. At this point we already know what contacts and leads has been found.

%dw 2.0
output application/java
---
{
fromContacts:payload.Contact map (salesforceContact,IndexOfContact)->{
(id:salesforceContact.external_id__c) if (salesforceContact.external_id__c != null),
},
fromLeads:payload.Lead map (salesforceLead,indexOfLeads)->{
(id:salesforceLead.external_id__c) if ( salesforceLead.external_id__c != null),
}
}


Filter-and-collecting-records

This flow will filter from the original payload removing existing contacts from salesforce and leaving the records that need to be created as leads.

Filter Contacts / Update contacts will take any existing records from the groupedObjects.fromContacts flow variable based on the id using this script:

%dw 2.0
output application/java
---
vars.originalPayload filter (not (vars.groupedObjects.fromContacts.id contains ($.id)))

Basically we are removing records to an array from another one.


In the same component we are doing basically the same but without the not sentence so it means we are collecting the information that needs to be updated as Contact and we are able to map the fields we need to update

%dw 2.0
output application/java
---
(vars.originalPayload filter ((vars.groupedObjects.fromContacts.id contains ($.id))) map (contact,indexOfContact) -> {
Id:vars.salesforceResponseMap[contact.id].Id,
FirstName:contact."First Name"
})


Filter leads / Update Leads is basically the same but using the Leads group.

Finally the remaining component collects the remaining information of records that need to be created as Leads in Salesforce and we can map the information.




enqueue-batch-jobs

The meaning of this job is just to set the payloads for update and create records, the only additional thing on this components is that we are specifying sObject and externald variables, so instead of write a batch component for each type, dynamically we are passing the sObject for updates and sObject and externalId for Upsert calls, this means we can reuse our batch processes.



Finally we can see the batch processing flow . One batch corresponds to update objects and just  control the  response from salesforce with a DW component like this:

%dw 2.0
output application/json
---
payload.items map {
id:$.id,
success:$.successful,
(field:$.errors[0].fields[0]) if $.successful == false,
(message:$.errors[0].message) if $.successful == false,
(statusCode:$.errors[0].statusCode) if $.successful == false
}

So basically we can collect the responses and use them


There are some consideration of using SOSL over SOQL, I think one of the best advantages of this is that we are able to retrieve multiple objects in a call and we are saving a couple of API calls, this can be used on processes that need just a few records, massive amounts of data might include some complexity on how we create the SOSL sentence, but in the end we can just adjust a bit to the limits (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_sosl_limits.htm) in case we need to.

Let me know if you think this is helpful and I will be happy to enhance this process as well.

You can pull the code from this repository if you want to see the whole process working.

Edgar Moran

Published 8 months ago

Comments?

Leave us your opinion.