The Quillix Database Lookup QSX performs an automated query from a SQL database and populates Quillix document fields with information returned from the query. Configuration of the Database Lookup QSX is achieved through the use of a graphical user interface, allowing the user to select database query parameters from drop-down lists, or for advanced users, specifying a custom SQL query.
The Database Lookup QSX works with any ODBC compliant database. The QSX requires a 32-bit ODBC DSN connected to the lookup database to be created. A 64-bit ODBC DSN must also be created if using the 64-bit version of Quillix Capture. The 64-bit DSN must be named the same as the 32-bit DSN.
The QSX requires a 32-bit ODBC DSN connected to the lookup database to be created. A 64-bit ODBC DSN must also be created if using the 64-bit version of Quillix Capture. The 64-bit DSN must be named the same as the 32-bit DSN.
The Database Lookup QSX may be configured to support multiple document types in one instance of the QSX. Since the Database Lookup's query is based on one or more document-level index field values, consideration should be given to the types of documents for which a lookup is desired.
A lookup will only be performed for documents with document types that have been configured for the specific instance of the Database Lookup QSX.
The Database Lookup configuration dialog may be displayed be right-clicking on the QSX icon and selecting the Configure... option from the context menu.
1. Select Document Type
All configuration settings apply to a specific document type, including the database connection. This provides the greatest flexibility, even allowing queries to be made on different databases for different document types. Select the desired document type from the Document Type drop-down.
2. Configure Database Connection
To create or configure the database connection, click on the Configure Connection button. This will display the DSN Information dialog for selecting the desired DSN and to provide connection credentials.
3. Define Lookup Query
Once a connection to the database has been established, select the proper DB Schema and DB Table from the drop-down lists.
Next, Select Fields to Add to Query. The Simple Query option can be used when all Quillix Index Fields and Database Fields match. If the Quillix Index Field name is not the same as the Database Field (or column) name, you can "map" the Quillix Index Field by selecting the correct value from the drop-down in the Database Field column. Select Add/Modify Selected Fields to add the fields to the SQL Query edit box.
Finally, Query Conditions should be set by selecting the Database Column, Operator and Index Field values and clicking Add Condition.
As the query is being defined, the SQL Query text box will dynamically update with the results of the query selections.
4. Test Lookup Query
Once all the options for the query have been selected, click the Test Query button to run a test. A value will need to be provided for the Quillix Index Field placeholders.
5. Select Process Outcome Options
Select the desired options for Process Outcomes for exception handling.
6. Save Document Type Configuration
Once all query options have been selected for a document type, and after a test query has been successfully executed, click Save Document Type Configuration to save the settings for the selected document type.
Repeat this process for each document type for which a database lookup is desired in this instance of the Database Lookup QSX.
SQL Query
This edit box allows an advanced user to create a custom SQL Query manually, or the wizard-created query can be modified. Quillix Capture uses the query entered here.