2023:Database Lookup (Lookup Specification)

WIP	This article is a work-in-progress or created as a placeholder for testing purposes. This article is subject to change and/or expansion. It may be incomplete, inaccurate, or stop abruptly. This tag will be removed upon draft completion.

Database Lookups are used to populate or validate fields in a Data Model using an external database, such as a SQL database.

Previous Versions
Grooper 2.80

About

For validation, values in a Data Field are compared to corresponding values in a database table. If the results differ from what is in the table, that field can be flagged.

For data population, as long as the "lookup values" exist in the external database, Grooper extracted values can be used to populate additional Data Fields. Given a certain field or fields in a Data Model matches fields in a database table, additional values in the table’s row can be assigned to empty Grooper Data Fields. For example, an extracted social security number from a document could be used to lookup a corresponding name in database that has both the social security number and name.

The Grooper data fields used for comparison against a database table are called "lookup fields". The fields populated from the database table are called "target fields". A SQL query is used to find the lookup fields and target fields in the database table.

Database Lookup can be used to validate data already populated in a Grooper data model. That way, you can check to see if data extracted from a document set matches what's already in an existing database. It can also be used to populate data in a Grooper data model from a database. In these cases, some kind of extracted value from the document is used as a key to return information from a database that is not found on the document.

FYI	SQL databases and ODBC compliant databases are compatible with Grooper.

How To

Create a Data Connection

Before you can perform a database lookup, first Grooper must be able to connect to it in order to see everything in the data table. This is done by creating a Data Connection. Grooper can connect to a SQL database or any ODBC compliant database provider. You will also need the user name and password used to access the database.

Add a Data ConnectionConfigure the Database Connection

In the node tree inside of your project, right click the folder where you would like to create your Data Connection. Hover your mouse over "Add". Select "Data Connection..." from the list that pops up.
When the "Add" window pops up, enter a name for your Data Connection. Click "EXECUTE" to create.

Select the Data Connection in your node tree. Click the hamburger menu to the right of the Connection Settings property. Select ODBC or SQL Server:- from the drop down list. For this example we are going to select SQL Server:-.
Click the arrow next to the Connection Settings Property to open up its sub properties. Enter in the name of the server hosting the ODBC or SQL. Click the ellipsis button next to the Database Name property.
When the "Database Name" window pops up, scroll through and select the desired database. Click "OK" to select.
Enter in the User Name and Password you will be using to access the database. If you leave these fields blank, Grooper will default to Windows Authentication. Click the Save icon to save your changes.
Click the play button to verify your connection. You should see your database information pop up in the other windows.

Click here to return to the top