Lookups

A database lookup connects DocView Capture to your own data, such as your ERP's supplier or purchase order tables. Lookups can:

• check that a value exists, for example a purchase order number,
• fill other fields from the matching row, for example the supplier's name and ID from its VAT number,
• suggest values while an operator types, and
• act as a master data list that finders match documents against, even with OCR mistakes.

Lookups query SQL Server. They are set up once for the whole system and then used by fields and finders in any Batch Setup.

Before you start: store the connection string

Lookups never hold a password themselves. The connection string is stored as a secret.

1. Go to Settings and open the secrets.
2. Add a secret with the SQL Server connection string, for example named lookup.erp.

See Settings and Security.

Tip: Use a database account that can only read the tables you need. Lookups only ever run the query you write.

Create a lookup

1. Go to Administration › Batch Settings › Lookups. The page is called Database lookups.
2. Click New lookup.
3. Enter a Name, for example Suppliers by VAT number.
4. Choose the Connection string secret.
5. Write the Query (see below).
6. Choose what the lookup is for under Use.
7. Click Save lookup.
8. Type a Test value next to the lookup and click Test to see the rows it returns.

Write the query

Use @value for the field's value. Other fields of the document can be used as @FieldName. Values are always passed as parameters, never pasted into the SQL, so the query is safe from SQL injection.

SELECT VendorName, VendorId FROM Vendors WHERE VatNumber = @value

A query can use another field too:

SELECT PoNumber, Amount FROM PurchaseOrders
WHERE PoNumber = @value AND SupplierId = @SupplierId

Queries have a time-out of 5 seconds. Keep them fast: filter on indexed columns.

Lookup uses

Empty field values are not looked up.

Any lookup can also be opened by a lookup button on a designed form, where the operator searches its rows by column and picks one to fill several fields. See Screen designer.

Use a lookup on a field

1. Open the Batch Setup, go to Document classes & fields and open the field.
2. Under Extraction, choose the Database lookup.
3. Under Lookup fills these fields, map lookup columns to fields, one per line, as column = field:

VendorName = Supplier
VendorId = SupplierId

Columns named like a field fill it anyway. The map is used when a row is found and when the operator picks a row from the list.

4. Click Save field.

The operator's pick list

Fields with a lookup get a search button on the Index / Validate screen. Operators press F4 or click the button to open a searchable list of the lookup's rows. Choosing a row fills every mapped field of the document, or of the table row.

Master data and fuzzy matching

OCR is rarely perfect. A supplier name may come out as "Acme Industrles Ltd" or a VAT number with a space in the wrong place. A master data list lets DocView find the right supplier anyway.

1. Create a lookup with Use set to Master data list. The query returns all rows and needs no @value:

SELECT SupplierId, Name, VatNumber, Postcode FROM Suppliers WHERE Active = 1

2. On the Finders tab, add a finder with the method Database lookup (fuzzy) and choose this lookup.
3. Set Column to match on the document (for example Name) and Also match these columns (for example VatNumber and Postcode).
4. Set Value to return, for example SupplierId.
5. List the Other columns fields can use, then connect fields to Supplier.VatNumber, Supplier.Name and so on.
6. Test on your samples.

Minimum similarity (default 0.85) sets how many OCR mistakes are allowed. See Finders and zones.

A master data list may hold up to 50,000 rows. DocView keeps it in memory for 10 minutes and reloads it at once when you change the lookup. For larger tables, narrow the list in the query, for example to active suppliers only.

Tip: Matching on several columns makes results far more reliable. A supplier name alone can be ambiguous; name plus VAT number or postcode rarely is.

Lookups and exported Batch Setups

Batch Setup files refer to lookups by name. When you import a Batch Setup on another system, create lookups with the same names there first, and fields pick them up automatically. See Import and export Batch Setups.

Troubleshooting