Creating lookup tables in the template
Lookup tables provide a way of storing data that can be referenced directly or mapped from the answers that a user has given on a questionnaire. They can be used to store all of the options for a text selection variable, or to link one value to another. For example, if a user has selected a geographical location, that answer can be used to find other data (such as the currency or capital city) for that location. This data can be presented on the questionnaire or in the assembled document.
Lookup tables can be found in the Tables section of the Dictionary Editor.
Creating a new lookup table
To create a new lookup table, select the new icon 
from the toolbar then select New Lookup Table from the drop down menu. You will then be asked to enter the number of rows and columns for the lookup table; these can be edited at a later stage.
from the toolbar then select New Lookup Table from the drop down menu. You will then be asked to enter the number of rows and columns for the lookup table; these can be edited at a later stage.
Once you click OK a new lookup table will be created and you will be able to give the lookup table a name.
The column headers can be edited by right clicking on the column header and selecting the Rename option. Alternatively, double clicking on the column header will also give you the option to edit the column name.
To insert additional columns, right click on the column header and select either Insert Before to insert a column before the currently selected column or Insert After to insert a column after the currently selected column. Similarly, to delete a column, right click on the column header and select Delete.
Rows can be added or deleted in a similar fashion. Right click on a row and select either Insert Before or Insert After to add additional rows. Select a single row or multiple rows by clicking on a row and dragging the mouse, then select Delete to delete the row(s).
Adding data to a lookup table
You can begin adding data to the lookup table as soon as it has been created by selecting the cell you require and typing the content.
You can also apply formatting to the data by selecting the text within a cell and clicking the 
icon in the toolbar above the lookup table. This will open the html editor window and will apply the html code into the cell. Note that the formatting applied within a lookup table will be displayed on the questionnaire when the lookup data is included in Active Text. The formatting will not be displayed on the questionnaire if the lookup data is used within the selection of a variable of type text selection.
icon in the toolbar above the lookup table. This will open the html editor window and will apply the html code into the cell. Note that the formatting applied within a lookup table will be displayed on the questionnaire when the lookup data is included in Active Text. The formatting will not be displayed on the questionnaire if the lookup data is used within the selection of a variable of type text selection.
Multiple cells can be copied from an existing Microsoft Excel spreadsheet and pasted into the lookup table. To do this, select the relevant cells in Microsoft Excel and either right click and select copy or use CTRL + C. Within the lookup table select the paste button 
. Formatting that has been applied within a cell in Excel will be carried through to the lookup table.
. Formatting that has been applied within a cell in Excel will be carried through to the lookup table.You can also copy data from a lookup table by clicking the copy button in the toolbar 
. It can then be pasted into a new lookup table or an Excel spreadsheet.
. It can then be pasted into a new lookup table or an Excel spreadsheet.Dynamic data definition
To use external data within the lookup table select the Dynamic checkbox. The table will change into a box where you can enter a select expression against an External Table that has been defined in the Contract Express web application.
Dynamic lookup tables are evaluated once and the result is cached so that subsequent lookup or select expressions against the lookup table do not require the underlying database to be accessed again during that request.
For example, consider a database table named
US States
containing: State Code | State Name | Capital City | Time Zone |
|---|---|---|---|
AL | Alabama | Montgomery | Central |
AK | Alaska | Juneau | Hawaii |
AZ | Arizona | Phoenix | Mountain |
AR | Arkansas | Little Rock | Central |
To link a lookup table to this database, use the following select expression:
Select 'State Code', 'State Name', 'Capital City', 'Time Zone' From 'US States'
The column names of the lookup table will be the column names of the database table. Note: the single quotes are needed as there are spaces in the table name and column names.
Find a lookup table
To search for a specific lookup table in the list click the Find icon
in the Dictionary Editor toolbar. This will open the Find pane above the list of lookup tables. With Current List selected in the Find Where dropdown the search will be performed against the list of lookup tables as displayed on the left.
in the Dictionary Editor toolbar. This will open the Find pane above the list of lookup tables. With Current List selected in the Find Where dropdown the search will be performed against the list of lookup tables as displayed on the left.
Find content within a lookup table
The Find pane also enables users to search for a string of text within a selected lookup table and to replace that text with new text. Select Selected Item from the Find Where dropdown. Enter what you want to find in the Find What text box and enter the replacement text in the Replace With text box. If you do not want to replace the text, simply leave the Replace With text box empty to only do a search of the lookup table.
Add notes to a lookup table
Author notes can be added to a table via the Notes tab. These notes are applicable to the currently selected table.
Using data from a lookup table in your template
For examples of how to reference lookup data in the template, see Select Operator (from Lookup Table) (full form expression) and [Dot] Operator (abbreviated expression).
Using a lookup on a questionnaire
Using lookups to provide the options for text selection variables
Lookups can provide the selection options for text selection variables. This is particularly useful when the selection list is large or is re-usable across many variables.
For example, the dynamic options of a select variable
TargetTimeZone
: Distinct( Select 'Time Zone' )
Note the use of the function
Distinct
. This is necessary because the lookup table contains duplicate entries. All unique entries from the column Time Zone in the above lookup table will be added as selection options for the TargetTimeZone variable.Filtering the values of a lookup
Dynamic lookups also offer a way of varying the selection options offered, based on an answer to an earlier question.
For example, the dynamic options of a select variable
TargetState
which is restricted to those states in the Sometimes it is useful to display selection options that differ from the values you wish to store for a variable
: Select 'State Name' Where 'Time Zone' Is TargetTimeZone
Using lookups to display different selection wording to the answer values
Sometimes it is useful to display selection options that differ from the values you wish to store for a variable.
For example, you may wish to use a short code as the answer for a US State, but display the full name of the State for a user to choose from. To achieve this for the
TargetState
above its dynamic options are defined as: Select 'State Code', 'State Name' Where 'Time Zone' Is TargetTimeZone
When the select expression references two column names the first column represents the actual value of the variable and the second column represents the options that appear on the questionnaire. If a questionnaire user chooses Arizona, for example, the value of the
TargetState
variable will be "AZ".This means not only will this value go into the generated document but also if you have other business rules referencing the answer to this variable they must use the first column you state in the Select statement too:
[
TargetState IS "AZ"
This Agreement is subject to the laws of {TargetState.'State Name'} ({TargetState})]Using lookups to display information on a questionnaire
A lookup provides an easy way to make the text on a questionnaire page change automatically (see active text), based on a questionnaire user's answers. It can be used in prompts, guidance, page titles or group titles. You can use either the full form or abbreviated form of the lookup (as above).
For example for the Guidance of the Jurisdiction question we might want to remind the user of the currency that is going to be used for the Agreement as a result of their selection. Therefore in the Guidance area of the Jurisdiction variable's attributes we might have:
The Currency of this Agreement is {Select CurrencyName Where Country Is Jurisdiction} ({Select CurrencySymbol Where Country Is Jurisdiction}).
Create lookup table as external table from dictionary
Lookup tables can now be configured as external tables to enhance flexibility in data management. This is particularly useful when you need to maintain separation between a lookup table and the template where it will be used. By setting up lookup tables in a separate template, users can model external table functionality, supporting a modular design and ensuring data integrity and functionality across various components.
To configure a lookup table as an external table, simply navigate to the desired lookup table and click the "Setup as External Table" button. This action streamlines the setup process, and the system immediately confirms the successful creation of the external table. This feature simplifies lookup table management and improves the handling of complex data structures efficiently.
note
Starting with version 10.6, local lookups can be used as external tables via the admin tab in the CE interface.
This setup thus offers several advantages compared to traditional local lookup tables:
- More security:If your template is used by external users, this offers additional security. The data of external tables is never fully loaded into the browser.
- Efficient presentation: This setup supports the searchable list presentation for single-select variables which returns results based on the search term entered by the user. The search term will be used to filter results, returning entries that contain the term with matching case sensitivity.
- Easier template drafting and troubleshooting: This setup can simulate the external table functionality during early template drafting stages or when troubleshooting a template without access to the relevant external data source. The external table using a lookup from a template can be easily replaced with an external table using a source outside of Contract Express and vice versa.
Related Content
-
Accessing external data in the questionnaire
Format:
Article,
4 min read