Skip to Content

Query Salesforce records using SOQL in Jitterbit Studio

Introduction

SOQL (Salesforce Object Query Language) is the query language used by all Salesforce APIs to retrieve records. Studio offers two approaches for querying Salesforce records:

  • Salesforce Query activity: A configuration wizard that builds and validates the SOQL statement, generates a response schema, and acts as the source in an operation. Use this for bulk reads where Salesforce records drive a transformation.
  • Scripting functions (SfLookup, SfLookupAll): Inline lookups executed within a script or transformation mapping. Use these for single-value or small-set lookups that support processing logic, such as resolving a parent record ID during a transformation.

For complete reference documentation, see Salesforce Query activity and Salesforce functions.

This guide assumes a Salesforce connection is already configured in the project.

Design pattern

The Query activity follows the transformation pattern:

flowchart LR A[Salesforce Query activity] --> B[Transformation] --> C[Target activity]

Part 1: Configure the Salesforce Query activity

Step 1: Name the activity and select objects

  1. On the design canvas, drag a Query activity type from an existing Salesforce connection to start the operation.

  2. Double-click the activity to open its configuration.

  3. Name: Enter a descriptive name, such as Query Contacts.

  4. Select Object(s): Click the primary object to query, for example Contact or Account. Only one primary object can be selected.

  5. Select Related Object(s): Optionally, select the checkbox next to each related object to include in the query. This produces a relationship query, which allows you to retrieve fields from child or parent objects in a single request. Related object selection is limited to immediate children and ancestors up to five levels.

  6. Click Next.

Step 2: Select fields and create conditions

  1. In the Select Fields & Create Conditions panel, select the checkboxes for the fields to include. The SOQL Statement text box on the right autopopulates as you make selections.

  2. To add clauses not supported by the field selector (such as ORDER BY, LIMIT, or subqueries), edit the SOQL statement text box directly. For SOQL syntax reference, see Salesforce's documentation on SOQL SELECT Syntax.

  3. To filter results, add a WHERE clause using the Object: Field, Operator, and Value dropdowns, then click the add link to append the condition to the clause. Repeat to add multiple conditions, inserting AND or OR between them.

    Tip

    To query only records modified since a specific point in time (the most common filter pattern in sync integrations), add a condition on LastModifiedDate using the >= operator and a project variable or global variable that holds the timestamp. For example:

    SELECT Id, Name, Email FROM Contact WHERE LastModifiedDate >= 2024-01-01T00:00:00Z

    Replace the literal date with a variable reference such as [last_sync_timestamp] so the value can be updated between runs without modifying the activity configuration.

    Note

    When using a global or project variable in a WHERE clause, set a default value for the variable before clicking Test Query. Global variables obtain their value at runtime, so the query validator requires a fallback value to test successfully. See Define a default value in Global variables.

  4. Click Test Query to validate the statement. The query must pass validation before the Next button becomes available.

  5. Click Next.

Step 3: Review the data schema

  1. Review the response schema generated from the query. This schema is used in the transformation in Part 2.

  2. Click Finished.

Part 2: Map records in a transformation

  1. On the design canvas, add a Transformation step after the Query activity, then add a target activity after the transformation.

  2. Open the transformation. The response schema from the Query activity appears as the source. When the transformation mirrors the Salesforce activity's schema, the source includes a records root node. This node is automatically set as a loop node so that each record in the result set is processed individually.

  3. Map the fields under the records node to the corresponding target fields.

For a full walkthrough of transformation mapping, see Transformation mapping overview.

Part 3: Alternatively, query from a script

Use scripting functions when you need to look up a value or a small set of records inline (for example, to resolve a parent record ID during a transformation, or to drive conditional logic in a script). Scripting functions log in to Salesforce automatically using the endpoint reference; no separate login call is required.

SfLookup

SfLookup returns the first field of the first record matching the query. Use it to resolve a single value, such as an account or contact ID:

accountId = SfLookup("<TAG>endpoint:salesforce/Salesforce</TAG>",
    "SELECT Id FROM Account WHERE Name='" + $account_name + "'");
If(IsNull(accountId), RaiseError(GetLastError()));

SfLookupAll

SfLookupAll returns a two-dimensional array of all records matching the query. Use it when you need to iterate over a result set in a script:

contacts = SfLookupAll("<TAG>endpoint:salesforce/Salesforce</TAG>",
    "SELECT Id, Name, Email FROM Contact WHERE AccountId='" + $account_id + "'");
If(IsNull(contacts), RaiseError(GetLastError()));
firstContactName = contacts[0]["Name"];

Fields can be accessed by name (contacts[0]["Name"]) or by zero-based index (contacts[0][1]).

SfCacheLookup

SfCacheLookup has the same signature as SfLookup but caches the result for the duration of the operation chain. Use it when the same lookup runs multiple times with identical parameters (for example, when looking up a static reference value once per transformation loop iteration):

orgId = SfCacheLookup("<TAG>endpoint:salesforce/Salesforce</TAG>",
    "SELECT Id FROM Organization LIMIT 1");

Subsequent calls with the same endpoint reference and SOQL string return the cached value without making another API call.

Verify the integration

Deploy and run the operation. Check the operation logs to confirm the Query activity ran successfully and verify that the record count in the target matches the expected result set from Salesforce. If no records are returned, check the WHERE clause conditions and confirm that any variables used in the clause have been assigned their expected values before the operation runs.