Database Lookup

The 'Database Connection' property 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.

Usage

1. Create or select a Data Connection in your Grooper repository.
2. Assign the Data Connection to this property to enable lookups against that database.
3. Test the connection to ensure proper connectivity and permissions before deploying lookups in production.

Best Practices

• Store Data Connections in a dedicated Project to simplify management across environments (e.g., DEV/TEST/PROD).
• Use descriptive names for each Data Connection to clarify its purpose and target environment.
• Always verify that the selected connection has access to the required tables and data for your lookup scenarios.

For more information, see the documentation for Data Connection and Database Lookup.

The 'SQL Query' property 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.

Configuration Guidance

1. Start with the FROM clause to ensure intellisense provides the correct set of table and column names.
2. Add the SELECT clause, including all columns to be used for field population. Use aliases as needed.
3. Add the WHERE clause, referencing Grooper fields or variables using @VariableName.
4. For validation-only lookups, select only a single column or use SELECT 1 for efficiency.

Validation-Only Query Examples

To validate a field value, reference the field value in the WHERE clause. The examples below assume Field Population is set to 'None'.

Validates that the value of the 'PO Number' field is present in the database.

Validates that the 'Invoice Number' / 'Vendor ID' pair is present in the database.

Field Population Query Examples

To populate field values, include the source columns in the SELECT clause, providing aliases where needed to ensure column names match field names. The examples below assume Field Population is set to 'Overwrite' or 'Supplement'.

Validates the 'PO Number' field, and populates the 'Vendor ID' field with the value of the 'VendNo' column.

Validates the 'Student Number' field, and populates the 'Last Name', 'First Name', and 'DOB' fields from the 'Last', 'First', and 'DOB' columns.

Best Practices

• Use column aliases in the SELECT clause to ensure returned column names match Grooper field names for population.
• For validation-only lookups, use SELECT 1 or a single column to minimize data transfer.
• Always test your query in the native database editor for complex or platform-specific SQL before pasting into Grooper.
• Use the query editor's intellisense to assist with table, column, and variable names.

For more information, see the documentation for Database Lookup, Data Connection, and Grooper's data extraction workflow.

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.

Can be one of the following values:

Determines the conditions under which a lookup operation is triggered. Use this to control automation, user interaction, or custom logic for lookup execution.

Enter a code expression that evaluates to true or false. The lookup will only execute if the expression returns true. This allows for advanced, data-driven control over when the lookup fires. Example:@FieldA != null && @FieldB == "Active"

Can be one of the following values:

Determines the behavior of the lookup operation when no matching records are found in the external data source. Configure this to control user feedback and data clearing.

Can be one of the following values:

Controls the behavior when the lookup query returns more than one matching record. Choose the option that best fits your data integrity and workflow needs.

Can be one of the following values:

Controls whether and how the results of a lookup operation are written to the target fields. Choose the method that matches your data entry and validation requirements.

This property 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.

This property 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.