Salesforce Lookup

In this article

Overview

The Salesforce Lookup Snap is a Read-type Snap that provides the ability to format WHERE clause from input document stream for a SOQL (Salesforce Object Query Language) query. See Introduction to SOQL and SOSL for more information.

To use newer Salesforce objects, use the latest API versions where those objects are available.



Prerequisites

A valid Salesforce account with the required permissions.

Support for Ultra Pipelines

Works in Ultra Pipelines.

In Ultra mode, ensure that you enable the Single Lookup Request property checkbox to process one document at a time.

Limitations and Known Issues

None.

Snap Views

TypeFormatNumber of ViewsExamples of Upstream and Downstream SnapsDescription
InputDocument
  • Min:1
  • Max:1
  • JSON Formatter
  • Mapper
This Snap has exactly one input view and receives Document(s) in the view. Expressions in the Object type, Output fields, Conditions and Correlation ID properties can be evaluated with values from the input view during the execution.
OutputDocument
  • Min:1
  • Max:1
  • Mapper
  • Copy
  • File Writer
Documents
Error


The error view contains error, reason, resolution and stack trace. For more information, see Handling Errors with an Error Pipeline.

Snap Settings

Field

Field Type

Description


Label*

 

String

Specify a unique name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your Pipeline.

Default ValueSalesforce Lookup
ExampleSalesforce Lookup

Service Version*

String/Expression

 Specify the version number associated with the Salesforce service that you want to connect to. Alternatively, click the Suggestion  icon to fetch the list of versions and select the desired version.

Default Value52.0 
Example41.0

Salesforce API*


Dropdown list

This property sets the Salesforce API mode to Bulk API or REST API during the pipeline execution.

Default Value: REST API
Example: Bulk API

Object Type*

String/Expression/Suggestion

Specify the name of the Salesforce object or select one from the suggested list.

This Snap does not support Net Zero Cloud Salesforce objects.

Default Value:  Account
Example:  Account

Output Fields*

Use this field set to define the field names for the Salesforce Object Query Language (SOQL) SELECT statement. Specify each output field as a separate row. Click  to add a new row. 

Output Fields*String/Expression/Suggestion

Specify a list of field names for the SOQL SELECT statement. Alternatively, click the Suggestion  icon to fetch the list of field names associated with the object type and select a field.

The value for this field can also be an expression that will be evaluated against the values configured in Pipeline parameters only.

Default Value: N/A
ExampleId, Name, ShippingAddress

Conditions*Use this field set to define conditions. Specify each condition as a separate row. Click  to add a new row.

Conditions*

String/Expression

Specify the condition for the WHERE clause.

The values in this field forms an AND clause in the SOQL WHERE statement. Each entry in the Conditions row can be an expression to be evaluated with the values from the input view. The Snap receives a stream of document data, where each document is formatted into an AND clause and all AND clauses are ORed together to form an entire WHERE statement. If the size of SOQL statement exceeds 10,000 characters, the Snap sends the SOQL query request, retrieves the resulting records which are sent to the output view, and restarts forming a new WHERE statement.

Example: "Name = '" + $Name + "'"
The above example is an expression which can be evaluated to "Name = 'O\'Connor'", if $Name is a JSON path to a value "O'Connor" in the input document data. The value of the Name field is a string type which must be enclosed by a pair of single quotes. If there is any single quote in the value, like the above example, it is escaped with a backslash by the Snap automatically. Otherwise, Salesforce.com will reject the SOQL request.


Using quotes in the WHERE clause

  • Do not use quotes for field names in the WHERE clause. Using quotes results in an error.
  • Use only single quotes for values in the WHERE clause as using double quotes results in an error.
  • The above rules do not apply when you are using SnapLogic expressions, you can use quotes for the field names and values as applicable. 

Default Value: N/A







Example:  "Name = '" + $Name + "'"

Correlation ID

String/Expression/Suggestion

Specify an ID field name which the Snap uses to correlate input document to the output record when you want to pass through input data to the output view. Alternatively, click the Suggestion icon to fetch a list of IDs and select a ID.

  • The value for this field can be an expression that will be evaluated against the values configured in the Pipeline parameters only.
  • The value of this field must be unique throughout the entire records in the selected object type, so that it identifies a specific record in an SObject type. External ID field names fit into this category. This field may be left empty if users do not want to pass through input document to the output view. 

