Data Export (Export Definition)

This article is about the current version of Grooper. Note that some content may still need to be updated.
2025	2024	2023	2021

Data Export is an Export Definition available when configuring an Export Behavior. It exports extracted document data over a database Data Connection, allowing users to export data to a Microsoft SQL Server or ODBC compliant database.

The Data Export is an Export Definition that enables you to map extracted data from Grooper documents to one or more tables in an external database using a configured Data Connection. This Export Definition is typically added to an Export Behavior on a Content Type to automate the export of structured data to a database as part of a batch process.

Overview

Data Export provides a flexible and powerful way to send structured data from Grooper to relational databases. It supports:

• Exporting to multiple tables in a single operation
• Executing custom SQL statements for advanced scenarios
• Dynamically selecting alternate databases at runtime
• Mapping data from Data Models, Data Sections, and Data Tables to database columns

This Export Definition is ideal for integrating Grooper with accounting systems, ERP platforms, document management systems, and other enterprise applications that rely on relational databases.

How it works

When a Data Export is executed (typically as part of a Batch Process via an Export activity), it performs the following steps:

1. Connects to the target database using the configured Data Connection
2. Evaluates the "Alternate Database" expression (if set) to determine the target database
3. Executes each Table Mapping in sequence, exporting rows to the specified tables
4. Executes any Custom Statements defined in the "Custom Statements" property
5. If any Table Mapping has "Return Values" configured, saves the updated data back to the document

Configuration

Connection

The "Connection" property specifies the Data Connection used for export. This determines the target database server, authentication method, and connection settings.

• Select a connection to an MS SQL Server, Oracle, MySQL, or other supported database
• The connection must be configured in the repository before assigning it to the Data Export
• If the connection is not set, export will not be performed

Table Mappings

The "Table Mappings" property defines the list of table export operations to perform. Each Table Mapping specifies:

