Validation Module DBLOOK2

Purpose:

Search external database and optionally replace data.

Usage:

DBLOOK2("dbfname","alias","index",value,"errmsg","expression")

dbfname is the absolute or relative [from Image-Master home] path to the dBase 3,4, or FoxPro database to be searched enclosed in quotation marks as indicated above.

alias is a user defined alias used to reference the searched database on subsequent calls. If an alias is specified, the system will not close the database upon exiting the function. If no alias is specified ("") then the database WILL be closed after the function terminates.

index is an optional index TAG or filename. (see below)

"": No associated index is available. The function will use "LOCATE FOR &value" to perform the search.

"TAG tagname": The tag "tagname" will be selected from the structural CDX associated with the database. The function will use "SEEK value" to perform the search.

"indexname": The absolute/relative (as in dbfname) path to a corresponding .NDX or .IDX index file. The function will use "SEEK value" to perform the search.

value is the value to SEEK if an index is being used, if an index is not being used (index="") then value is an expression to be evaluated by LOCATE FOR and must be enclosed in quotes.

errmsg is the error message to display to the user if the search fails.

expression is the actual Field Replacement Expression enclosed in quotation marks as indicated above.

Search and Copy Existing Matching Database Entry Information

A key field can be designated to search and copy information from existing documents in Image-Master scanned for the same party or similar matter or topic. When the user types in information in the key field, the database is searched for any existing entries that match. If a match is found, a subset of the existing information can be brought into the current entry and other actions can be taken. The user is given immediate visual feedback that a match was located, and can edit the copied information to make the new document unique in the database.

A common use for this feature would be to avoid having the scanning operator type identifying information that is repeated verbatim for multiple documents.

NOTE: The term key field usually refers to a single field, but does not have to be limited to just one. The rule configured may reference multiple fields on the Data Entry window. In addition, this feature can be implemented more than once on the Data Entry window in order to aggregate information from multiple sources within the Image-Master database.

How to Configure

Once a key match is found, one or more Field Replacement Expressions (FREs) control what data is copied from the matching record. If a single field is going to be updated, the FRE can be placed directly within the command in the Configurator. If multiple fields are going to be replaced, a text file containing all of the FREs (one per line) must be created first.

A typical field replacement expression (FRE) is constructed as follows:

m1fieldname2=fieldname1

where: "m1" is the mandatory prefix to indicate a field in the Data Entry Screen.

fieldname1 is the database field name or expression that is the source of the data

fieldname2 is the Data Entry Screen field name.

In most cases, fieldname1 and fieldname2 are the same.

Multiple FREs can be stored in a file placed for convenience in the network Image-Master directory. Choose a name that follows the 8.3 naming convention (See next note.) and an extension of .TXT for ease of administration and technical support.

NOTE: An 8.3 filename (also called a short filename or SFN) is a filename convention used by old versions of DOS, versions of Microsoft Windows prior to Windows 95, and Windows NT 3.51.

Start Image-Master, and select [Database Configuration].
Select your desired database.
Select [Index Field Editor].
Click on the key field from the Field list on the left and select [Field Options].
If the Fast Find Feature is disabled, enable it for this field by selecting [On]. If the Fast Find Feature is already enabled, leave the existing setting.
Click [OK] to close the Field Options dialog box.
Click on [Validation Clause Expression].
Type in: SDBLOOK("fieldname",search value,"expression") -or- SDBLOOK("fieldname",search value,"~filename")

where:

fieldname is the name of the key field being searched enclosed in quotation marks as indicated above.

search value is the value to be searched, which can be the name of a field (preceded by "m1") or an expression. In single FRE mode, expression is the actual FRE enclosed in quotation marks as indicated above. In multiple FRE mode, filename is the name of the file that holds the FRE expressions as previously described. The filename should be enclosed in quotation marks and preceded by a tilde (~) as indicated above.
Select [OK] to close the Expression Builder.
Select [OK] to close the Index Field Editor.

SDBLOOK Example

Given a database with the following structure.

"Phone" is a key field that can be used to perform a lookup to determine the "Company" as well, and optionally the "Address" too if that was appropriate.

fieldname is "phone"

search value is trim(m1phone)

expression is "m1comp=comp" (a single FRE)

In the Validation Clause for the "Phone" field, the complete expression to be typed is: SDBLOOK("phone",trim(m1phone),"m1comp=comp")

If both "Company" and "Address" need to be replaced, a file needs to be created to store the multiple FREs.

The text file ADRREPL.TXT contains:

m1comp=comp

m1addr=addr

The Validation Clause for the "Phone" field would change to: SDBLOOK("phone",trim(m1phone),"~ADRREPL.TXT")

NOTE: Search value is an expression and must match the data type and size of the "Key" field. If it does not, then the lookup will always fail to find matching information.

NOTE: When sourcing search values from the Data Entry window, the size of these variables (m1...) may not match the associated fields exactly and must be extracted before they can be used. Numeric and date data types do not need to be extracted and can be specified directly. Character data types need to be extracted. The type of extraction chosen will depend on the uniqueness of the match desired.

Data Type	search value expression
Date or Numeric	m1fieldname
Character (partial matching permitted)	TRIM(m1fieldname)
Character (absolute exact match)	LEFT(m1fieldname,nn) where nn is the size of the "Key" field as specified in the Default Value

How to Use

In the Scanning Module, place the cursor into the key field.
Type a value that references an existing scan set that was previously saved.
Move to a different field using the [up/down arrow], [Tab] or [Enter]. The database will be searched for an existing matching record, and if one is found, the FRE replacements specified during configuration will be performed. Any Data Entry screen fields that are updated will show immediately in the Data Entry window.