Retrieve the Name and IDs from Account Records with the queryMore Operation

If you need to fetch more than 2000 records from Salesforce.com, use the queryMore operation. If you enter an SOQL query that selects more than two custom fields of type long text and it does not return a required set of records with the query operation, you can use the queryMore operation.

This use case describes how to use the queryMore operation to retrieve the name and IDs for all account records from Salesforce.com. This use case uses the REST Adapter as the trigger to execute the integration.

The queryMore operation retrieves the next set of object records from the query operation.

The query function in SOQL retrieves a set of records and creates a server-side cursor that is represented in the queryLocator element. The queryMore operation processes the subsequent records in chunks and returns a newly-generated queryLocator. You typically call the queryMore operation repeatedly until all records in the result set have been processed.

The following important variables are used:

  • batchSize: A request header that represents the number of records returned for each Salesforce trigger. The default value is 200, the minimum allowed size is 200, and the maximum is 2000.

  • queryLocator: A value which points to the last record retrieved. Initially, it has no value. Therefore, you can initialize the local queryLocator with empty single quotes (‘’).

  • size: A variable that contains the number of records that have been retrieved in the previous iteration.

  • done: A boolean variable that indicates whether the source has more than the records you fetched. Its value is true if there are no records to fetch. Otherwise, it is false.

Note:

  • This application integration shows how to retrieve more than 200 records (when batchSize is set to 200) from Salesforce. You use the data by performing the required operation inside the while action loop. For validating, you can count the number of records returned for every Salesforce trigger.

  • A Salesforce Aggregate query does not support the queryMore operation.

  • While working with nested queries, if you use a queryMore call on a parent object, it invalidates all the child cursors in the previous result.

  1. Create REST Adapter and Salesforce Adapter connections.
  2. Create an application integration.
  3. Drag the REST Adapter connection into the integration as a trigger connection.
  4. Drag an assign action to the workspace below the REST Adapter.
    This assign action declares and initializes the done and querylocator variables locally.
    These variables are initialized with false and ‘’ (no value within the single quotes) values for done and querylocator, respectively. The countRecords variable counts the records retrieved and initialized to 0.
    This image shows the assign action. In the first row, the variable done is a data type with a value of false. In the second row, variable querylocator is a data type with a value of “ “. In the third row, the variable countRecords is a data type with a value of 0.0.

  5. Drag a while action below the assign action and set the condition to done= ‘false’.
    This image shows the While action. In the upper left is the Inputs sources section. In the section on the right, done is set to = “false”.

    The while action loop iterates if the value of done remains false.
  6. Configure a Salesforce Adapter endpoint.
    1. Drag a Salesforce Adapter as an invoke connection inside the while action.
    2. On the Basic Info page, enter an endpoint name (for this example, named SFDC_Demo).
    3. On the Action page, select Query Information.
    4. On the Operations page, select Query in the Select an Operation field, and Exclude in the Deleted and archived records field.
    5. Enter a SOQL query in the text box and click Test My Query to verify the input query.
      Select id, name, BillingCity from Account

      where Select id, name, BillingCity retrieves the ID, name, and billing city from the Salesforce Account object for all the records.

    6. On the Headers page, set the required batch Size (which allows a minimum value of 200) to the maximum value of 2000.
    7. On the Summary page, review the entered values.

    Note:

    The scope of the configured Salesforce Adapter endpoint is limited to the while action. Therefore, you cannot access the Salesforce input and output values outside the while action.
  7. In the mapper, map the local source querylocator variable to the target QueryLocator element of the request mapper. For every iteration, the input for the Salesforce endpoint is the queryLocator value, which is initialized with “” (empty value) in the first configured assign action. This is further updated dynamically with the Salesforce endpoint response in the second assign action.
    This image shows the mapper action. On the left side is the Sources section. Below this is the source tree of elements. The querylocator source element is mapped to the QueryLocator value in the Target section on the right side of the page.

  8. Drag the assign action below the Salesforce Adapter.
    1. Update the local variables (done, querylocator, and countRecords) with Salesforce output variables.
      For the done variable:


      This image shows the done element mapped to a value of done and the querylocator element mapped to a value of queryLocator.

      For the querylocator variable:


      On the left side is the Input sources section. Below this is the Source element tree. The queryLocator element is mapped to the queryLocator value on the right side.

      For the countRecords variable:


      On the left side is the Functions section. Below this is the Source element tree. The records source element is mapped to the Expression field. The value of countRecords is mapped to the value of count(records).

      The count() function is available under Functions, categorized in Node-set. This takes Node-set as input and returns the count of nodes available in the Node-set. The record Node-set is passed to the count (). After mapping, the function call looks as follows: count($SFDC_E/nsmpr3:queryResponse/nsmpr3:QueryResults/nsmpr3:records)

  9. In the mapper, map the response. Enter an “Operation is Successful” string as the status. The count of records available in the countRecords variable are passed to OutputString1 and OutputString2, respectively.

    This image shows the mapper action. Below this is the source tree of elements. The countRecords source element is mapped to OutputString1 and OutputString 2 in the Target section on the right side of the page. At the bottom is the Expression Builder section. OutputString1 has a text value of "Operation is Successful".

  10. Update the tracker with any variable for tracking reference.

    The completed integration looks as follows.


    This image shows the completed integration. From beginning to end, this integration consists of a REST Adapter, assign action, while action that includes a map action, Salesforce Adapter action, and assign action), and map action.