Database Lookups are powerful tools that can speed up and fully automate document indexing. Database Lookups allow the use of data already available in existing databases to populate index data within the program. Users can create as many Database Lookups as needed for each Capture Profile. The lookups can all point to one database or to multiple databases even of different types.
In the area below the Lookups tab a list of all currently available Database Lookups for the current Capture Profile will be listed.
Click on the "Add" button to bring up the Lookup Configuration window shown below.
Lookup Definition Tab
There are 2 different types of database lookups:
- Query - Query lookups will take values in certain index fields and use those values to run a query in the selected database table or view and return only records matching those values.
- List - List lookups will return all the records from the select row within a table or view and allow the user to select the record they wish to use to populate the selected index fields.
The name the user chooses to give this database lookup definition.
Choose one of the following providers:
- Delimited Text Files (CSV,etc.)
- FileBound List
- IBM DB2 (AS400 or MVS)
- IBM Informix Dynamic Server
- Microsoft Access
- Microsoft SharePoint Choice Field
- Microsoft SharePoint List
- Microsoft SharePoint Person/Group
- Microsoft SharePoint Term Set
- Microsoft SQL Server
- ODBC Compliant Databases
- OpenLink Virtuoso Server
NOTE: With the exception of OpenLink and ODBC the program connects directly to them without any further configuration then that which is presented in the programs configuration screens. Some Databases do require a client application to be loaded on each PC connecting to that database. Please check with the database provider for more information.
Lookup Provider Configuration
Click “Setup” button to configure the lookup. Outlined below is the provider information needed for configuration.
|Delimited Text Files (CSV,etc.)||
|IBM Informix Dynamic Server||
|Microsoft SharePoint Choice Field||
|Microsoft SharePoint List||
|Microsoft SharePoint Person/Group||
|Microsoft SharePoint Term Set||
|Microsoft SQL Server||
|ODBC Compliant Database||
|OpenLink Virtuoso Server||
Lookup Field Mappings
This section allows the user to map the index fields defined in Capture Profile - Index Data Fields with the fields defined in the selected database table.
- To map a field, highlight it and click on this Edit Field Mappings icon.
- To clear a field, highlight it and click on this Clear Field Mappings icon.
Lookup Field Selection
Lookup Field Type
There are three options for field mappings as listed below. NOTE: Query lookups define both Key and Return fields while List lookups define only Return fields.
- Key Field - A Key Field is a usually a unique field used to match the data entered in the Index field. However, the program supports an unlimited number of Key Fields. In this case, all key fields must match, even if blank in order to return values to the corresponding return value Index fields. Key fields are the fields used to match or filter records from the lookup table when running a Query lookup.
- Return Value - Return fields contain the information that will populate the Mapped Field Name with its corresponding data.
- Key Field and Return Value - The return value of a first lookup is used as key value to perform a second lookup.
Edit Index Field Mappings Options
- Truncate Whitespace - Selecting this option will cause all spaces at the end of the values contained in the Return fields to be removed once the value is placed in an index field.
- Do Not populate if field is already populated - When this feature is enabled, PSIcapture will not populate an index field already populated with data after performing a lookup.
- Entire Value - The entire return value is populated in the field.
- Subset - Only a portion of the return value is populated in the field, denoted by a starting position and an ending position.
- Split - Only a portion of the return value is populated in the field, denoted by a split character and element number.
- Automatically Return the First Matching Record - This is chosen by default.
- Display the "Browse Lookup Results" dialog to select the correct record when multiple matches exist - Selecting this option will cause a database grid view (multiple lines) to be shown during index when the lookup occurs and more than one key match is found.
- Create a Document Record for each record returned from the lookup - Selecting this option will automatically build additional data records for ALL key field return value matches. Example – The users scans a check that pays 5 invoices and the key field is the check number and the return values include invoice number etc. Then 5 data records will be built for that document. NOTE: the “Allow Multiple Records per Document when Indexing” in Capture Profile - Document Records must be enabled on the Capture Profile for this option to become available.
- Create a Document Record for each selected record in the "Browse Lookup Results" dialog - Use this option to allow multiple selections and record creation using database lookups. Each record selected within indexing will create a document record. NOTE: This is a manual process and will not function for automated indexing.
- Flag Records as they are Chosen - Selecting this option will set a field in the lookup table to a certain value when a particular record is chosen from the record selection dialog. This is useful if the users wish to ensure that a particular record is not used multiple times or if they wish to confirm a record was indexed by the program. This is normally a Boolean field (yes/no, true/false, etc.)
- Enter Field Name and Value or choose from the list.
- Default Record Display - Choose from the following:
- Display all records
- Display unused Records Only
- Don't run the lookup if the mapped Index Fields are already populated - Selecting this option checks the return fields and does NOT over write them with data if data was already populated. Additional options for these setting are:
- Skip when all return fields are populated
- Skip when one or more fields are populated
- Don't run the lookup if another lookup with the same key and return fields has already been run successfully - Selecting this option means if two different lookup fields have the same key and return fields, then the second field will not run if the first was successful.
Run LIKE query if a wildcard character (% or *) was entered - Selecting this option allow the user to enter a wildcard in the field and therefore will bring back all records matching and not force a unique match of the Key Field(s).
Limit entry to items defined in list - When defining a List type lookup, the user has the option to prevent other users from manually keying any values that have not been defined in the list into one of the Return fields defined for the lookup. Selecting this option ensures that all data for this field is data that was retrieved using the database lookup.
Implied wildcards - Selecting this option logically places wildcards on both sides of whatever values are entered. This can be especially helpful if only part of the information is available prior to running the lookup. All results containing the entered text will be displayed.
Enable Logging - Enabling this option with generate a log entry when a lookup is performed. This log is viewable under “Lookup Logs” in Batch Information.
- Set an Alert on the Index field and Parent (Batch, Folder, or Document) when a lookup fails - This shows as an exclamation and cause a fail as long as the Capture Profile is configured for validation failure.
- Enable Query Timeout - By selecting this option users have the ability to timeout the query if it is taking too long or limit the time the query takes.
Lookup Scripting - Please see the lookup scripting section for additional information on how the module functions. NOTE: Scripting within PSIcapture requires a knowledge of the C# programming language. Training and user guides may also be available, please contact your reseller for more information.