Writing SOQL for the Data Connector applet

The Data Connector applet cannot run all available SOQL functions, and certain limits are in place. To use SOQL in the Data Connector applet, you must take into account the following information. 

LIMIT clause

You must use the LIMIT clause in all SOQL queries to specify the maximum number of records that you expect your query to return. The exception to this rule is if your query contains only aggregate functions. This enables the Data Connector to prepare the data sources that store the returned values.

If your query contains only aggregate functions, LIMIT is not required as aggregate functions return single values. Using the LIMIT clause with aggregate functions is invalid SOQL.

You can specify a maximum of 10 in the LIMIT clause for performance reasons.

Field names and labels

In the SOQL query, refer to fields and objects using their names or API names as they appear in Salesforce. The resulting data sources use the corresponding labels for those fields and objects. For example, use NVMContactWorld__ACD__c in the SOQL query to return the value of the ACD field on a task (task) record. The corresponding data source is named Task|ACD.

Existing data sources and custom configuration settings in the query

You can include one or more placeholders in your SOQL query. The Data Connector applet replaces the placeholders with data source values or custom configuration settings retrieved at run time.

Specify a data source placeholder using the following format:

$(DataSource)

For example, SELECT firstname, lastname, email from contact WHERE id = '$(Prospect|ID)'

Specify a custom configuration setting using the following format:

#(AccountConfigKey)

For example,  SELECT firstname, lastname, email from account WHERE accountnumber = '#(CustomerAccountID)'

Unsupported SOQL clauses

You cannot currently use the following clauses:

• TYPEOF
• GROUP BY ROLLUP
• GROUP BY CUBE

Date aggregate functions are also not supported.

Telephone number format

The telephone number in the CLID data source is in E.164 format. Because SOQL queries only return exact matches, if you plan to use the CLID to retrieve data, ensure that your Salesforce data contains telephone numbers in E.164 format.

Highlighting and autocomplete

• The SOQL Editor highlights keywords (such as SELECT, LIMIT, FROM, and so on) in your query as you type.
• The SOQL Editor autocompletes data sources and custom configuration settings as you type. For example, if you type $ or # a list of available data sources or custom configuration keys appears.
• The SOQL Editor accepts standard key combinations, such as Ctrl+A, Ctrl+C, Ctrl+X, Ctrl+V, Ctrl+Z, Ctrl+Y. Ctrl+D deletes a line in SOQL Editor.

Created data sources

When you save a Data Connector containing a SOQL query, a list of the data sources created by the applet appears.