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"