Rule type Query
Use this rule type when you want to find a value by using a query of Salesforce data. Generated SOQL is logged in the Guided Matching Log field, and the query execution time is logged as queryTimeMillis. If Guided Matching fails, check these logs. Slow queries can lower matching performance. Values above 500 are too high. Please refer to standard Salesforce query performance best practices to solve issues in this area.
You should only query indexed fields. Querying a non-indexed field significantly lowers search performance. To index custom fields, under the general options for the field, set External ID to true.
The queries can be defined for matching multiple dynamic criteria on transaction fields, where a field value on the query object is matched to a field value on the transaction record. For example, you can match transactions against installments that have the same contact and the same amount.
For query rules on Payment Profile, the new Primary Identifier field should be used.
Criteria against a transaction field are only available if the comparison operator is ‘equals.’ Only the first and mandatory criterion (Account = Account in the example below) is included in the generated SOQL query. All ‘value’ criteria (Status != “Collected” in the example screenshot below) are included in the generated SOQL query. However, other ‘field’ criteria (Amount = Amount and Originating Campaign = Campaign in the example) are not included in the generated SOQL query. These are filtered out by looping through the query result set.
Optimizing queries
Make sure you use the most specific comparisons first in your Query rule setup. This optimizes performance helps avoid potential Too Many Query Rows errors. The initial Where-clause is directly applied to the SOQL query, while subsequent clauses are applied to the initial query results.
Salesforce has a per-transaction governor limit of 50,000 for total number of records retrieved by SOQL queries. The Guided Matching Query rule type explicitly checks if the query returns more records than allowed by the Salesforce limit with the addition of LIMIT [Remaining Rows] to each generated query. If the number returned equals Remaining Rows, it is assumed that the governor limit has been exceeded, and the Transaction or Inbound Report records are set to Failed with a clear error message indicating the query limit was exceeded.
Query setup
| Setting | Description |
|---|---|
| Multi-Value? | The checkbox appears to the right of the first query criterion Transaction Field. If checked, the value in the Transaction Field is interpreted as a comma-separated list of values, and the query uses any of these values. |
| Multiple Results Strategy | Strategy for what to do if the query returns more than one field. With Guided Review, the transaction record goes to guided review. Users are shown a table with all resulting records and need to select one of the records. Take First means the first record found is automatically selected. No Result sets the rule status to ‘Done, No Result’ and the next rule is processed. If the Take All option is selected and there are multiple results, the query result is written in field “Multiple Installment Ids” as a comma-separated list of records, and the Installment lookup is left empty. If there is just one result, the Installment lookup is filled with the result and “Multiple Installment Ids” is left empty. |
| Single Results Strategy | Strategy for what to do if the query returns only one field. Guided Review sets the transaction record to go to guided review. Users are shown a table with all resulting records and need to select one of the records. Take Result means select the record that was found. |
| Object to Query, Return Field, Where and Order by Fields | Together these fields are used to form a SOQL query. Note that for the where clause, SOQL is not case sensitive. |
| Filter | A filter for the Object to Query drop-down list, which can be very long. This value is not saved in any way. |
| Result Fields to Display | Only if Multiple Results Strategy ‘Guided Review’ is selected. Use this field to define how the table in guided review is rendered. |