Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Lookups and Expressions

Select Expression

A select expression is a SQL-like statement which relates columns in a lookup table, database table, database view or SharePoint list to values, returning a list of texts.
It takes the general form:
Select
column1
,
column2
From
source_name
Where
constraint
where
column2, source_name
 and
constraint
 are all optional.
If
,
column2
 occurs then each member of the return list is itself a list containing two texts (i.e. a list of lists), otherwise the expression returns a list of texts.
If
From
source_name
 occurs then it is an expression which selects values from an external data source (see
Contract Express Administrator
 for configuring external data sources), otherwise it is an expression which selects values from a lookup table.
If
Where
constraint
 occurs then only texts which satisfy the constraint are returned, otherwise all texts from that column are returned.

Example 1

Consider the lookup tables:
Office
Employee Number
Employee Name
Employee Gender
Boston
B1
Mr Bo A
Male
Boston
B2
Mr Bo B
Male
Boston
B3
Mrs Bo C
Female
Cambridge
C1
Mr Cam A
Male
Cambridge
C2
Mr Cam B
Male
Cambridge
C3
Mr Cam C
Male
Cambridge
C4
Mrs Cam D
Female
Cambridge
C5
Mrs Cam E
Female
Office Name
Office Address
Boston
10 Times Square, Boston
Cambridge
20 Times Place, Cambridge
The following select expressions reference the lookup tables above:
Expression
Result
Select 'Office Name'
List( "Boston", "Cambridge" )
Select Office
List( "Boston", "Boston", "Boston", "Cambridge", "Cambridge", "Cambridge", "Cambridge", "Cambridge" )
Distinct( Select Office )
List( "Boston", "Cambridge" )
First( Select Office )
"Boston"
Last( Select Office )
"Cambridge"
Count( Select Office )
8
Count( Distinct( Select Office ) )
2
Select 'Employee Number'
List( "B1", "B2", "B3", "C1", "C2", "C3", "C4", "C5" )
Select 'Employee Number' Where 'Employee Gender' = Gender
where the variable Gender has the value "Female"
List( "B3", "C4", "C5" )
Select 'Employee Number' Where 'Employee Gender' = Gender And Office = "Cambridge"
where the variable Gender has the value "Female"
List( "C4", "C5" )
Select 'Employee Number' , 'Employee Name' Where 'Employee Gender' = Gender And Office = "Cambridge"
where the variable Gender has the value "Female"
List( List( "C4", "Mrs Cam D" ), List( "C5", "Mrs Cam E" ) )

Example 2

If the External database contained a similar table named
Employees
 having columns named
Office
,
EmpNumber
,
EmpName
 and
EmpGender
 then the final expression in Example 1 above would be:
Select EmpNumber , EmpName From Employees Where EmpGender = Gender And Office = "Cambridge"
The above could also be used to obtain data from a SharePoint list that has been set up in Contract Express Administrator as an external data source called "Employees"

This article applies to:

  • Product: Contract Express