Download Real-Time Lookup in Quick Fields White Paper

Real-Time Lookup in Quick
Fields
White Paper
September 2006
The information contained in this document represents the current view of Compulink Management
Center, Inc on the issues discussed as of the date of publication. Because Compulink must
respond to changing market conditions, it should not be interpreted to be a commitment on the part
of Compulink, and Compulink cannot guarantee the accuracy of any information presented after the
date of publication.
This chapter is for informational purposes only. COMPULINK MAKES NO WARRANTIES,
EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.
1
Table of Contents
Real-Time Lookup in Quick Fields ............................................................................i
Table of Contents ..................................................................................................2
Introduction to Real-Time Lookup........................................................................3
Lookup ...................................................................................................................5
Real-Time Processing.........................................................................................11
Dynamic Field Update Example ......................................................................11
Field Verification .................................................................................................14
2
Introduction to Real-Time Lookup
Real-Time Lookup is an add-on that allows Quick Fields to communicate with
a third-party database using Open Database Connectivity (ODBC). The add-on
includes two processes called Lookup and Field Verification. Both processes
compare a value taken from the document to the values stored in a column of
the third-party database. If there is a match, Lookup retrieves the
corresponding values in the same row and makes them available as tokens.
Field Verification can flag and/or delete the values that do not generate a
match in a specified column of the database.
For example, suppose you have a database of employees and their contact
information. You would like to use this database to fill your documents’
template fields with the contact information of that document's author, then
file the documents into subfolders by author. You could use Zone OCR to
extract the author’s name from each document. Lookup can then take these
values and generate tokens for you to use in your template fields. These
tokens will automatically be replaced with the contact information that
matches with each author. You can then use Zone OCR’s name token to
specify the destination folder for your documents. Field Verification can
clear name tokens whose values are not found in the database, so that you do
not create subfolders for employees who no longer work for your company.
The following figures give a general overview on how Lookup works.
3
Figure 1a: Lookup tokens are
used to populate template fields.
Figure 1b: Real-time Lookup compares the author’s name to those listed in a
database. When it finds a match, it uses the other values in that row for the tokens.
Figure 1c: The tokens are replaced by
corresponding values from the database.
4
This white paper addresses the Real-Time Lookup add-on. It assumes that you
are familiar with the basic Quick Fields operation. For more information on
Quick Fields, refer to the Quick Fields help file or the user manual.
Real-Time Lookup supports a variety of common database formats such as
Microsoft SQL Server databases, Excel files, Access files, comma-separated
value (CSV) data files, and many more. See the Quick Fields help file for a
complete list of supported database formats and operating requirements for
Real-Time Lookup.
Lookup
Lookup is available in the Identification and Image Processing steps of a
Quick Fields session. Like other processes, it has a wizard in the task pane
that will help you configure the settings. First, you will need to select your
third-party database. Then you will specify the lookup criteria and how
Lookup tokens are used.
To configure ODBC and select your third-party database
1. In the Lookup Wizard, click on Administer Data Sources.
Figure 2: Step 1 of the Lookup Wizard. Click on Administer
Data Sources to open the ODBC Data Source Administrator box.
5
2. Select or add the appropriate source for your database from the User
DSN tab. You can add a source by clicking Add and selecting the
desired driver. The other tabs in the ODBC Data Source Administrator
dialog box are not used in configuring the Lookup process. They are
mainly for informational and debugging purposes.
Figure 3: ODBC Data Source Administrator dialog box. You can
specify the third-party database.
3. Click Configure. A dialog box corresponding to the data source you
selected will appear.
4. Specify which database you would like Real-Time Lookup to work
with and click OK. Remember, a database can simply be an Excel or
CSV file.
6
Figure 4: This is the ODBC setup screen for an Excel database.
In this case, click Select Workbook and browse for the file you want.
Note: As long as Quick Fields is open, you cannot open this
database while it is linked with the Lookup process.
5. Click OK to close the ODBC Data Source Administrator dialog box.
Note: You can also configure ODBC outside of Quick Fields.
From the Start menu, select Control Panel. Double-click
Administrative Tools and then Data Sources (ODBC). The ODBC
Data Source Administrator dialog box will appear.
To configure the Lookup process
1. From Step 1 of the Lookup wizard, select ”User” for ODBC Data Source
Type and the desired ODBC Data Source Name.
Note: User DSN is only available to the currently logged-in
Windows user. System DSN is available to all users who log into
the machine. Aside from the access properties, there are no other
differences between User DSN and System DSN.
2. Complete the Authentication section if needed. For instance, if you are
accessing data from an SQL database, you will need to provide the
appropriate login information.
3. Click on the Next button (
) to go to Step 2 of the wizard.
7
4. Choose a database table if needed.
5. Specify the lookup value and the database column that the lookup
value will be searched under. The lookup value can be a combination
of text and tokens. Tokens can specify document properties, field data,
or results from data extraction processes such as Zone OCR or Bar
Code. The lookup value may or may not be case-sensitive depending on
the data source. Real-Time Lookup does not support wildcards.
6. Click Add. You may add more criteria, but all criteria must be satisfied
to qualify as a match.
Figure 5: The tree pane shows that Lookup is set to occur after an International Zone OCR
process. Therefore, the OCR token is available to use as the lookup value, as seen in the task
pane.
8
If you are using Lookup in the Image Processing step, there is an additional
option to mark fields that Lookup could not populate with data with the
following icon: . After sending your documents to a repository, this icon
will not appear in the Laserfiche client.
Figure 6: There is an option to mark the
fields that Lookup was unable to populate.
Figure 7 If that option is selected, you will be
alerted to fields that Lookup failed to
populate.
9
Once you have configured Lookup, you can use its tokens to identify a page,
populate a field, name the document, or determine the document’s
destination location. The token follows the syntax $Process_Column$, where
Process represents the name of the Lookup process and Column represents the
database column that will contain the desired value from a matching row. For
instance, if you are retrieving data from a column called "Birthday" using a
Lookup process named "Lookup1", the token would be $Lookup1_Birthday$.
You can access tokens by clicking the > button next to eligible fields.
Figure 8: The user in this example clicked the
> button for the Category field and is about to
use a Lookup token to populate it.
10
Real-Time Processing
Since a lookup value can be expressed with tokens, a change in the value of
one token may affect the value of one or more Lookup tokens. If the user has
not modified the data that was retrieved from the third-party database by the
Lookup process, then that data (which may only be a portion of the field
value) will be replaced with the updated value. On the other hand, if the user
has already modified the portion of the field populated by the Lookup
process, then the new value extracted from the third-party database will be
appended to the current value assigned to the field.
Each field used by a Lookup process is assigned an icon. A field that can
update one or more fields when modified is indicated by the following icon:
. A field that is updated whenever another field is modified is indicated by
the following icon: . The next section gives an example of real-time
processing.
Dynamic Field Update Example
The following example demonstrates how real-time processing can affect the
field data that will be assigned to a document. In this example, we will
assume that the Lookup value is set to a field called "Zip Code" ($[Zip
Code]$). This indicates that for each document being processed, the Lookup
process will compare the value assigned to the Zip Code field to data in a
third-party database. We will also assume that the third-party database
associated with the Lookup process contains entries for both "90507" and
"90807" in the database column associated with the lookup value.
The following illustration is the Fields task pane associated with the
document class that will process the documents in this example. The Zip
Code field will be populated with data extracted from an image in the
document. The City and State fields will be populated with data extracted
from the third-party database when a match for the data specified in the Zip
Code field is found.
11
Figure 9: These fields are populated using
International Zone OCR tokens and Lookup tokens.
The following two illustrations demonstrate the effects of the Lookup process.
Both of these illustrations are of the task pane associated with a particular
processed document. The illustration on the left demonstrates the original
values that were stored as field data. Please note that the value "90507" was
stored in the Zip Code field. This value was found in the third-party database.
This caused the Lookup process to store the values "Torrance" and "CA" to the
City and State fields, respectively.
At this point, the user will review the field values. By viewing the image(s)
associated with the document, the user will realize that the correct zip code
value is 90807. The user can then correct this value and reassign the Zip Code
field to "90807". Once the user makes this correction, the Lookup process will
look up the value "90807" in the third-party database. A matching value will
be found in the third-party database, which will result in the automatic
correction of the City and State fields. This can be seen in the illustration on
the right.
12
Figure 10: The figure on the left is the document task pane before the user changed the zip
code. The figure on the right is the document task pane after the change.
In the illustrations above, the entire data stored in the City and State field
were updated with new data from the third-party database. This happened
because the values assigned to the fields ("Torrance" and "CA") matched the
data originally retrieved by the Lookup process.
We will now discuss two scenarios in which the Lookup field update
behavior would be slightly different. The first alternate scenario occurs when
data not retrieved from the Lookup process is also present. The manner in
which this data was specified does not matter. If this additional data is
modified but the Lookup value is kept intact, then the Lookup process will
only replace the value originally retrieved by the Lookup process. For
example, if prior to modifying the Zip Code field, the user modifies the City
field to "Torrance (Los Angeles County)", then when the user updates the Zip
Code field to "90807" Lookup would still replace "Torrance" with "Long
Beach".
13
A second alternate scenario occurs when the data retrieved by the Lookup
process is modified. This will cause the new value retrieved by the Lookup
process to be appended to the field data. For example, if prior to modifying
the Zip Code field, the user modifies the value for the State field to
"California", then updating the Zip Code value would cause the Lookup
process to append the value "CA" to the value assigned to the State field
("CaliforniaCA").
Note: If the lookup value specifies a field that will be populated
by the same Lookup process, then this field will not be updated
by the Lookup process after its modification.
Field Verification
The configuration process for Field Verification is similar to that for Lookup.
After creating the process under “Image Processing,” the Field Verification
Wizard in the task pane will prompt you to select a third-party database and
the lookup criteria, just as you did for Lookup. Unlike Lookup, however,
Field Verification performs its main action when it can not find a match in
the third-party database. In the last step of the wizard, you can configure
whether Field Verification clears unverified data, flags fields with unverified
data, or both.
Figure 11: There are two actions you can take
when Field Verification encounters unverified
data.
Field Verification is only available under Image Processing. You can use
Field Verification to alert you of OCR mistakes or to prevent fields from being
filled with invalid data. For instance, suppose there is a mistake in Zone OCR
such that “Ohio” is read as “Obio.” When Field Verification fails to locate
“Obio” in the third-party database, it will clear and/or flag that field, so that
the scanner operator can fix the error later.
14
Title: Real-Time Lookup in Quick Fields
September 2006
Author: Edmund Tran
Editor: Regina Carns
Compulink Management Center, Inc.
Global Headquarters
3545 Long Beach Blvd.
Long Beach, CA 90807
U.S.A
Phone: +1.562.988.1688
www.laserfiche.com
Laserfiche is a trademark of Compulink Management Center, Inc.
Various product and service names references herein may be
trademarks of Compulink Management Center, Inc. All other products
and service names mentioned may be trademarks of their respective owners.
Copyright © 2006 Compulink Management Center, Inc.
All rights reserved
15