Home → Using LincDoc 3.0 → Field Attributes → Codelists
6.4. Codelists
Overview
LincDoc fields may be assigned to be either a codelist, or codelist-custom, field type. Codelists present themselves as drop down choices for the user to select one particular value. Custom codelists are exactly the same, but they also allow the user to type in a value that does not appear in the choice list.
Internally, 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 3rd party databases (currently Oracle, Microsoft SQL Server, MySql, and PostgreSQL).
NOTE: a 3rd party database can only be used if the Database Integration module has been purchased and configured.
Creating a new codelist
Open any form for editing. Go to the Fields/Sections tab. Select any field. In the right pane, click on the drop down list in the codelist(s) section; choose new. You are then prompted to enter an ID for this codelist, and you must select a type:
- Manual entry - this is the default and it will allow you to manually type in the values
- JDBC - presents an interface to choose codes/labels/descriptions from a 3rd party database
- JDBC Advanced - same as JDBC, but allows you to type in your own SQL queries
NOTE: the JDBC types can only be used if the Database Integration module has been purchased and configured.
Manual codelists
There are two types: simple and advanced.
Simple
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 yes/no codelist:
Advanced
Enable the advanced checkbox and you can now edit a description and a label:
At run time, this is what the user sees:
Conditional codelists
Conditional codelists allow a field to present a different choice list to the user based on one or more conditions. 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. To configure a conditional codelist, click on the advanced checkbox as shown here, then press the configure button:
Note the codelists behind a conditional codelist may be a mix of manual entry and JDBC.
Conditional codelists based on SQL query
There is an alternate method to create a conditional codelist. When creating the codelist, the type will be JDBC Advanced:
When you get to the configuration screen, choose your data source. Then type in your query. LincDoc field names can be used in the where clause to conditionally select data. Just delimit each LincDoc field with << >>, as shown below for field city.
Once you have the query typed in press refresh, if there are no syntax errors then you be able to map database fields to the code, label, and description attributes of the codelist. Note that you can re-use the same database column for both the code and label. Also note that mapping the description is optional.
A note about "where" clauses
The where clause is free to use any function supported by the underlying database; refer to your database's documentation for details. Here 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 LincDoc field "city", and it should match case insensitively: select * from dbo.companyinfo where lower(city) like '%' + lower(<<city>>) + '%'. So if the user had typed in for into field city, this query would return companies in Fort Worth, Rockford, New Hartford, Fort Lauderdale, etc.
As another example, consider a situation where you have a table of copier manufacturers, called manufacturers, with column manufacturer:
| manufacturer |
| Kanon |
| Tosheeba |
| Parasonic |
Then another table of manufacturers' copiers (table name copiers) with columns manufacturer and copier_model. This table relates manufacturers to their respective copier models:
| 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 |
Further assume you have a LincDoc eForm 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 LincDoc field copier_model. To select copiers based on the currently selected manufacturer the "JDBC advanced" codelist would use the query: select * from copiers where manufacturer=<<manufacturer>>
.