Skip to main content

​
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 and Updating data sets. 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). 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). 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). Only then can the vendor detection mechanism select the right vendor record for an invoice.
The program can then capture additional fields and use rules in the Document Definition to fine-tune the result and obtain the required value.
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.

​
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.
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.
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: 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: * 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.