Database Check rule

A database check of field values involves comparing a recognized field value with values in a database table. If a captured field value is found among the values in the database, the check is passed. Otherwise, the rule returns an error. You can use this rule to check if there is an entry for a captured value in your database, to carry out fuzzy searches in your database even if there are typos or errors in fields, to replace a field value with its corresponding record from the database, and to copy certain values from the database into document fields.

​

Creating a Database Check rule

To create a database check rule:

1. Open the field properties dialog box (right-click the field to open its shortcut menu and select Properties…) or the Document Definition properties dialog box (from the Document Definition editor menu, select Document Definition → Document Definition Properties…).
2. Go to the Rules tab.
3. Click the New Rule… button.
4. From the list of rule types, select Database Check and click OK.
5. Type the name of the rule.
6. Disable the Show rule name in message to verifier option if necessary. If this option is disabled, the error message will be displayed as “error message” instead of “name of rule: error message”.
7. Specify the severity of the rule.
8. If required, specify the conditions under which the rule must be applied (by default, a rule is applied unconditionally). See Conditions for applying rules for more information.
9. Specify tags for the rule.
10. Disable the Enabled rule option if necessary. The rule will be created, but will not be applied until this option is enabled by a user.
11. Click Next >.
12. Set up a connection to the data source by specifying its type and connection parameters.
    The following data sources are supported:

• External Database (Simple) Connection to the external database is set up for each rule in the Document Definition.
• External Database (Variable) Connection to the external database is set up only in the properties of the project (Project Properties → Environment Variables). This type of data source can be simultaneously used for multiple rules. If you use a different database for the project, you will need to modify the connection settings only in the project properties.
• data set Connection to the data set is set up by the user in the properties of the Document Definition (Document Definition Properties → Data Sets).
  Tip. We recommend selecting the following as OLE DB Provider:
• When exporting to SQL, prefer OLE DB Provider for ODBC Drivers
• When exporting to Oracle, prefer Oracle Provider for OLE DB (For details see Exporting to an Oracle database)
  Note: To avoid entering the password each time you connect to the database, select the Allow saving password option or set up the database so that it requires no password to connect to it (for example, you can use Windows authentication).

13. Select the required schema and database table from the drop-down lists or specify the required data set if you are setting up a connection to a source of type data set.
    Note: If the Find in all schemas is selected as the scheme and the specified table is not included in the default scheme, the following error message may occur:
    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name K1. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
    In the event of the error message, the name of the schema that contains the table should be specified explicitly.
14. Select the Don’t check if all fields are empty option to omit rule checks if there are no values in the fields involved in comparison. This will speed up rule validation.
15. Establish links between the fields in the Document Definition and the fields in the database (see Specifying fields to compare below).
16. Use the Add…, Edit… and Delete buttons to add, edit or remove links between fields.
17. If you selected a Data Set as the source, you can now select the field that will act as the primary key from the Field where to save record ID drop-down list.

​

Specifying fields to compare

In the Field links group on the Rule Settings tab, you can see the already specified field comparisons. You can set up a Database Check rule either for a single field or for a set of fields. When you add or modify a fields to compare, you must specify the following:

• Document field The field of the Document Definition that is to be compared with a database field
• Database field The field in the database that is to be compared with the document field
• Search Specifies the type of comparison:
  • Exact The value in the document field must exactly match the value in the database field (note, however, that searches are not case-sensitive)
  • Fuzzy The value in the document field may only partly match the value in the database. Only text fields can be used in this type of comparison.

Fuzzy searches will find the closest matches, i.e. words where the minimum number of letters must be added, removed or replaced to achieve a match. The number of allowed errors depends on the length of the original word. If a fuzzy search finds more than one matching value, the following error message is displayed to the Operator: Too many records found.. This error message is accompanied by the list of close matches from the database so that the Operator can select the correct value from the list. The following additional options are available:

• Don’t search if field is empty If the field is empty, its value will not be compared with values from the database.
• Enter value from database If a fuzzy search finds a matching value in the database, this value may be automatically entered into the field if:
  • the values in the field and in the database are different
  • the field is empty.
    Note: In the case of exact searches, selecting the Enter value from database option makes no sense, as the two values will be identical.
• Allow empty values in database Depending on the results of the database check and other settings, this option will have the following meaning:
  • if the database field is empty and the document field is not empty, the field and database values are deemed to be identical
  • if the database field is empty and the document field is not empty and Fill from database is selected, the empty database value will not be entered into the field.

​

Primary keys in data sets

If your project includes a data set (a local copy of an external database) from which you want to source field values for documents when they are processed, you can use a database check rule to specify a field that will serve as the primary key (unique identifier) of a record in the data set. If you do this, the data on the Data Form will be equated to data in the data set. When you select a record, all fields will be filled in with values from the data set. You will not be able to edit values on the Data Form. If you attempt to do so, a dialog box prompting you to take one of the following actions will appear:

• select another record from the data set
• edit the existing record

You can open the dialog box for adding or editing records by right-clicking a field and clicking Edit Data Set Record… or Add Data Set Record… on the shortcut menu. When you delete data in a filled-in field by pressing the Delete key, data in all other fields related to the rule will also be deleted. The rule will be checked again when you apply your changes. How primary keys work

1. All fields targeted by the rule must have the Enter value from database option enabled, in which case, provided the field acting as the primary key contains a value, the program will not search for values of the other fields and will instead fill them in with values from the database.
2. If the field acting as the primary key contains no value, the program will examine other fields for which the Search option was enabled when fields that need to be compared were specified in the Field Link dialog box (see Specifying fields to compare above). If a unique record is found for these fields, values from the database will be used (as in 1. above).

Now all the user has to do is decide whether to accept all changes or choose another record from the data set. The following conditions must be met in order for the primary key to work:

• A Data Set is selected as the source;
• A primary key is specified in the data set, i.e. the Primary key option was enabled for a field when
• The field used as the primary key is not listed in the Field links list on the Rule Settings tab.

The ABBYY FlexiCapture for Invoices section contains more information about data sets and explains their use in a sample project. See Using vendor and business unit databases. See Looking up vendors and business units in the database.

​

Doing a database check for a set of fields

In this case, the program will search the database for a matching string for the values in a set of fields, each value in the set of fields is linked to different columns of the found string. Consider the following example: Let us associate the document fields Fa, Fb, and Fc with the database columns Ta, Tb, and Tc as follows:

• The value of the Fa field exactly matches the value of Ta
• The value of the Fb is a fuzzy match of the value of Tb
• When the specified conditions are met, the value of the Tc field is copied to the Fc field.

The database check will be successful if the program finds a database string where the value of the Ta column exactly matches the value of the Fa field and the value of the Tb column is a fuzzy match of t value of the Fb field. The value of the Tc column of this string will be copied to the Fc field. If no such string is found, the rule will return an error, even if the program finds, for example, Ta = Fa and Tb = Fb in different strings.