Default Value: N/A







ExampleId

Single Lookup Request


Checkbox

Select this checkbox to enable the Snap to send one lookup request for each input document. In Ultra mode, ensure that you select this checkbox to process one document at a time.

Default Value: Not selected 

Polling Interval*


Integer/Expression

Specify the polling interval in seconds for the Bulk API query execution. At each polling interval, the Snap checks the status of the Bulk API query batch processing.

Maximum value:: 60 

This field can be an expression that will be evaluated against the values configured in Pipeline parameters only.

Default Value: 5
Example: 5

Polling Timeout*

 

Integer/Expression

Specify the polling timeout in seconds for the Bulk API read batch execution. If the timeout occurs while waiting for the completion of the read batch execution, the Snap throws a SnapExecutionException. 

This field can be an expression that will be evaluated against the values configured in Pipeline parameters only.

Default Value:  3000
Example:  300

Process Date/Time

 

Checkbox

All date/time fields from Salesforce.com  are retrieved as string type. If this property is unchecked, the Snap sends these fields without any conversion.

If checked, the Snap converts date/time fields to corresponding date/time types by accessing the meta data of the given SObject. Salesforce datetime is converted to Joda DateTime, Salesforce date to Joda LocalDate and Salesforce time to Joda LocalTime.

Default Value: 
false

Include Deleted Records

 

Checkbox

Select this checkbox to include deleted records in the query result. This feature is supported in REST API version 29.0 or later and Bulk API version 39.0 or later.

Default Value: Not selected 

Pass-through on No Lookup Match

Checkbox

Select this checkbox to allow the input document to pass through to the output view when there is no lookup matching an input document. Else, the input document is written to the error view as an error condition.

Default ValueNot selected

Match Data Type


 

Checkbox

Select this checkbox to match the data types of the Bulk API results with the data types in REST API. This property applies if the content type is not JSON in Bulk API. In Bulk API, Salesforce.com returns all values as strings if Bulk content type is XML or Batch size is for PK Chunking. If this property is checked, the Snap attempts to convert strings values to the corresponding data types if the original data type is one of boolean, integer, double, currency, and percent.

This property is ignored in REST API or with JSON Bulk content type. In Bulk API (not with JSON Bulk content type), Salesforce.com does not return any value for null. Therefore, if the value is null, the key-value entry is not present in the output document, which is different from the REST API result.


Default Value
:  Not selected

Snap Execution

Dropdown list

Select one of the following three modes in which the Snap executes:

  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.

  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.

  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default ValueExecute only
Example: Validate & Execute


Example


Looking up Records

In the following example Pipeline, the Salesforce Lookup Snap shows how Salesforce object records can be looked up:

We use the Mapper Snap to map an account number to the AccountNumber field of a Salesforce object record.

 

The Salesforce Lookup Snap queries the Salesforce Object Account for the provided account number and specifies the output to contain only the BillingStreet value.

 

Successful validation of the Snap gives the following output.

Looking-up Records with REST API Using the Correlation ID

In this Pipeline, we read data from the Salesforce Read Snap using filter condition in WHERE clause, take the first 10 rows of data in Head Snap and pass those values in the Salesforce Lookup Snap using the Correlation Id property.  

The Salesforce Read Snap reads the data from the SObject type Account, with the desired output fields as Name, Id, BillingCity and AccountNumber.  

 
The output preview of the Salesforce Read Snap: 

 
 The Head Snap reads the first ten documents starting from 0, and the output preview is as displayed:


 
The values are passed from the Head Snap to the Salesforce Lookup Snap. The Salesforce Lookup Snap with the output fields, Id, Name and Type, and Id, given as the where condition. The Correlation ID is set to the ID.

 

 
Successful execution of the Pipeline displays the below output preview.

 

Looking-up Records with a Single Lookup Request

In this Pipeline, the Salesforce Read Snap reads the records from an object, maps the value of an ID, and passes the values to the Salesforce Lookup Snap.

 The Salesforce Read Snap retrieves the records for the object, Account _c.

The Mapper Snap maps the values of the ID for the Account_c object and passes them to the Salesforce Lookup Snap.

