How to Retrieve Attachments in Salesforce (Full Example)

Document created by chris_stevens Employee on Mar 21, 2016Last modified by Adam Arrowsmith on Aug 17, 2017
Version 3Show Document
  • View in full screen mode

This article describes how to connect to Salesforce to retrieve binary attachment data associated with record objects and output to a file.

 

 

Use Case

Retrieving attachments in Salesforce is not as straightforward as retrieving a record/document from the Salesforce object that contains the attachment. This is because attachments are stored as Binary data and Binary fields cannot be selected in join queries in Salesforce. In other words, you cannot retrieve the body of an attachment if you choose any related parent/child objects in the operation. This can be overcome by first querying the attachment ID by using the attachment object as a child object (of say the Account object) in your query. You can then use the ID of the attachment to retrieve the attachment using a second query directly against the attachment object.

 

Problem: You wish to retrieve attachments bound to a related parent/child object and may be receiving a ‘MALFORMED_QUERY: Binary fields cannot be selected in join queries’ error:

 

 

 Get the example process used below from the Process Library here.

 

Approach

The following scenario will not only review how to accomplish this task, but will highlight other AtomSphere Steps, concepts and best practices, such as using:

  • Test Mode at various stages throughout the Process to validate data
  • The Data Process Step to:
    • Split an XML Document into multiple Documents based on a specific element identifier
    • Decode BASE64-encoded text strings
  • The Message Step to strip away XML tags and reveal the raw data within a specific XML tag element
  • The Set Properties Step to assign appropriate file names to outputted files

 

Implementation

 

Step 1: Add/Review Attachments:  In Salesforce.com, view (or add) the files as Attachments to an Object.  In this example, for the ‘GenePoint’ account there are a total of four attachments.  One attachment is bound to the Case object (00001016) and the other three attachments are bound directly to the Account object (GenePoint).  Let’s assume that we wish to retrieve the three attachments bound to the Account object.

 

 

Step 2: Configure the First Salesforce Connector Operation:  Create an AtomSphere process and configure a Start Step Salesforce connector such that the IDs of the Account and Attachments objects are retrieved. Within the Operation component, use the Import Wizard to generate the Response Profile (SF_Account_QUERY_Response) to import the Attachments object along with the Account details.

 

 

Once the Import is complete, the imported objects will appear in the Objects pane.  In the Fields tab, select the Account fields you wish to import.  In this example, we chose to import the ‘Id’ and ‘Name’ of the Account.  For the Attachments child object, choose only the ‘Id’ field.  Obtaining the Attachments Id up front will allow us to reference the Attachments object (and the Body of the Attachment) later in the second Salesforce Connector.

 

Be sure to apply a Start Step Parameter so that the Start Step only returns results for specific Account records.  For our example here, we’ll start by applying a filter to the Operation representing the Name of the account. To accomplish this, click on the Filters tab and add an ‘Account Name =’ element referencing the ‘Name’ field from the Account object.

 

 

Within the Start Step, be sure to pair this filter with a Parameter value so to only retrieve a limited set of results.  For our example, we’ll set a static runtime Parameter for the ‘GenePoint’ Account.  For the Input value, browse to the ‘Account Name =’ filter created previously.  Set the Type as Static and enter ‘GenePoint’ for the Static Value.

 

 

Step 3: Test the Connection and Verify Attachment Ids are Populated: It’s always a best practice to test the connection to verify the data retrieved is valid before building out the rest of the Process.  Run the Process in Test Mode and observe the XML file returned via the Document Viewer.  Note the three <Attachment> nodes returned.

 

 

Step 4: Split the Documents: Since we have more than one Attachment, we’ll want to split the XML file at the <Attachment> element level.  We can accomplish this by using the Data Process Step.  Within the Data Process Step, chose the ‘Split Documents’ Processing Step.  Be sure to load the same Response Profile (SF_Account_QUERY_Response) from the above Operation and choose ‘Attachment’ for the Split Element.

 

 

Step 5: Configure the Second Salesforce Connector Operation: Configure a second Salesforce connector such that the ‘Body’ field of the Attachment objects is retrieved. Within the Operation component, use the Import Wizard to generate the Response Profile (SF_Attachment_QUERY_Response) to import the Attachments object.

 

 

Once the Import is complete, the imported objects will appear in the Objects pane.  In the Fields tab, select the Attachment fields you wish to import.  In this example, we chose to import the ‘Id’, ‘Name’ and ‘Body’ of the Attachment.

 

 

Be sure to apply a Connector Action Parameter so that the Connector only returns results for specific Attachment ID records.  For our example here, we’ll start by applying a filter to the Operation representing the Id of the Attachment. To accomplish this, click on the Filters tab and add an ‘Attachment_Id’ element referencing the ‘Id’ field from the Attachment object.

 

 

Within the Connector Action Step, be sure to pair this filter with a Parameter value so to only retrieve a limited set of results.  For our example, we’ll set a dynamic Parameter to return results for specific Attachment Ids.  These are the same Attachment Ids that were returned in our first Salesforce Connection.  For the Input value, browse to the ‘Attachment_Id’ filter created above.  Set the Type as ‘Profile Element’ and the Profile Type as ‘XML’.  Browse to load the original SF_Account_QUERY_Response profile and chose the Attachment ‘Id’ element.  Remember, it’s within this profile that the Attachment Ids were referenced.

 

 

Step 6: Test the Connection and Verify Attachment Body is Populated: Run the Process in Test Mode and observe the XML files returned by the second Salesforce Connection via the Document Viewer.  Note the three Documents returned and how the <Body> tag contains BASE64 data.

 

 

Step 7: Set a File Name: Drag a Set Properties Step onto the canvas.  On the ‘Properties to Set’ pane, add a Document Property of type Disk - File Name.  This is assuming we’re outputting these files to disk.  On the ‘Parameters’ pane, add a new Parameter to pair with the Property.  Choose ‘Profile Element’ for Type and ‘XML’ for Profile Type.  Browse to load the ‘SF_Attachment_QUERY_Response’ profile and select the ‘Name’ element.

 

 

Step 8: Prepare the Documents for Decoding: In order to properly decode the documents, we’ll need to strip away the XML tag elements so that the Documents only contain the binary data housed within the <Body></Body> XML tag.  In order to accomplish this, we’ll use the Message Step.  Drag a Message Step onto the canvas and type ‘{1}’ in the Message window, referencing the first (and only) Parameter returned.  Add a Parameter and Choose ‘Profile Element’ as Type and ‘XML’ as Profile Type.  Browse to the SF_Attachment_QUERY_Response profile and load the Body Element.

 

 

If you choose at this point to run the Process in Test Mode, you can observe the results of the Document (via the Document Viewer) as it passes through the Message Step.  Notice how the BASE64 binary data stored within the <Body></Body> tag is now stripped away and the Document has been converted to only contain this BASE64 data.

 

 

Step 9: Decode the Document Contents: Drag a Data Process Step onto the canvas.  Choose to add a Step and select ‘BASE64 Decode’ from the Process Type pick list.

 

 

Step 10: Write Files to Disk: Drag a Connector onto the canvas and configure it to Send to Disk.  As an option, you can also choose to end the Process with a Stop Step.

 

 

Step 11: Test the Process: Execute the full Process in Test Mode and note the files written to Disk:

 

 

Process Solution Overview: The resulting Process flow should look similar to the following:

 

8 people found this helpful

Attachments

    Outcomes