1. PSIGEN Support Portal
  2. PSIcapture Administrator Guides (Configuration)
  3. Capture Profiles

Advanced Indexing: Lookups

Avatar
Joel Fernandez

Overview

 

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.

 

Configuring Lookups

 

In the area below the Lookups tab a list of all currently available Database Lookups for the current Capture Profile will be listed.

 

 

Add

Click on the "Add" button to bring up the Lookup Configuration window shown below.

 

 

Lookup Definition Tab

Lookup Type

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.

Name

The name the user chooses to give this database lookup definition.

Lookup Provider

Choose one of the following providers:

  • Delimited Text Files (CSV,etc.)
  • FileBound
  • 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
  • Oracle
  • Xero

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.)  
  1. Select the Directory
  2. Select the File
  3. Select the File Type
    1. CSV Delimited
    2. Tab Delimited
    3. Fixed Length Fields
    4. Custom Delimited
    5. Enter the Custom Delimiter (ie. | or ,)
  4. Check the box if the First Row contains field names.
FileBound   
  1. Select the version of the FileBound.
  2. Enter the FileBound Server URL.
  3. Enter the User Name.
  4. Enter the Password.
  5. Select Test Connection
  6. Select the desired Project.
FileBound List  
  1. Select the version of the FileBound.
  2. Enter the FileBound Server URL.
  3. Enter the User Name.
  4. Enter the Password.
  5. Select Test Connection
  6. Select the desired Project.
  7. Select the desired Options List Field.
IBM DB2  
  1. Enter the name of the Database Alias.
  2. Enter the Schema (optional)
  3. Enter the UserID and Password for the selected database.
IBM Informix Dynamic Server  
  1. Enter the name of the Host.
  2. Enter the name of the Service.
  3. Enter the name of the Server.
  4. Enter the name of the Database.
  5. Enter the User ID and Password for the selected database
Microsoft Access  
  1. Enter the path to the Access Database or select Browse to locate it.
  2. Enter the User ID and Password if applicable.
Microsoft SharePoint Choice Field  
  1. Enter the SharePoint Site Address.
  2. Enter the User Name.
  3. Enter the Password.
  4. Select Test Connection.
  5. Select the desired Web.
  6. Select the desired Document Library/List.
  7. Select the desired Choice Field.
Microsoft SharePoint List  
  1. Enter the SharePoint Site Address.
  2. Enter the User Name.
  3. Enter the Password.
  4. Select Test Connection.
  5. Select the desired Web.
  6. Select the desired Document Library/List.
Microsoft SharePoint Person/Group  
  1. Select the SharePoint Version.
  2. Enter the SharePoint Site Address.
  3. Enter the User Name.
  4. Enter the Domain if applicable.
  5. Enter the Password.
  6. Select Test Connection.
  7. Select the desired Web.
  8. Select the desired Lookup Type (i.e. person, group, etc).
Microsoft SharePoint Term Set  
  1. Select the SharePoint Version
  2. Enter the SharePoint Site Address
  3. Enter User Name, Password, Domain
  4. Test Connection
  5. Select Term Store ID, Term Set ID, Parent ID
Microsoft SQL Server  
  1. Enter the name of the SQL Server or select the drop down box (WARNING: this will query the whole domain for all SQL servers) to locate it.
  2. Select the Use Trusted Connection or enter the User ID and Password for the selected SQL server.
  3. From the drop down Select Database to use.
ODBC Compliant Database  
  1. Select a predefined System DSN.
OpenLink Virtuoso Server  
  1. Enter the name of the Virtuoso Server.
  2. Enter the name of the Qualifier.
  3. Enter the name of the Owner.
  4. Enter the name of the Database.
  5. Enter the User ID and Password for the selected database.
  6. Select Test Connection
Oracle  
  1. Enter the name of the Oracle Server.
  2. Enter the Schema / Owner (optional)
  3. Select the Use Trusted Connection or enter the User ID and Password for the selected Oracle server.
Xero  
  1. Enter the Private Program Name.
  2. Enter the Consumer Key.
  3. Select the location of the Certificate (.ptx) File by clicking on the "..." ellipses button.
  4. Enter the Certificate Password.
  5. Select Test Connection.
  6. Select Lookup Type.

 

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


1.png

  • 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.

Lookup Parsing

  • 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.

Lookup Options

 

 

Record Selection

  • 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.

Flagging

  • 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

Miscellaneous

  • 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.

     

Was this article helpful?
2 out of 2 found this helpful

Comments

0 comments

Please sign in to leave a comment.

Related articles