Design

Back Next

Setting Source Values: Query

Use a query to retrieve object information from all of the objects that are selected by the Where Clause.

Query controls appear in different steps and in some dialogs so what you may see may differ slightly.

What you will be doing is creating an SQL WHERE clause. While you can enter your condition directly into the text area, it is usually far easier to click the Add Condition
button and the Order By picklist. After Process Designer inserts this information, you can edit it if you need to. Letting Process Designer do the work is recommended as it is sometimes not obvious what the field's internal name is. The text entered here is a standard SQL WHERE clause. Also, this text does not include the WHERE keyword.

Each condition has four parts:

The name of a field in the object. In this example, the field is one of those contained within the object.

An operator that Process Designer uses when it compares the field value on the left with values on the right.

The kind of data that will be compared. Your choices are Content, Field, or Formula. If you choose Field, you are comparing the contents within the first field in the current object (the one on the extreme left of the Condition
dialog) with the contents of the field in the type of object being queried.

The data to which the field on the left is being compared. If the source is Content and you click within this area, a small icon appears to the right. After selecting it, Process Designer displays a picklist from which you can select the name of a field in another object that will be used when making the comparison.

Pressing the Order By
picklist button lets you select the fields that the query sorts upon when it displays information. For example, you might want to sort the displayed information by a person's last name. You can add additional keys by reselecting a field from this picklist. The Order By
clause is placed within the text box. This lets you edit it after it is inserted. You can also directly type the Order By clause here as well.

Note
: You can add an SQL LIMIT clause to your query by entering it in the Where clause text box. For example, you can limit the number of retrieved rows to 200 by typing "Limit 200". When adding this clause, be sure to add a space after the where information or place it on its own line after the where. ("Limit" can be in upper, lower, or mixed case.) By default, 100 rows are returned. If your guide requires more than 100, you must add this clause.

Specifying Conditions in a WHERE Clause

Your ability to define a condition within a WHERE clause depends upon what Produce Designer is interacting with.

Direct Interaction with a Relational Database: Most databases fully support the SQL WHERE operator. If you encounter problems, you should consult the documentation for that database.
Using an Informatica Connector: If you need more than one condition, only simple cognations (field operator values) are supported. You may join multiple conditions using either AND or OR. You can also use the NOT operator to invert the meaning of the condition.
Salesforce: The WHERE condition must conform to SOQL requirements. Consult the Salesforce SOQL documentation for more information.

Using a WHERE Clause When No Answer Is Expected

One common situation, and a situation that isn't at all unique to Process Designer, is that you are creating a WHERE clause that is based on a multiple criteria. However, not all of the criteria will have values. For example, an account and a territory, and the territory is optional. Setting up a WHERE clause such as

Account={$account) AND Territory={$territory}

wouldn't work if no territory value was used.

The solution for creating a WHERE clause with fields that are optional is to use the LIKE operator; for example,

Account LIKE '%{$account}%' AND Territory LIKE '%{$territory)%'

. If territory doesn't exist, this would be the same and entering

Account LIKE '%{$account}%' AND Territory LIKE '%%'

. The LIKE following the AND will always evaluate to true.

Setting Source Values

Download Guide

Watch

Comments

Cloud Application Integration Homepage