Skip to main content

Solutions

Products

Recommended products

Questions? We are here to help.
Contact us

Purchase

Questions? We are here to help.

Resources

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)
note
LOOKUP TABLE ORDER
If no table name parameter is supplied, the first table in the Lookup Table Order whose columns match those in the Select statement will be used. The order of lookup tables is the order by which they are arranged in the template document using the Lookup Editor. For templates which include/attach sub-templates the overall order is determined by the order in which the sub-templates are included/attached.
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.]
note
Column Names that contain certain characters will require quoting eg:
{Select 'Client Name'}.

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
 .
note
LOOKUP TABLE ORDER
If no table name parameter is supplied, the first table in the Lookup Table Order whose columns match those in the Select statement will be used. The order of lookup tables is the order by which they are arranged in the template document using the Lookup Editor. For templates which include/attach sub-templates the overall order is determined by the order in which the sub-templates are included/attached.
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
note
Column Names that contain certain characters will require quoting eg:
{Select 'Client Name'}

This article applies to:

  • PRODUCT: Contract Express

Related Content