Home → Using LincDoc 3.1+ → Configuring Storage Repositories → SQL Repository: Mapping Multiple Fields to a Single Column
12.10. SQL Repository: Mapping Multiple Fields to a Single Column
Important: This topic contains advanced user material. If you need further assistance, or are unsure about how to proceed, contact LincDoc Technical Support.
It is possible to map multiple fields to the same column in a table. This action requires writing multiple rows in the table per document. To complete this task, a multi-mapping key column needs to be added to the table. This column should not be confused with the id system column. Multiple distinct values are set for the multiple rows written to the database, and each field mapped to another column in the table corresponds to one of the distinct values.
Note: It is also possible to write multiple rows in the table per multi-row table record.
About the Example
In order to better understand this topic, it is important that you understand the example that is being used.
In this example, suppose a Document Package collects a residence's address and a mailing address with the following fields and associated values:
- address1: 123 Main St.
- address2: Apartment 4B
- city: Springfield
- state: NY
- zip: 12345
- mail_address1: PO Box 7
- mail_address2:
- mail_city: East Springfield
- mail_state: NY
- mail_zip: 12346
Using this information, a table can be created with the following columns:
- id
- version
- group_name
- row_index
- type
- address1
- address2
- city
- state
- zip
The type column is the multi-mapping key column, and its values are residence and mailing. Using the type column as a filter, a single lookup can be created to retrieve either the physical address or the mailing address. Normally, two separate lookups would be needed. In addition, if more address types are added (such as home, office, etc.), the structure can support them without the need for even more lookups.
The address1, address2, city, state, and zip fields are mapped to the appropriate database columns that correspond to the residence value of the type column.
The mail_address1, mail_address2, mail_city, mail_state, and mail_zip fields are mapped to the appropriate database columns that correspond to the mail value of the type column.
The full table, including specified values, would appear as follows:
|
id |
version |
group_name |
row_index |
type |
address1 |
address2 |
city |
state |
zip |
|
1 |
1 |
-1 |
residence |
123 Main St. |
Apartment 4B |
Springfield |
NY |
12345 |
|
|
1 |
1 |
-1 |
|
PO Box 7 |
East Springfield |
NY |
12346 |
Search index fields should be mapped corresponding to only the first three multi-mapping key column values in the first table.
For example, if you have residence, mailing, work, and alternate as your four address types, the search will only use the residence, mailing, and work address types. The fourth type (alternate) will not be used in the search.
If you are using multiple mapping, you should contact your local database administrator for assistance. Although LincDoc attempts to make this configuration easy to understand, without the proper knowledge you can easily corrupt your database.
Accessing the Multiple Mapping Option
In order to use multiple mappings, you first need to activate the general settings. Once that is done, you need to specify the multi-mapping key column for each table in your repository.
- Access the repository's Configure dialog box as described in Creating a Repository.
- Click the Options tab.
- Select the Allow mapping multiple fields to a single column check box.
- Click the mapping tab.
Notice that each table in your repository now displays a multiple mappings check box (immediately below the table's name). - Click the multiple mappings check box for the desired table.
A Multi-mapping key column drop-down list appears to the right of the multiple mapping check box. - Select the multi-mapping key column from the corresponding drop-down list.
Additional options appear near the top of the table, and in the corresponding row.. - Add multiple-mapping fields using the + buttons.
- Once multiple-mapping fields have been added, values can be entered for the key column values (these values must be distinct), and LincDoc fields can be mapped to each entry.
In the following example, three values have been added to the type column. - (optional) Delete any unneeded multiple-mapping fields using the - button for the appropriate field.
For the example described in About the Example above, the final address mappings would appear as shown below.