Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Lookup Functions

Select Operator (From Database Table)

Description 1 of 2 (select a list of text values from a column in an external source):

Select a list of text values found in the named return column of the named table which is defined in some external source.
The general form of this function is:
Select
return-column
From
 source_name
Where
 boolean-expression
where the boolean expression is optional.
Each individual comparison in the boolean expression takes the form:
comparison-column
Is
/
IsNot
/
IsLessThan
/
IsMoreThan
/
IsAtLeast
/
IsAtMost
/
SubString
 expression
which can be combined using the boolean operators
and
,
or
,
not
.
Lookups are commonly used:
  • to provide context-specific guidance on the questionnaire
  • to provide selection options using the Dynamic option
  • to retrieve data for the generated document based on answers given in the questionnaire (eg ClientAddress, CourtLocations)
Returns:
Text*
Parameters:
Parameter
Data Type
Description
1
Any
The name of a column.
2
Any
The name of the table in the external source.
3
Boolean
The boolean expression over columns in that table.
Examples:
When using functions inside a field, remember the field brackets:
{Select ClientAddress from Clients Where ClientName Is BuyerName}
Expression
Result
SELECT ClientName FROM Clients
where
Clients
 is a table/view/SharePoint list set up as an External DataSource in Administrator, containing the column
ClientName
all client names
SELECT ClientName FROM Clients WHERE 'Head Office Country' Is "USA"
where
Clients
 is a table/view/SharePointlist set up as an External Data Source inAdministrator, containing the column
ClientName
names of clients located in USA
Since Select returns a list of values (even when there is only one possible return value), when using Select statements as part of a business rule on a span, you sometimes need to use the First Function:
[
First(Select SalesTaxApplies from StateTable Where StateName is SellerState) is "Yes"
Sales Tax shall be added to this invoice.]

Description 2 of 2 (select a list of text values from multiple columns in
an external source):

Select a list of text values found in the named return columns of the named table/list which is defined in some external source.
The general form of this function is:
Select
 return-column-1, ..., return-column-K
From
 source_name
Where
 boolean-expression
where K>1 and the boolean expression is optional.
Each individual comparison in the boolean expression takes the form:
comparison-column
Is
/
IsNot
 /
IsLessThan
/
IsMoreThan
/
IsAtLeast
/
IsAtMost
/
SubString
 expression
which can be combined using the boolean operators
and
,
or
,
xor
,
not
.
Returns:
Text**
Parameters:
Parameter
Data Type
Description
1
Any*
The names of a list of columns.
2
Any
The name of the table in the external source.
3
Boolean
The boolean expression over columns in that table.
Examples:
Expression
Result
SELECT ClientID, ClientName FROM Clients
where
Clients
 is a table/view/SharePoint list set up as an External Data Source in Administrator, containing the columns
ClientID
 and
ClientName
all client ids and names
SELECT ClientID, ClientName FROM Clients
WHERE 'Head Office Country' = "USA"
where
Clients
 is a table/view/SharePoint list set up as an External Data Source in Administrator, containing the columns
ClientID
 and
ClientName
ids and names of clients located in USA

This article applies to:

  • Product: Contract Express

Related content