Database Lookup (Lookup Specification)

This article is about the current version of Grooper.

Note that some content may still need to be updated.

2025

2023

Database Lookup is a Lookup Specification that performs a lookup against a database Data Connection via a SQL query.

Introduction

A Database Lookup in Grooper is a powerful tool for validating or enriching document data by querying an external database. It is part of Grooper’s broader Lookup framework, which also includes CMIS Lookup, Lexicon Lookup, Web Service Lookup, and XML Lookup. Unlike other Lookup types, Database Lookup specifically interacts with relational databases using SQL queries, allowing Grooper to cross-reference extracted or user-entered values with authoritative records or to retrieve additional information.

When to use

Use a Database Lookup when you need to:

Validate that a field or set of fields matches a record in an external database.
Populate additional fields or tables with data returned from a database query.
Enrich document data with reference values, descriptions, or related records.

Prerequisites:

A configured Data Connection with valid authentication and connectivity to the target database.
Database Lookup can only be added to container elements: Data Model, Data Section, or Data Table. It cannot be added directly to Data Fields or Data Columns.

How to add and configure a Database Lookup

Configure a Database Lookup

Follow these steps to add and configure a Database Lookup from the Grooper Design page:

Select the target container element (Data Model, Data Section, or Data Table) where you want to add the Lookup.
Click the ellipsis button for the Lookup property.
In the "Lookups" collection editor, click the "Add" button.
Choose "Database Lookup" from the drop-down menu.
Configure the following:
- General
  - Database Connection: Select the Data Connection to be queried.
  - SQL Query: Enter your SQL query to define the lookup operation.
- Lookup Options
  - The properties of this section are not required to be configured, and the defaults may suffice. Your use case will determine how and why you would configure these properties.
- Lookup Info
  - The properties of this section are auto-populated based on the query written.

Test a Database Lookup

Select a container element with a configured Database Lookup.
Click the Tester tab.
Select a Batch Folder in the Batch Viewer.
Click the "Test Extraction" button.
Populate the Target Field with appropriate data and tab out of the field.
Depending on your configuration, the fields may or may not automatically populate.

How to edit SQL queries

SQL (Structured Query Language) is used to define the lookup operation, specifying which records to retrieve and how results are mapped to Grooper fields.

Write your SQL query in the "SQL Query" property. Example:

  SELECT CustomerName, Address FROM Customers WHERE CustomerID = @Customer_ID

Reference Grooper fields as parameters using the @FieldName syntax. At runtime, Grooper replaces @Customer_ID with the value of the "Customer ID" field.
Use the SQL Query editor for syntax highlighting and code completion.
Queries are executed against the selected database connection. Grooper validates the query and displays errors or warnings in the diagnostic output.

Step by Step Query Edit

Select a container element, such as a Data Model, Data Section, or Data table.
Click the ellipsis button for the Lookups property.
In the Lookups collection editor, with a Database lookup in the collection, click the ellipsis button for the SQL Query property.
Start by writing the Select clause.
Press space and intellisense will provide the option for the From clause.
- Here you select the lookup table.
After selecting a table from the intellisense dropdown, you cursor will be put between the "SELECT" and "FROM" clauses.
Intellisense will give you a list of fields from the Database Connection you can select.
If more than one field is used, type a comma, then a space and intellisense will come up again allowing you to continue adding fields to the query.
Use the AS clause after fields to set an alias when names of fields in Grooper differ from columns in the lookup table.
Once done adding fields put your cursor after the lookup table of the From clause, then press space.
Intellisense will pop up allowing you to select the Where clause.
Select the field that will function as the target field to fire the lookup.
Press space and intellisense will allow you to select the = sign.
Press space after this and use the @FieldName syntax to define the target field.

How fields are populated

After executing the SQL query:

Grooper maps the result columns to target fields based on name or explicit mapping.
If the query returns a single row, each column value is assigned to its corresponding Grooper field.
If multiple rows are returned and the target is a collection (such as a Data Table), each row is added as a new record.
Data types are interpreted according to the field’s configuration (e.g., text, number, date).
If no results are found or multiple results are returned, behavior is controlled by the "Miss Disposition" and "Conflict Disposition" properties.

