Home → Using LincDoc 3.1+ → Field Attributes → Using Codelists

9.5. Using Codelists

Codelists can be used to create drop-down lists from which form users can select various options. The options are typically defined by form administrators. However, you can also create codelists (the codelist-custom type) that allow users to type in additional options.

Proceed to one of the following sections below for more information:

About Codelists

LincDoc fields can be assigned one of two codelist types via the Field type attribute: codelist or codelist-custom.

When used in the Data Entry View, a codelist displays a drop-down list that contains defined choices, allowing users to select a single, specific value. Only the listed values can be selected.

A codelist-custom is exactly the same as a standard codelist, but it also allows a form user to manually enter a value that does not appear in the existing list of choices.

Internally, all codelists are database objects that consist of an ordered list of codes and descriptions of those codes. They may reside either inside LincDoc's internal database or in one of the supported third-party databases.

Note: A third-party database can only be used if the Database Integration module has been purchased and configured. For more information, contact LincDoc Technical Support.

Creating a New Codelist

When necessary, you can create a new codelist that will contain the needed choices for your field.

Open the desired form for editing using the Admin dialog box.
Click the Fields/Sections tab.
On the left side of the dialog box, select the appropriate field from the list of available fields.
On the right side of the dialog box, select codelist or codelist-custom from the Field type drop-down list.
Both of these options are configured using the same procedure. For more information on the difference between these two codelist types, see About Codelists.
From the Codelist(s) attribute's drop-down list, click new.

The New codelist dialog box appears.
In the ID text box, enter an name for this codelist, which will appear on the Codelist(s) drop-down list.
From the Type drop-down list, select one of the following options:
- Manual entry. Allows you to manually define the values that will be included in the codelist.
- Multi Value Field. Allows you to dynamically populate the codelist based on multi-value field(s) that exist within the current eForm or Document Package.
- JDBC. Allows you to choose codelist values from an existing, third-party database
- JDBC Advanced. Same as the JDBC option above, but allows you to type in your own SQL queries and use where clauses to specify conditional codelists.
Note: The JDBC types can only be used if the Database Integration module has been purchased and configured.
Click add.
Additional configuration options appear.
Proceed to one of the following sections below, based on the type of codelist you are creating:

Editing an Existing Codelist

You can edit any existing codelist directly from the Codelist(s) field attribute's drop-down list.

Open the desired form for editing using the Admin dialog box.
Click the Fields/Sections tab.
On the left side of the dialog box, select the appropriate field from the list of available fields.
On the right side of the dialog box, verify that codelist or codelist-custom is selected from the Field type drop-down list.
Expand the Codelist(s) attribute's drop-down list.
All existing codelists in your LincDoc environment are displayed.
Locate the codelist you want to edit, and click the edit button that appears to the right of the codelist's name and description.

A dialog box appears, displaying the current settings for the selected codelist.
Proceed to one of the following sections below, based on the type of codelist you are editing:

Defining a Manual Codelist

There are two types: simple and advanced.

Defining a Simple Codelist

Simple codelists are codelists which contain codes (or short text phrases) that have obvious meaning to the user, and therefore do not require a description. For example, a basic list of medical plans available for selection (as shown below).

Defining an Advanced Codelist

If you click the advanced check box, you can also add custom code for each entry (which is used internally by LincDoc to identify a selection) and edit a description for each entry. In a simple codelist, the code and label entries are the exact same. Descriptions appear immediately below the label in the codelist's drop-down list, providing additional information for each selection.

Some advantages of using separate codes and labels include the following:

From a database design perspective, it is often easier to use concise, short codes instead of potentially verbose labels.
It allows for the changing of a label, while not affecting the logic that is used to create reports (assuming some reports join up rows of data from different tables using the codes).

When the eForm or Document Package is run (the Data Entry View), the codelist displays both the label and description, as shown below.

Important: The code, not the label, will appear in the final, generated PDF document, as shown below.

However, if you want the description or label used in the final PDF instead of the code, the CodeListLabel and CodeListDescription DANG functions can be used to alter this default behavior. In addition, the functions may be combined with a generation calculation, if desired.

Defining a Multi-Value Field Codelist

You can dynamically populate the codelist based on multi-value field(s) that exist within the current eForm or Document Package. This type of codelist is specified using the Multi Value Field option from the Type drop-down list when creating a new codelist.

Perform one of the following actions:
- Create the new multi-value field codelist.
- Access an existing multi-value field codelist for editing.
The codelist dialog box appears, allowing you to define the codelist using a multi-value field.
(optional) Provide a description of the new codelist in the corresponding text box.
This description appears below the name of the codelist when selecting it from the Codelist(s) field on the Fields/Sections tab.
Using the Any field in source table drop-down list, select any existing multi-value field in the table that will be used to create the codelist. This option is used to determine the table for the codelist. Any field in the table can be selected.
(optional) Specify a condition if you want to execute one type of codelist (if the condition is true) and a different codelist under different (defined) circumstances.
For example, if your company has an expense report where you want to run a different codelist based on the employee type, you could define conditions so that a specific codelist is displayed for employees of type "SALES" and a different codelist to displayed for employees of type "ENGINEERING". Each employee type would use a different condition.
1. Click the Condition check box.
2. If necessary, click the line that bisects the Condition check box to view the related condition options (as highlighted below with a mouse pointer).
  
  The condition options are displayed in an expanded area.
3. Define a single condition or multiple conditions. For more information, see Creating and Editing Custom Conditions.
Click the refresh button.
(optional) Click the auto-map button, which, in most scenarios, automatically populates the code, label, and description drop-down lists using the first three rows in the selected table.
If necessary, specify the following information using the corresponding drop-down lists (if you did not use the auto-map button or if the auto-mapping is incorrect):
- code. The field that will be used to specify the information that will be stored in the database and generated documents. This entry will appear on the final, generated PDF document.
- label. The field that will be used to create the main entry seen in the codelist. This entry will appear in the corresponding drop-down list in the Data Entry View.
- description. The field that will be used to create a description of the main entry. This entry will appear below the specified label.
Click save.

Example: If "1" is specified for code, "blue" is specified for label, and "the color of the item" is specified for description: The corresponding drop-down list on the form would show "blue" as an option, with "the color of the item" appearing immediately below the "blue" selection. If "blue" is selected, the value of "1" would be submitted via the form and stored in the database.

Once the codelist is defined, it can be dynamically created using the specified fields directly in the corresponding eForm or Document Package.

Defining a JDBC Codelist

A JDBC codelist assumes that the table or view from which you want to extract codelist data already exists in your third party database. When the codelist is used, the data is extracted and inserted into the currently running LincDoc form.

Perform one of the following actions:
- Create the new JDBC codelist.
- Access an existing JDBC codelist for editing.
The codelist dialog box appears, allowing you to define the JDBC codelist.
(optional) Provide a description for the codelist in the corresponding text box.
This text will appear immediately below the codelist's name in the list of codelists.
From the Data source drop-down list, select the existing data source (database connection) that the codelist will use.
For more information on creating and configuring these connections, see Connecting LincDoc to an External Database.
From the Table/View drop-down list, select the table or view in the specified data source that the codelist will use.
Note: You can also upload data directly from a CSV file to a table using the upload CSV button or export information from the selected table to CSV file using the export CSV button.
From the Sort column drop-down list, select the column in the table that the codelist will use.
From the Sort direction drop-down list, select whether the data in the column will be arranged in ascending (A-Z) or descending (Z-A) order.
(optional) Specify a condition if you want to execute one type of codelist (if the condition is true) and a different codelist under different (defined) circumstances.
For example, if your company has an expense report where you want to run a different codelist based on the employee type, you could define conditions so that a specific codelist is displayed for employees of type "SALES" and a different codelist to displayed for employees of type "ENGINEERING". Each employee type would use a different condition.

Click the Condition check box.
If necessary, click the line that bisects the Condition check box to view the related condition options (as highlighted below with a mouse pointer).

The condition options are displayed in an expanded area.
Define a single condition or multiple conditions. For more information, see Creating and Editing Custom Conditions.

Determine if you want to allow offline caching. This option applies to forms that are enabled to run on LincDoc Mobile (an iPad). Because forms on an iPad may be run without having a WiFi signal (network connection), it would otherwise be impossible to run the codelist, since the codelist connects to a database somewhere on the network, requiring a network connection. When this option is active, LincDoc Mobile will download the entire table that corresponds to this codelist when form definitions are refreshed on the iPad and a network connection is present.
Important: Whenever a change is made to an eForm or Document Package (even a minor change, such as altering a field's label), LincDoc Mobile users must update their forms. This action also updates all offline-enabled codelists.

Tip: It is recommended that you not use this option with tables that contain more than approximately a few hundred rows.

Click the allow offline caching? check box.
Additional cache options appear.
Specify one of the following cache policies from the corresponding drop-down list:

- timed. Whenever you update forms on an iPad using information from the server, the offline codelists are refreshed. The act of refreshing starts the "timer" for each codelist. At the point that the codelist data is needed (for example, while running the form), this time is evaluated to determine whether the data for the codelist needs to be refreshed. If the iPad is online, the data will be refreshed and the timer reset before the codelist is run. If the iPad is not online (not connected to WiFi and the network), the codelist simply uses the existing offline data, and attempts to refresh the data the next time it is needed or the next time the forms are updated from the server.
- userSession. A timed codelist, as described above, with a pre-determined duration of 24 hours.

Click the refresh button.
(optional) Click the auto-map button, which, in most scenarios, automatically populates the code, label, and description drop-down lists using the first three rows in the selected table.
If necessary, specify the following information using the corresponding drop-down lists (if you did not use the auto-map button or if the auto-mapping is incorrect):
Note: You can re-use the same database column for both the code and label settings.
- code. The field that will be used to specify the information that will be stored in the database and generated documents. This entry will appear on the final, generated PDF document.
- label. The field that will be used to create the main entry seen in the codelist. This entry will appear in the corresponding drop-down list in the Data Entry View.
- description. (optional) The field that will be used to create a description of the main entry. This entry will appear below the specified label.
Click save.

Defining an Advanced JDBC Codelist

You can use Advanced JDBC codelists to specify options using values from an existing, third-party database. In addition, you can define your own SQL queries and use "where" clauses to specify conditional codelists.

Note: advanced JDBC codelists are not supported on iPad (LincDoc Mobile), except if the SQL query does NOT contain a WHERE clause.

Specifying Advanced JDBC Codelist Settings

Settings for an Advanced JDBC codelist are specified using the codelist dialog box.

Perform one of the following actions:
- Create the new JDBC Advanced codelist.
- Access an existing JDBC Advanced codelist for editing.
The codelist dialog box appears, allowing you to define the JDBC Advanced codelist.
(optional) Provide a description for the codelist in the corresponding text box.
This text will appear immediately below the codelist's name in the list of codelists.
From the Data source drop-down list, select the existing data source (database connection) that the codelist will use.
For more information on creating and configuring these connections, see Connecting LincDoc to an External Database.
In the large Query text box, enter your query. LincDoc field names can be used in a "where" clause to conditionally select data. Simply delimit each LincDoc field with << >>, as shown below for the field city.
Once you have fully defined your query, click the refresh button to check for syntax errors.
(optional) Click the auto-map button, which, in most scenarios, automatically populates the code, label, and description drop-down lists using the first three rows in the selected table.
If necessary, specify the following information using the corresponding drop-down lists (if you did not use the auto-map button or if the auto-mapping is incorrect):
Note: You can re-use the same database column for both the code and label settings.
- code. The field that will be used to specify the information that will be stored in the database and generated documents. This entry will appear on the final, generated PDF document.
- label. The field that will be used to create the main entry seen in the codelist. This entry will appear in the corresponding drop-down list in the Data Entry View.
- description. (optional) The field that will be used to create a description of the main entry. This entry will appear below the specified label.
Click save.

Using "where" Clauses in Advanced JDBC Codelists

The "where" clause can use any function supported by the underlying database. For more information, refer to your database's documentation.

Below is an example (building on the example shown above) assuming your database is Microsoft SQL Server. Suppose you want to select only those companies that are in cities whose name contains the string as typed by the user into the LincDoc "city" field. In addition, the entry should match case insensitively.

The following query can be used:

select * from dbo.companyinfo where lower(city) like '%' + lower(<<city>>) + '%'

With this query, if the user had entered for into the city field, the query would return companies in Fort Worth, Rockford, New Hartford, Fort Lauderdale, etc.

For another example, consider a situation where you have a table of copier manufacturers, called manufacturers, with column manufacturer, as shown below:

table: manufacturers
manufacturer
Kanon
Tosheeba
Parasonic

A second table exists of manufacturers' copiers (table name copiers) with columns manufacturer and copier_model. This table relates manufacturers to their respective copier models, as shown below:

table: copiers
manufacturer	copier_model
Kanon	K-310
Kanon	K-320
Kanon	K-330
Tosheeba	TS-5-A
Tosheeba	TS-5-B
Parasonic	Genesis I
Parasonic	Genesis II

Furthermore, assume that you have a LincDoc eForm or Document Package with fields of the same names as the database columns. The LincDoc field manufacturer would be set up as a simple codelist to the manufacturers table, and a JDBC Advanced codelist would be used for the LincDoc copier_model field. To select copiers based on the currently selected manufacturer, the JDBC Advanced codelist would use the following query:

select * from copiers where manufacturer=<<manufacturer>>

Creating Conditional Codelists

Conditional codelists allow a field to present a different list of choices to the user based on one or more conditions.

Note: You can also used Advanced JDBC codelists with "where" clauses to create conditional codelists.

For example, imagine a scenario where the field make asks the user to choose a particular copier manufacturer. Then the field model is a list of models from the manufacturer. The model's codelist depends on the copier manufacturer; it is conditional based on the value of make.

Verify that you have selected codelist or codelist-custom from the Field type attribute.
In the Codelist(s) attribute area, click the advanced check box.
Click the configure button that appears.

Note the codelists used as a conditional codelists can be of any type (manual, multi-value, JDBC, or advanced JDBC).
The Lookups for dialog box appears.
If you'd like to add a blank row to any codelist drop-down list, click the Add blank row check box.
(optional) Add a label, describing when the blank row should be selected, using the Blank row label text box.
In the condition column, specify the condition that you want to use for the first codelist.
In the codelist column, perform one of the following actions:
- Select an existing codelist.
- Add a new codelist.
If desired, click the + button to add another condition and corresponding codelist.
When you have completed your conditional codelist configuration, click save.

Deleting a Codelist

You can remove any existing codelist directly from the Codelist(s) field attribute's drop-down list. Once removed, the codelist will no longer be available for use with any eForm or Document Package.

Important: Since codelists can be shared across different eForms and Document Packages, care should be exercised when removing them. LincDoc does not scan and remove these codelists from all of your eForms and Document Packages in which they are referenced. As a result, execution errors will occur if any eForm or Document Package is executed that references a deleted codelist.

Open the desired form for editing using the Admin dialog box.
Click the Fields/Sections tab.
On the left side of the dialog box, select the appropriate field from the list of available fields.
On the right side of the dialog box, verify that codelist or codelist-custom is selected from the Field type drop-down list.
Expand the Codelist(s) attribute's drop-down list.
All existing codelists in your LincDoc environment are displayed.
Locate the codelist you want to remove, and click the delete button that appears to the right of the codelist's name and description.

A confirmation message appears.
Click Yes.
The codelist is deleted.
Save your eForm or Document Package.

This page was: Helpful | Not Helpful

← 27.3. Creating an Email Template