Skip to main content

Solutions

Law firms

Tax, audit & accounting firms

Corporations

Governments

Products

Legal

Trade & supply

Tax, audit & accounting

Corporate tax

Risk & fraud

Books

Recommended products

Questions? We are here to help.
Contact us

Purchase

Buy solutions

Buy books

Contact sales

Questions? We are here to help.

Resources

Insights

Events

Product training

Product communities

Developers

Lookup Functions

Select Operator (From Lookup Table)

The Select operator retrieves a list of text values from a lookup table. It can return either a single column or multiple columns at once.

Select single column from a lookup table

The general form is:
Select
 return-column
From Lookup
 tablename
Where
 boolean-expression
Both the
From Lookup table name
 and
Where boolean-expression
 parts are optional.
When selecting a single column, the expression returns
Text*
 (a list of text values).
Examples:
Expression
Result
SELECT ClientName
All client names from the first lookup table containing the column
ClientName
SELECT ClientName WHERE 'Head Office Country' Is "USA"
Names of clients in USA from the first table containing
ClientName
 and
'Head Office Country'
SELECT ClientName FROM LOOKUP Customer WHERE 'Head Office Country' Is "USA" And 'Customer Type' Is ClientType
Names of clients in USA from the Customer table matching the value of the ClientType variable

Select multiple columns from a lookup table

The general form is:
Select return-column-1, ..., return-column-K From Lookup tablename Where boolean-expression
where K>1, and both the
From Lookup table name
 and
Where boolean-expression
 are optional.
When selecting multiple columns, the expression returns
Text**
 (a list of lists of text values).
Examples:
Expression
Result
SELECT ClientID, ClientName
All client IDs and names from the first table containing both columns
SELECT ClientID, ClientName WHERE 'Head Office Country' Is "USA"
IDs and names of clients in USA from the first matching table
SELECT ClientID, ClientName FROM LOOKUP Customer WHERE 'Head Office Country' Is "USA" And 'Customer Type' Is ClientType
IDs and names of clients in USA from the Customer table matching the ClientType variable
note
Column names that contain spaces or certain characters must be enclosed in single quotes.
For example: Select 'Client Name'
Each individual comparison in the boolean expression takes the form:
comparison-column
Is
/
IsNot
/
IsLessThan
/
IsMoreThan
/
IsAtLeast
/
IsAtMost
/
SubString
 expression
Comparisons can be combined using the boolean operators
And
,
Or
,
Not
, and
Xor
.
Parameter
Data Type
Description
1
Any / Any*
The name of a column, or a list of column names (when selecting multiple columns)
2
Any
The name of the lookup table (optional)
3
Boolean
The boolean expression over columns in the lookup table (optional)
Select Operator is commonly used to:
  • Provide context-specific guidance within a questionnaire
  • Populate selection options using the Dynamic option
  • Retrieve data for the generated document based on questionnaire answers (e.g. ClientAddress, CourtLocations)
note
  • In
    Author Desktop
    , Select expressions used inside a field must be wrapped in curly brackets or any other brackets, for example:
    {Select ClientAddress Where ClientName Is BuyerName}
    .
  • In
    Author Online
    , curly brackets are not required, and the expression can be written directly without them in the Expression Editor.

Lookup Table Order

If no table name is specified, Contract Express automatically uses the first table in the
Lookup Table Order
 whose columns match those referenced in the Select statement.
The Lookup Table Order is determined by the arrangement of tables in the template document as configured in the Lookup Editor. For templates that include or attach sub-templates, the overall order follows the order in which those sub-templates are included or attached.

Using Select in Business Rules

Since Select returns a list of values, even when only one result is possible, you may need to wrap it with the First Function when using it in a business rule on a span. For example:
[
First(Select SalesTaxApplies Where StateName is SellerState) is "Yes"
Sales Tax shall be added to this invoice.]
This ensures the comparison is made against a single value rather than a list.
note
Above expression is only applicable for Author Desktop. In Author Online, this expression can be written directly without the square brackets in the Expression editor.

This article applies to:

  • Product: Contract Express

Related Content