• The source data element (from the document's Data Model) to export
• The target database table
• Column mappings between Grooper fields and database columns
• Optional duplicate detection and match actions
• Optional return values to read back after insert

Example

For an invoice document, you might configure two table mappings:

1. Header Mapping: Maps invoice header fields (Invoice Number, Date, Total, etc.) to an "Invoices" table
2. Line Items Mapping: Maps the Line Items Data Table to an "InvoiceItems" table, exporting one row per line item

Table mappings are executed in the order listed. At least one table mapping or custom statement must be defined for export to occur.

Custom Statements

The "Custom Statements" property allows you to define custom SQL statements to execute during export. These are useful for:

• Inserting log entries after export
• Updating status flags or timestamps in related tables
• Calling stored procedures for downstream processing
• Performing calculations or transformations not supported by standard table mappings

Custom statements are executed after all table mappings have completed. They can reference data from the document's Data Model using expressions, allowing for dynamic SQL generation.

Example

After exporting an invoice, you might execute a custom statement to update a "LastProcessed" timestamp in a tracking table:

UPDATE ProcessingLog 
SET LastProcessed = GETDATE() 
WHERE InvoiceNumber = '@InvoiceNumber'

Alternate Database

The "Alternate Database" property allows you to specify an expression that calculates the name of an alternate database to which data will be exported. This is useful for:

• Multi-tenant environments where each customer has a separate database
• Partitioned databases based on date, region, or other criteria
• Dynamic routing of exports based on document metadata

Important notes:

• This feature is only supported for MS SQL Data Connections
• The alternate database must have the same schema as the primary database
• The expression can reference fields from the document's Data Model
• If left blank, the default database from the Data Connection is used

Example

To route exports to a customer-specific database:

"Customer_" & CustomerID

This would export to a database named "Customer_12345" if the "CustomerID" field contains "12345".

Validation and error handling

Data Export includes validation to ensure proper configuration:

• A Data Connection must be set
• At least one Table Mapping or Custom Statement must be defined
• If "Alternate Database" is set, the connection must be an MS SQL Data Connection

During export, errors such as connection failures, SQL errors, or constraint violations are logged and will halt the export for the affected document. The document may be flagged for review depending on your Batch Process configuration.

Use cases

Exporting invoice data to an accounting system

Configure a Data Export on an "Invoice" Content Type with:

• Connection: Set to your accounting database connection
• Table Mappings:
  • Map invoice header fields to an "Invoices" table
  • Map line items Data Table to an "InvoiceLineItems" table
• Return Values: Configure the header mapping to read back an auto-generated "InvoiceID" column

Writing extracted form data to multiple related tables

For a loan application document:

• Map applicant information to a "Borrowers" table
• Map employment history to a "EmploymentHistory" table
• Map references to a "References" table
• Use "Match Columns" to prevent duplicate entries

Executing custom SQL after export

After exporting a document, execute a stored procedure to trigger downstream processing:

EXEC ProcessNewDocument @DocumentID = '@DocumentID', @BatchID = '@BatchID'

Dynamically routing exports to different databases

For a multi-tenant SaaS application, use the "Alternate Database" property to route each customer's data to their dedicated database:

"TenantDB_" & TenantID

Best practices

• Use Table Mappings for standard scenarios: For straightforward data export, configure Table Mappings rather than writing custom SQL
• Use Custom Statements for advanced logic: Reserve custom statements for scenarios not supported by table mappings, such as calling stored procedures or updating multiple tables
• Test with small batches first: Before deploying to production, test your Data Export configuration with a small test batch to ensure mappings and SQL are correct
• Monitor export logs: Review diagnostic logs and error messages to troubleshoot connection issues, SQL errors, or data validation problems
• Ensure database schemas match: If using "Alternate Database", verify that all target databases have identical schemas to avoid export failures
• Use Return Values for generated keys: When exporting to tables with auto-generated identity columns, configure "Return Values" to capture and store the generated IDs for use in subsequent mappings or activities

How to add a Data Export to an Export Behavior

This guide explains how to configure a Data Export within an Export Behavior on a Content Type, enabling automated export of extracted data to a database as part of your batch processing workflow.

Prerequisites

Before configuring a Data Export, ensure you have:

• A Content Type with a Data Model containing the fields, sections, or tables you want to export
• A Data Connection configured and tested for your target database
• Appropriate database permissions to insert, update, or execute custom SQL statements

Step 1: Add an Export Behavior to your Content Type

1. In the Grooper repository tree, navigate to your Content Type
2. If the Content Type does not already have an Export Behavior, add one:
   • Right-click the Content Type and select Add Behavior
   • Choose Export Behavior from the list of available behaviors
   • Click OK

The Export Behavior will appear in the "Behaviors" section of the Content Type's property grid.

Step 2: Add a Data Export definition

1. Select the Content Type in the tree
2. In the Property Grid, locate the Export Behavior section
3. Click the ellipsis button (...) next to the "Export Definitions" property
4. In the Object Collection Editor dialog that appears:
   • Click the Add dropdown button
   • Select Data Export from the list of available export types
   • Click OK

A new Data Export definition will be added to the collection.

Step 3: Configure the Data Connection

1. With the Data Export definition selected in the Object Collection Editor, locate the "Connection" property
2. Click the dropdown or ellipsis button to select your Data Connection
3. Choose the connection that points to your target database
4. Click OK to confirm

Step 4: Configure Table Mappings

Table mappings define how data from your Data Model is exported to database tables.

1. In the Data Export properties, click the ellipsis button (...) next to "Table Mappings"
2. In the Object Collection Editor:
   • Click Add to create a new Table Mapping
   • Configure the following properties for each mapping:
     • Source Element: Select the Data Field, Data Section, or Data Table from your Data Model to export
     • Table Name: Enter the name of the target database table
     • Column Mappings: Map each field to a corresponding database column
     • Match Columns (optional): Define columns used to detect duplicates
     • Match Action (optional): Specify what to do when a duplicate is found (Insert, Update, Skip, or Error)
     • Return Values (optional): Specify columns whose values should be read back after insert (e.g., auto-generated IDs)
3. Click OK when all mappings are configured

Step 5: Configure Custom Statements (Optional)

Use custom statements to execute additional SQL logic after table mappings are processed.

1. In the Data Export properties, click the ellipsis button (...) next to "Custom Statements"
2. In the Object Collection Editor:
   • Click Add to create a new Custom Statement
   • Enter your SQL statement in the "Statement" property
   • Use expressions to reference fields from your Data Model (e.g., @InvoiceNumber)
3. Click OK when finished

Example custom statement:

UPDATE ProcessingLog 
SET LastProcessed = GETDATE() 
WHERE InvoiceNumber = '@InvoiceNumber'

Step 6: Configure Alternate Database (Optional)

If you need to dynamically route exports to different databases based on document data, configure the "Alternate Database" property.

1. In the Data Export properties, locate the "Alternate Database" property
2. Click the ellipsis button (...) to open the expression editor
3. Enter a code expression that evaluates to the target database name
4. Click OK

Example expression:

"Customer_" & CustomerID

This would export to a database named "Customer_12345" if the "CustomerID" field contains "12345".

Step 7: Set the Link Name (Optional)

The "Link Name" property determines the label for the Content Link created after export.

1. In the Data Export properties, locate the "Link Name" property
2. Enter a descriptive name (e.g., "Database Export" or "ERP Export")
3. If left blank, the default name "Export" will be used

Step 8: Save and test

1. Click OK to close all dialogs and save your Data Export configuration
2. Save the Content Type (Ctrl + S)
3. Test the export:
   • Create or open a Batch with documents assigned to this Content Type
   • Ensure the documents have been extracted and contain valid data
   • Run the Export activity (either manually or as part of a Batch Process)
   • Verify that data appears correctly in your target database tables

Troubleshooting

• Connection errors: Verify that your Data Connection is configured correctly and that the database server is accessible
• SQL errors: Check that table and column names are correct and that your database user has appropriate permissions
• No data exported: Ensure that your Data Model has been extracted and that the mapped fields contain values
• Duplicate key violations: Configure "Match Columns" and "Match Action" to handle duplicates appropriately

See also

• Export Definition
• Export Behavior
• Data Connection
• Export activity