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 SOAP 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 orchestrated 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 SOAP Adapter and Salesforce Adapter connections.
2. Create an app-driven orchestrated integration.
3. Drag the SOAP Adapter connection into the integration as a trigger connection.
4. Drag an assign action to the workspace next to the SOAP Adapter (for this example, named Init).
   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.
   
   Description of the illustration usequery_assign.png
   
5. Drag a while action below the assign action and set the condition to $done= ‘false’.
   
   Description of the illustration usequery_while.png
   
   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 (named Init). This is further updated dynamically with the Salesforce endpoint response in the second assign action (named Update).
   
   Description of the illustration usequery_mapper1.png
   
8. Drag the assign action next to the Salesforce Adapter (for this example, named Update).
   1. Update the local variables ($done and $queryLocator) with Salesforce output variables.

      • For the $done variable:

        
        
        Description of the illustration usequery_exp1.png
        

      • For the $queryLocator variable:

        
        
        Description of the illustration usequery_exp2.png
        

      • For the $countRecords variable:
        
        Description of the illustration usequery_exp3.png
        

        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 Successfully Completed” string as the status. The count of records available in the $countRecords variable are passed to outputString1 and outputString2, respectively.
   
   Description of the illustration usequery_mapper2.png
   
10. Update the tracker with any variable for tracking reference.
    The completed integration looks as follows.
    
    Description of the illustration usequery_orch.png