Filtering the records retrieved from Salesforce

Use the filter element in the LocalEnvironment.Destination.Salesforce.Request.filter environment variable to restrict the records that are retrieved from the Salesforce system.

About this task

You can restrict the records that are returned by using the LocalEnvironment.Destination.Salesforce.Request.filter environment variable to specify a filter with a where, limit, or skip clause. You can also specify a filter with an order clause, and the records are returned in the specified order. The returned records appear as a JSON array in the location that is specified by the Output data location property of the SalesforceRequest node. By default, records are returned with all fields populated even if the field value is null. If a filter is specified with a field clause, only the specified field is returned.

The LocalEnvironment.Destination.Salesforce.Request.filter environment variable enables you to specify filter clauses, using child elements of the filter element:

filter
     where <value>
     limit <value>
     skip <value>
     order (1..n)
          <fieldname>/<value>
     field (1..n)
          <fieldname>/<value>

You can specify the child elements of the filter element to restrict the Salesforce records that are retrieved. The available elements are shown in the following table:

Table 1. Elements used for filtering the records retrieved from a Salesforce system
Element	Type	Description
where	string	A condition expression used to filter the set of returned records. The expression syntax is a Salesforce Object Query Language (SOQL) condition expression.
limit	positive integer	Imposes a maximum number of records in the returned set.
skip	positive integer	Removes the first `n` records from the returned set.
order	structure	One child, the name of which must match the name of a field of the record. The value is either `ASC` or `DESC` (case-sensitive) and is used to order the returned set. You can specify multiple order clauses, which are applied in the order in which they appear in the filter tree.
field	structure	One child, the name of which must match the name of a field of the record. The value can be either `true` or `false` (or `1` or `0`), and controls whether the field is included as part of each returned record. You can specify multiple field clauses.

For example, to retrieve the Name and Phone fields for 5 (limit) Contact objects in order of Name (ascending), Email (ascending), and Phone (descending), ignoring the first 2 (skip) records, specify the following statements:

SET OutputLocalEnvironment.Destination.Salesforce.Request.operation = 'retrieve';
SET OutputLocalEnvironment.Destination.Salesforce.Request.object = 'Contact';
SET OutputLocalEnvironment.Destination.Salesforce.Request.filter.limit = 5;
SET OutputLocalEnvironment.Destination.Salesforce.Request.filter.skip = 2;
SET OutputLocalEnvironment.Destination.Salesforce.Request.filter.order[1].Name = 'ASC';
SET OutputLocalEnvironment.Destination.Salesforce.Request.filter.order[2].Email = 'ASC';
SET OutputLocalEnvironment.Destination.Salesforce.Request.filter.order[3].Phone = 'DESC';

SET OutputLocalEnvironment.Destination.Salesforce.Request.filter.field[1].Name = true;
SET OutputLocalEnvironment.Destination.Salesforce.Request.filter.field[2].Phone = true;

You can also use filtering to retrieve a large number of records in manageable batches, by using repeated invocations of the SalesforceRequest node with different skip and limit values. For example:

/customers?filter[limit]=10&filter[skip]=0
/customers?filter[limit]=10&filter[skip]=10
/customers?filter[limit]=10&filter[skip]=20

In this example, each REST request returns ten records; the first request returns the first ten records, the second request returns the next ten records, and the third request returns the next ten records.