Skip to main content
Getting Started
Search
Search Contract Express Help and Support.

This article applies to:

  • Product: Contract Express

External Tables

This guide explains how to connect to and use
External Tables
 in Contract Express, both in the Contract Express site and in the Author tool in Word. The two most common connection types,
SQL
 and
OData
, are covered in detail, but the same principles apply to other external table possible connections.
note
The External tables configuration will prevent some examples from working. You can replicate the methodology in your environment using the guidance below.
If you are not familiar with the Lookup functionality or SELECT statements, see Lookup Functions for an introduction to these topics.

Connecting to a SQL Database

SQL stands for Structured Query Language and can refer both to a database itself, and the language which controls queries made to the database. In this article, we will focus on how to utilise the Contract Express External tables connection with a SQL database and how to select and refine information using the Author tool.
First, let's configure the external table:
  • External table name
    : Choose an appropriate, unique, name; this example uses "Customers".
  • Data provider
    : Select SQL.
  • Row limit
    : Choose the maximum number of data rows you wish to be returned. Use
    0
     to return all rows as the example below.
  • Server
    : Enter the server string as specified in the SQL and MySQL data provider section.
  • Target
    : Enter the target as specified in the SQL and MySQL data provider section.
There is not a publicly available example environment to demonstrate the SQL functionality, however, please contact Support if you would like a live run through of this functionality.
note
Authors can target SQL
views
 instead of individual tables. This is useful if you need to query data spread across multiple tables without setting up a separate connection for each, and it allows WHERE statements to work properly, since they don't function across separate tables.
Once the required data has been collected, you can begin creating the Contract Express variables. Start by creating a
Text Selection
 variable to present the user with an initial set of options in the questionnaire. Before defining the selection values, select the
Dynamic
 checkbox. Then, enter the
Select
 statement using the following format:
SELECT ColumnName FROM TableName
The
TableName
 is the name of the External table connection configured in the Admin portal of Contract Express.
note
Any spaces in a column or table name should be enclosed with single quotes. In the example below, there are no spaces so this is not needed, otherwise the presentation would be
Select 'Customer Name'
.
If a large selection should return, consider using a Searchable list presentation in your text selection variable. This provides a
search as you type
 functionality within the questionnaire. For example,
{CustomerNameCES}
You can then refine the required data based on the initial selection. Additional information can be captured through further
Text Selection
 variables if it needs to be displayed within the questionnaire, or by allowing users to enter their own values using the
Other
 option. Alternatively,
Computables
 can be used to process the selected information for use within the template document or guidance content, as demonstrated in this example.
The data is refined by adding a
WHERE
 clause to the initial
SELECT
 statement.
SELECT ColumnName FROM TableName WHERE InitialColumnName IS InitialVariableName
For example, if you wish to retrieve a customer's address, the following query returns the value stored in the
CustomerAddress1
 column for the customer identified by the
CustomerNameCES
 variable:
Select CustomerAddress1 from Customers where CustomerName is CustomerNameCES
The result is then stored in a Contract Express variable:
{CustomerAddressCES}
You can use the same pattern to retrieve other values from the same record by selecting a different column while keeping the same filter condition. To retrieve customer's city
{CustomerCityCES}
, use the following statement.
Select CustomerCity from Customers where CustomerName is CustomerNameCES
This approach can be extended to retrieve any field from the
Customers
 table, provided that the
WHERE
 clause uniquely identifies the required record.

Connecting to OData

OData (or Open Data Protocol) is an application-level protocol for interacting with data through RESTful interfaces, letting applications focus on business logic without worrying about the various API approaches to define request and response headers, status codes, HTTP methods, URL conventions, media types, payload formats, query options, etc.
In this example, we will be using the public
Northwind
 sample dataset as an example:
Let us now configure the new external table:
  • External table name
    : Choose an appropriate, unique, name; below example uses "OData Customers".
  • Data provider
    : Select OData.
  • Row limit
    : Choose the maximum number of data rows you wish to be returned; to return all rows set this to 0; this example does this.
  • Server
    : Enter your OData source URL; this example uses the Overview below.
  • Target
    : Specify the target collection within the OData source; this example uses "Customers".
The Target in the External Table connection above refers to a Collection.
Each
Collection
 contains sub section of information that corresponds to the “Column Names” you would usually target when working with a more traditional table format., similar to the column names in a traditional table structure.
Select the Property Name you want to return from within that Collection, e.g, CompanyName for a list of Company Names, and point the Select statement to your external table connection name.
{CustomerCE}
Select CompanyName from 'OData Customers'
As with SQL, you can filter additional information using a WHERE clause, either in a computable or as a further selection variable. For example,
{ContactNameCE}
.
Select ContactName from 'OData Customers' where CompanyName is CustomerCE
or in a further selection variable option, for example,
{ContactCityCE}
,
Select City from 'OData Customers' where CompanyName is CustomerCE
The
Other
 option can also be used as in the above example if you want the users to be able to input their own answer instead of the information drawn through.
To bring in data from another collection, repeat the setup process with a new external table connection and SELECT statement. For example,
{EmployeesCE}
,
Select FirstName from 'OData Employees'
For more information about OData, refer to the following resources:

Additional Information

For information about connecting additional external sources, see Managing external tables