2 things that surprised us while developing Attachment Exchange Interface for Salesforce instances with Mulesoft.

Our Task:

Build interface for synchronization of Cases with attachments between two Salesforce instances. Main assumption of the implementation:

  •      Attchaments linked directly to case and also attachments from emails are process separately. Exchange of Attachments should be triggered by successful transfer of Case.
  •      So generally first Case exange flow is process and when it is successfully done, Attachments exchange flow is triggered.

Today we are focused on Attachment part. Implementation for the first sight seems to be easy. All we need to do is to query needed Attachment data basing on Case Id from one Salesforce instance and insert it to second Salesforce instead.

Unexpected issue:

The main problem was proper approach for querying Attachments data. As we know the best practice for that is to use for that Content Version object. Because we need to retrieve attachments that are linked directly with Case as well as attachments from linked to Case Email Messages, we decided to use Salesforce Composite connector  (Execute Composite Request) with this code in Composite Request body:

%dw 2.0 
output application/json
--- 
{
	"allOrNone": false,
	"compositeRequest":[
                {
			"method": "GET",
			"referenceId": "getCase",
			"url": "/services/data/v49.0/query/?q=SELECT+Id+FROM+Case+WHERE+ExternalCaseNumber__c='"++ vars.caseId ++ "'"
		},
		{
			"method": "GET",
			"referenceId": "getCaseAttachments",
			"url": "/services/data/v49.0/query/?q=SELECT+ContentDocumentId,VersionData,Title,FileType,FileExtension,FirstPublishLocationId,ContentSize,Id+FROM+ContentVersion+WHERE+FirstPublishLocationId= '" ++ "@{getCase.records[0].Id}" ++ "'"
		},
		{
			"method": "GET",
			"referenceId": "getEmailsAttachments",
			"url": "/services/data/v46.0/query/?q=SELECT+ContentDocumentId,VersionData,Title,FileType,FileExtension,FirstPublishLocationId,ContentSize,Id+FROM+ContentVersion+WHERE+FirstPublishLocationId+IN+(SELECT+Id+FROM+EmailMessage+WHERE+ParentId= '" ++ "@{getCase.records[0].Id}" ++ "')"
                }
         ]
}

WHERE: vars.caseId is provided to API, and store in variable Case Id.


We were more than happy that with one request we are able to get all needed data for transferring them. But after tests we have realized that with this approach we are getting only Attachments that were created by our User. It was our first surprise.

Solution:

To get all Attachments from Case we need to first get all ContentDocumentId using Content Document Link object. Content Document Link object represents  the link between a Salesforce CRM Content document or Salesforce file and where it's shared. A file can be shared with other users, groups, records, and Salesforce CRM Content libraries.

%dw 2.0 
output application/json
--- 
{
	"allOrNone": false,
	"compositeRequest":[
		{
			"method": "GET",
			"referenceId": "getCase",
			"url": "/services/data/v49.0/query/?q=SELECT+Id+FROM+Case+WHERE+ExternalCaseNumber__c='"++ vars.caseid ++ "'"
		},
		{
			"method": "GET",
			"referenceId": "getCaseAttachments",
			"url": "/services/data/v49.0/query/?q=SELECT+ContentDocumentId+FROM+ContentDocumentLink+WHERE+LinkedEntityId='" ++ "@{getCase.records[0].Id}" ++ "'"
		}, 
		{
			"method": "GET",
			"referenceId": "getEmailsAttachments",
			"url": "/services/data/v49.0/query/?q=SELECT+ContentDocumentId+FROM+ContentDocumentLink+WHERE+LinkedEntityId+IN+(SELECT+Id+FROM+EmailMessage+WHERE+ParentId='" ++ "@{getCase.records[0].Id}" ++ "')"
		}
        ]
}

With this approach we were able to retrieve all Attachments linked to Case.

In our design we use VM module to publish every Attachment's ContentDocumentId  value and process them one by one in asynchronous way. After that we have second Composite request to get Content Version data for specific ContentDocumentId:

