> ## Documentation Index
> Fetch the complete documentation index at: https://docs.abbyy.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Preparing vendor and business unit databases

> Prepare ODBC vendor and business unit databases for FlexiCapture data sets: pick unique keys, remove duplicate records, and use multiple-value columns.

## ODBC-compatible databases

FlexiCapture for Invoices lets you connect a data set to an ODBC-compatible database, and then either load its data once or update it from the external database at regular intervals. For more information, see [Using vendor and business unit databases](/flexi-capture/invoice-reader/ir-db-connection) and [Updating data sets](/flexi-capture/invoice-reader/ir-data-set-update).

Therefore, your first step is to store the external data in an ODBC-compatible database, such as Microsoft Access, Microsoft SQL Server, or Oracle.

## Eliminate duplicate records in the external database

First, determine which field in the external vendor (or business unit) database holds the value you need when the vendor (or business unit) is detected on an invoice. This field (or set of fields) should become the unique key of the data set.

The unique key of a business unit data set is the **Id** field (see [BusinessUnits data set](/flexi-capture/invoice-reader/ir-columns-bu)). From the user's point of view, this field is the result of detecting the business unit to which the invoice was issued.

The unique key of a vendor data set should be the value passed to the external information system when FlexiCapture for Invoices detects the vendor that issued the invoice. If one vendor table is used, the unique key must be associated with the **Id** field of the data set.

If you plan to process invoices issued to different business units of your company within one project, each of which has its own vendor database, associate the unique key of each business unit with the **BusinessUnitId** column of the **Vendors** data set, and the key of each vendor with its **Id** column. The unique key of the vendor record passed to the external information system when the vendor is detected is then the pair of **Id** and **BusinessUnitId** values (see [Vendors data set](/flexi-capture/invoice-reader/ir-columns-vendors)).

The explanations that follow consider only vendor detection, as the situation is identical for business units.

The unique key of a record must determine the unique combination of parameters used for vendor detection. Very often, an external information system considers a vendor record to be unique based on more parameters than are specified in the invoice or used for vendor detection.

For example, an external information system may expect to receive an **MCN\_USD** identifier if the vendor **My Company Name Ltd.** issues an invoice in US dollars, or an **MCN\_EUR** identifier if the same company issues an invoice in euros. Even though the currency is stated in the invoice (so you could tell when **MCN\_USD** rather than **MCN\_EUR** should be returned), currency is not used for vendor detection.

Therefore, the vendor detection mechanism cannot decide between **MCN\_USD** and **MCN\_EUR**.

In cases like this, create an **MCN** identifier that corresponds to both **MCN\_USD** and **MCN\_EUR** and that is returned when the vendor is detected. You can then create a rule in the Document Definition that chooses between **MCN\_USD** and **MCN\_EUR** depending on the currency of the given invoice.

To generalize, the unique identifier of a vendor record should have a unique set of parameters that are used for vendor detection, such as company name, company address, tax IDs (VATID, NationalVATID), and IBAN (see [Vendors data set](/flexi-capture/invoice-reader/ir-columns-vendors)). Only then can the vendor detection mechanism select the right vendor record for an invoice.

<Note>
  The program can then capture additional fields and use rules in the Document Definition to fine-tune the result and obtain the required value.
</Note>

If the same (or easily confused) set of parameters used for vendor detection corresponds to multiple unique keys, the program cannot choose only one key, vendor detection becomes haphazard, and the quality degrades.

The reason is as follows. If the program can use the data printed on the invoice to reliably detect the **My Company Name** vendor, but several records (**MCN1**, **MCN2**, ..., **MCNN**) in the data set match that data, the program may choose another record that is a worse match but does not have as many matching records. This ultimately causes the vendor to be detected incorrectly.

Consequently, it is very important to eliminate duplicate records and create a column in the external database that has a unique value for each unique combination of the record fields used for vendor detection.

When connected to a data set, rows with identical values in the column associated with the unique key are automatically collapsed into a single record.

## <a id="complexcolumns" />Multiple-value columns in a data set

A data set can store multiple values for one logical column of one company record.

Storing multiple values is necessary because company parameters such as the company name and street may vary from invoice to invoice (for example, "My Company Name" and "MCN Ltd."). To detect the company reliably, the text in the data set must closely match the text captured from an invoice image. Also, a company may have multiple bank accounts or other attributes.

<Note>
  Multiple-value columns in data sets should be used to store the same information written in different ways. For example, "Karl Marx Street" and "K. Marx str." are two different ways of writing the same address. However, company branches in London and Berlin should have two separate records.
</Note>

Values for multiple-value columns may be taken from an external database (for example, a table of vendors may contain one to five bank details for each company), or they may be added by the user during data capture (for example, an Operator may add to the data set a commonly used variant of a company name that is not known to the external database).

Data sets use column denormalization, that is, they store data in the following form:

| Unique key of data set \* | Name1                    | Name2     | ... | NameN           | ... |
| ------------------------- | ------------------------ | --------- | --- | --------------- | --- |
| 1                         | My Company Name          | MCN Ltd.  | ... | `<empty value>` | ... |
| 2                         | The Second Company, Inc. | S-Company | ... | `<empty value>` | ... |
| ...                       | ...                      | ...       | ... | ...             | ... |

For one logical field of a record, such as **Name**, multiple columns are created in the data set to store all the possible values of the logical field. Thus, **Name** can be termed a "complex column."

When connecting a table (or a view) from an external database, you can use column denormalization as described above, or the more common row denormalization. In the latter case, the rows that should be merged into one record must have the same value in the column that corresponds to the unique key. For example:

| Column corresponding to unique key in data set \* | Name                     | ... |
| ------------------------------------------------- | ------------------------ | --- |
| 1                                                 | My Company Name          | ... |
| 1                                                 | MCN Ltd.                 | ... |
| ...                                               | ...                      | ... |
| N                                                 | `<empty value>`          | ... |
| 2                                                 | The Second Company, Inc. | ... |
| 2                                                 | S-Company                | ... |
| ...                                               | ...                      | ... |
| N                                                 | `<empty value>`          | ... |
| ...                                               | ...                      | ... |

\* The unique key of a **BusinessUnits** data set is the **Id** field. The unique key of a **Vendors** data set is either the **Id** field or the pair of fields **Id** and **BusinessUnitId**, depending on the settings. For more information, see [Vendors data set](/flexi-capture/invoice-reader/ir-columns-vendors).