Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Lookup Functions

Select Operator (From Lookup Table)

Description 1 of 2 (select a list of text values from a lookup table):

Select a list of text values found in the named return column from a lookup table.
The general form of this function is:
Select
 return-column
From Lookup
 tablename
Where
 boolean-expression
where the table name and boolean expression parameters are 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 a lookup table
3
Boolean
The boolean expression over columns in a lookup table.
Examples:
When using functions inside a field, remember the field brackets:
{Select ClientAddress Where ClientName Is BuyerName}
Expression
Result
SELECT ClientName
all client names taken from the first lookup table containing the column
ClientName
SELECT ClientName WHERE 'Head Office Country'Is "USA"
names of clients located in USA taken from the first lookup table containing the columns
ClientName
 and
'Head Office Country'
SELECT ClientName FROM LOOKUP Customer WHERE 'Head Office Country'Is "USA" And 'Customer Type' Is ClientType
names of clients located in USA taken from the Customer lookup table that match the value of the ClientType variable
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 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 a lookup table):

Select a list of text values found in the named return columns of a lookup table.
The general form of this function is:
Select
 return-column-1, ..., return-column-K
From Lookup
 tablename
Where
 boolean-expression
where K>1 and both the table name and boolean expression are 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 lookup table
3
Boolean
The boolean expression over columns in a lookup table.
Examples:
When using functions inside a field, remember the field brackets:
{Select ClientAddress Where ClientName Is BuyerName}
Expression
Result
SELECT ClientID, ClientName
all client ids and names taken from the first lookup table containing the columns
ClientID
 and
ClientName
SELECT ClientID, ClientName WHERE 'Head Office Country'Is "USA"
ids and names of clients located in USA taken from the first lookup table containing the columns
ClientID,
ClientName
 and
'Head Office Country'
SELECT ClientID, ClientName FROM LOOKUP Customer WHERE 'Head Office Country'Is "USA" And 'Customer Type' Is ClientType
ids and names of clients located in USA taken from the Customer lookup table that match the value of the ClientType variable

This article applies to:

  • Product: Contract Express

Related content