{
	"allOrNone": false,
	"compositeRequest":[
                {
			"method": "GET",
			"referenceId": "getAttachmentData",
			"url": "/services/data/v49.0/query/?q=SELECT+ContentDocumentId,VersionData,Title,FileType,FileExtension,FirstPublishLocationId,ContentSize,Id+FROM+ContentVersion+WHERE+ContentDocumentId ='"++ vars.contentDocumentId ++ "'"
		}
         ]
}
WHERE: vars.contentDocumentId is provided to VM Listener, and store in variable Content Document Id.

We were close to celebrate! But wait… look at the VersionData value:
/services/data/v49.0/sobjects/ContentVersion/0770Q000001QiL6MAK/VersionData

After investigation we have realised that VersionData is returned as sObject Blob Retrieve resource. We have made research and it is bevaiors of REST Salesforce API which composite connector is using.

Second surprise!

With Salesforce Composite Request connector we are using SOAP API. So in the end we have used standard Salesforce Query Connector which use SOAP API: 

WHERE: :contentDocumentId is bind variable to vars.contentDocumentId. 


SELECT ContentDocumentId,VersionData,Title,FileType,FileExtension,FirstPublishLocationId,ContentSize,Id FROM ContentVersion WHERE ContentDocumentId = ':contentDocumentId'

And now we can finally celebrate 😊


See you soon,
Monika on behalf of Salesforce Freaks ;)


Comments

Post a Comment

Popular posts from this blog

How to start work with Anypoint Studio?

Image
Anypoint Studio  is a user-friendly eclipse-based  IDE (integration development environment) used for designing and testing Mule applications. Basic setup of our IDE: Install the following software: OpenJDK 8:  https://adoptopenjdk.net/ Anypoint Studio 7.9.0:  https://www.mulesoft.com/lp/dl/studio Git:  https://git-scm.com/download/ Maven:  https://maven.apache.org/download.cgi  ( Installation Guide ) Create your account on Anypoint Platform:    https://anypoint.mulesoft.com/login/signup After successful   installation  of software, start Anypoint Studio and: create a workspace configure Anypoint Studio for EU Region: Window > Preferences:  Configure Anypoint Studio to use the native browser: Window > Preferences: Configure your credentials for the Anypoint platform: Window > Preferences: Setup local Maven (Optional)  If you want to run MUnit tests from the command line, you will need to define a maven reposi...
Read more

Security Vulnerabilities in Salesforce

Image
  In Salesforce we are using a lots of custom user interfaces. That’s why we should be aware about  various types of security vulnerabilities. The most common you can find here:          SOQL injection,          Cross-Site Scripting (XSS),          Cross-Site Request Forgery (CSRF). Let's discuss each of them briefly. SOQL injection From definition SQL injection can happen when we have insecure construction of database queries with user-supplied data e.g.:         queries are built directly with user data inlined,         queries are   concatenated directly with the query text (not type-safe bind parameters). When designing SOQL queries there are basically three places where behavior of the query can be based on input from User:     Behaviors of the WHERE clause to change the set of returned records.   Selecting fields from ...
Read more

Fetch dependent picklist values depending on record type

Image
The problem The main problem with getting picklist based on recordtype, there is no "out of box" solution. You can easly fetch all values from picklist but without information about recordtypes.  The solution You need use "User Interface API", not very elegant solution, but it works. Give more time Salesforce for implement such obvious functionality (the idea arose only 8 years ago). /ui-api/object-info/{objectApiName}/picklist-values/{recordTypeId}/{fieldApiName} Step by step: public static String innerSalesforceCallout(String serviceEndpoint) {      if (String.isEmpty(serviceEndpoint)){           return null;      } Http http = new Http(); HttpRequest webReq = new HttpRequest(); webReq.setMethod('GET'); webReq.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId()); webReq.setEndpoint(serviceEndpoint);     try { HttpResponse res = http.send(webReq);     } c...
Read more