The Salesforce Lookup Snap retrieves the data from the object, for the specified output fields and generates a SQL statement based on the condition ""Id = '" + $Id + "'".

The Successful execution of the pipeline displays the below output preview.

Looking-up Records Using Correlation ID

In this Pipeline, the Salesforce Read Snap reads the records from an object, maps the value of an ID, and passes the values to the Salesforce Lookup Snap.

 The Salesforce Read Snap retrieves the records for the object, Account _c.

The Mapper Snap maps the values for the ID and the custom field, '$Customer_enq__c' for the object, Account_c, and passes them to the Salesforce Lookup Snap.

The Salesforce Lookup Snap retrieves the data from the object, for the specified output fields and generates a SQL statement based on the condition ""Id = '" + $Id + "'". selecting the custom field ($Customer_enq__c) as correlation ID, 

The successful execution of the pipeline displays the below output preview:

Downloads

Important steps to successfully reuse Pipelines

  1. Download and import the pipeline into the SnapLogic application.
  2. Configure Snap accounts as applicable.
  3. Provide pipeline parameters as applicable.

  File Modified

File Example_salesforce_Lookup_Records.slp

Mar 10, 2022 by Subhajit Sengupta

Snap Pack History

 Click to view/expand
Release Snap Pack VersionDateType  Updates

May 2024

main26341

 

Stable

Updated and certified against the current SnapLogic Platform release.

February 2024436patches25626 LatestEnhanced the Salesforce Bulk Create, Bulk Delete, Bulk Query, Bulk Update, and Salesforce Bulk Upsert Snaps to include the complete error details under the original object that contains the copies of the failed input records.
February 2024436patches25192 Latest

Fixed an issue with the Salesforce Mutual Authentication Account where the 8443 port was not used while authorizing the account. Now, the access token request uses the 8443 port for authorizing the Mutual Authentication Account.

Breaking change

Prior to 436patches25192, the Salesforce Mutual Authentication Account supported default Login URLs such as https://login.salesforce.com and https://test.salesforce.com. From 436patches25192, this account no longer supports the default Login URLs—your existing pipelines using the Mutual Authentication Account with the default Login URLs might break.

To prevent this breaking change, you must provide a valid Salesforce Login URL as part of your account configuration.

February 2024main25112 StableEnhanced the Salesforce Bulk Query Snap with expression support for the Include Deleted Records checkbox.
November 2023435patches24747 LatestFixed an issue that caused the input stream to not close properly, which resulted in stale connections.
November 2023435patches24368 Latest

Enhanced the performance of the Salesforce Bulk Query Snap with a new Maximum threads field. This field enables you to download query results simultaneously in multiple threads.

November 2023435patches24006 Latest

Enhanced the Salesforce Read Snap by adding expression enablement to the following checkboxes:

  • Use PK chunking if supported

  • Include Deleted Records

November 2023main23721 StableUpdated and certified against the current SnapLogic Platform release.
August 2023434patches23646 LatestFixed an issue where a long-running pipeline containing the Salesforce Subscriber Snap would suddenly stop responding.
August 2023434patches22537 Latest

Improved the Salesforce Bulk Upsert Snap error messages by limiting the length of the reason displayed with the error.

August 2023main22460 StableUpdated and certified against the current SnapLogic Platform release.
May 2023433patches21367 Latest
  • The Salesforce SOQL Snap now honors the selection of the Match Data Type checkbox when the value entered for Batch Size is greater than 50,000.

  • The Salesforce Read Snap now honors the selection of the Match Data Type checkbox if the Use PK chunking if supported checkbox is also selected.

May 2023

main21015 

Stable

Upgraded with the latest SnapLogic Platform release.

February 2023432patches20586 Latest

Fixed an issue with the Salesforce SOQL and Salesforce Read Snaps where the Match Data Type checkbox selection was not honored if the Salesforce API field was set to Bulk API.

February 2023432patches20393 Latest
February 2023432patches20145 LatestFixed an issue with the Salesforce Read Snap that was causing an error for the Time data type.
February 2023432patches20087  Latest

The Salesforce Create, Update, and Upsert Snaps now include an Apply active assignment rules checkbox so you can control whether to use assignment rules for the selected object

February 2023

432patches20008