Properties overview

Below is a comprehensive list of Database Lookup properties, including their definitions, remarks, and use cases:

General

Database Connection
- Specifies the Data Connection used to execute the lookup query.
- Determines which database will be queried when performing a Database Lookup. Select a Data Connection that has been configured with the appropriate server address, authentication, and provider settings for your target database. The lookup's SQL query will execute against the tables and views available in the selected database. Use the Connection Settings property on the Data Connection to review or modify connection details as needed.
- Purpose: Establishes connectivity for the lookup operation.

SQL Query
- The SQL query to be used for the lookup operation.
- Defines the SQL statement executed against the selected Data Connection during a Database Lookup. Use @VariableName syntax in the WHERE clause to reference Grooper field values or variables. The SELECT clause should include all columns needed for field population, using aliases to match Grooper field names as necessary. For validation-only lookups, select only the minimal required columns for efficiency.
- Purpose: Defines the criteria and columns for the lookup.

Description
- An optional description for this lookup operation.
- Use this property to provide a human-readable explanation of the lookup's purpose, configuration, or any special instructions. This description is displayed in the property grid and can help other users understand the intent and behavior of the lookup.
- Purpose: Improves maintainability and clarity.

Lookup Options

Trigger Mode
- Controls when the lookup is executed.
- Determines the conditions under which a lookup operation is performed in Grooper. This setting allows you to control whether lookups are executed automatically, only under certain conditions, manually, or based on a custom expression. Choosing the appropriate trigger mode is essential for balancing automation, user control, and data integrity in your solution.
- Purpose: Determines the timing of the lookup operation.

Miss Disposition
- Specifies what happens if the lookup returns no results.
- Determines how Grooper responds when a lookup operation does not return any matching records from the external data source. This setting controls whether errors are flagged, target fields are cleared, or no action is taken.
  - Select the option that best fits your data integrity, user experience, and workflow requirements.
  - Use to control how missing or unmatched data is handled during validation or population.
  - Consider the impact on downstream processes and user review when configuring this setting.
- Purpose: Controls error handling for missing records.

Conflict Disposition
- Specifies what happens if the lookup returns multiple results.
- Controls how Grooper handles situations where a lookup operation returns more than one matching record from the external data source. This setting determines whether errors are flagged, data is cleared, the first result is accepted, or no action is taken.
  - Select the option that best fits your data quality, business rules, and user experience requirements.
  - Use to control how ambiguous or conflicting data is managed during validation or population.
  - Consider the risk of data ambiguity and the need for user intervention when configuring this setting.
- Purpose: Controls error handling for ambiguous results.

Field Population
- Specifies how target fields are populated with the lookup results.
- Controls whether and how the results of a lookup operation are written to the target fields in Grooper. This setting determines if lookup results will overwrite existing values, supplement only empty fields, or leave all fields unchanged.
  - Choose the method that matches your data entry and validation requirements.
  - Use to control whether lookups act as authoritative sources, provide supplemental data, or serve only for validation.
  - Consider the impact on user-entered data and downstream processes when selecting a population method.
- Purpose: Controls whether fields are updated with lookup results.

Lookup Info

Lookup Fields
- The list of fields and variables used as lookup criteria.
- Displays a comma-separated list of all Data Fields and Variable Definitions that are used as input for the lookup query. These are the values that will be sent to the external data source to perform the lookup.
- Purpose: Identifies which fields are used as query parameters.

Target Fields
- The list of fields that will be populated by the lookup operation.
- Displays a comma-separated list of all Data Fields that will be set or validated by the results of the lookup. These are the target fields that will receive values from the external data source.
- Purpose: Specifies which fields receive data from the lookup.

Summary

Database Lookup is an essential Grooper feature for integrating document data with external databases. By configuring a Database Lookup, users can automate validation, enrichment, and population of fields and tables, ensuring data integrity and reducing manual entry. Proper configuration and testing are key to successful implementation.

See also: