Data Export (Export Definition): Difference between revisions

From Grooper Wiki
← Older edit
No edit summary
 
(4 intermediate revisions by the same user not shown)
Line 3: Line 3:
<blockquote>{{#lst:Glossary|Data Export}}</blockquote>
<blockquote>{{#lst:Glossary|Data Export}}</blockquote>


 
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 {{IconName|Data Connection}} [[Data Connection]]. This Export Definition is added to an [[Export Behavior]] on a {{IconName|Content Model}} [[Content Model]] to automate the export of structured data to a database during the {{IconName|Export}} [[Export]] step in a {{IconName|Batch Process}} [[Batch Process]].
{{DISPLAYTITLE:Data Export}}
*<li class="fyi-bullet">Most typically Export Behaviors are added to '''Content Models''' but can be added to '''Content Categories''' and '''Document Types''' for more Document Type-specific export scenarios.
[[Category:Export Definitions]]
[[Category:Data Export]]
[[Category: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 ==
== Overview ==
Line 24: Line 19:
== How it works ==
== 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:
When a Data Export is executed (typically as part of a Batch Process via an Export activity), it performs the following steps:


# Connects to the target database using the configured [[Data Connection]]
# Connects to the target database using the configured Data Connection.
# (''Only if configured'') Evaluates the "Alternate Database" expression to determine the target database
# (''Only if configured'') Evaluates the "Alternate Database" expression to determine the target database
# Executes each "Table Mapping" in sequence, exporting rows to the specified tables
# Executes each "Table Mapping" in sequence, exporting rows to the specified tables
Line 34: Line 29:
== Configuration basics ==
== Configuration basics ==


Data Export is primarily configured using the "Connection" and "Table Mappings" settings.
Data Export is primarily configured using the '''Connection''' and '''Table Mappings''' settings.


More advanced scenarios are accommodated by the "Alternate Databases", "Match Columns" (and "Match Actions") and "Custom Statements" settings.
More advanced scenarios are accommodated by the '''Alternate Databases''', '''Match Columns''' (and '''Match Actions''') and '''Custom Statements''' settings.


=== Primary configuration settings ===
=== Primary configuration settings ===
Line 44: Line 39:
==== Connection ====
==== Connection ====


The "Connection" property specifies the [[Data Connection]] used for export. This determines the target database server, authentication method, and connection settings.
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
* Select a connection to an MS SQL Server, Oracle, MySQL, or other supported database
Line 52: Line 47:
==== Table Mappings ====
==== Table Mappings ====


The "Table Mappings" property defines the list of table export operations to perform. Each [[Table Mapping]] specifies:
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 source data element (from the document's Data Model) to export
* The target database table
* The target database table
* Column mappings between Grooper fields and database columns
* Column mappings between Grooper fields and database columns
Line 64: Line 59:
For an invoice document, you might configure two table mappings:
For an invoice document, you might configure two table mappings:


# '''Header Mapping''': Maps invoice header fields (Invoice Number, Date, Total, etc.) to an "Invoices" table
# Header Mapping: Maps invoice header fields ("Invoice Number", "Date", "Total", etc.) to an "Invoices" table
# '''Line Items Mapping''': Maps the Line Items [[Data Table]] to an "InvoiceItems" table, exporting one row per line item
# 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.
Table mappings are executed in the order listed. At least one table mapping or custom statement must be defined for export to occur.
Line 75: Line 70:
==== Match Columns and Match Actions ====
==== Match Columns and Match Actions ====


Match Columns and Match Actions work together to control how Data Export handles duplicate records during the export process. These settings are configured on each Table Mapping.
These settings are configured in the '''Table Mapping''' settings of a Data Export. '''Match Columns''' and '''Match Actions''' work together to control how Data Export handles duplicate records during the export process.


===== Match Columns =====
===== Match Columns =====
Line 134: Line 129:
* '''Match Action''': Error
* '''Match Action''': Error
* '''Result''': Stops export if a person with the same SSN and date of birth already exists
* '''Result''': Stops export if a person with the same SSN and date of birth already exists


*<li class="fyi-bullet">'''Note:''' When Match Columns are used, the "Rows Updated" statistic is tracked instead of "Rows Exported" to reflect the number of existing records processed.
*<li class="fyi-bullet">'''Note:''' When Match Columns are used, the "Rows Updated" statistic is tracked instead of "Rows Exported" to reflect the number of existing records processed.
Line 146: Line 142:
* Performing calculations or transformations not supported by standard table mappings
* 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.
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 =====
===== Example =====
Line 187: Line 183:
Data Export includes validation to ensure proper configuration:
Data Export includes validation to ensure proper configuration:


* A [[Data Connection]] must be set
* A Data Connection must be set
* At least one [[Table Mapping]] or [[Custom Statement]] must be defined
* 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]]
* If "Alternate Database" is set, the Data Connection must utilize an MS SQL database.


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.
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 ==
== Use cases ==
Line 197: Line 193:
=== Exporting invoice data to an accounting system ===
=== Exporting invoice data to an accounting system ===


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


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


Line 232: Line 228:
== Best practices ==
== Best practices ==


* '''Use Table Mappings for standard scenarios''': For straightforward data export, configure [[Table Mapping]]s rather than writing custom SQL
* '''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
* '''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
* '''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
Line 241: Line 237:
== How to add a Data Export to an Export Behavior ==
== 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.
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 ===
=== Prerequisites ===
Line 247: Line 243:
Before configuring a Data Export, ensure you have:
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 '''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
#*<li class="fyi-bullet">Most typically Export Behaviors are added to '''Content Models''' but can be added to '''Content Categories''' and '''Document Types''' for more Document Type-specific export scenarios in larger Content Models with a larger variety of Document Types.
* A '''Data Connection''' configured and tested for your target database
* Appropriate database permissions to insert, update, or execute custom SQL statements
* Appropriate database permissions to insert, update, or execute custom SQL statements


=== Step 1: Add an Export Behavior to your Content Type ===
=== Step 1: Add an Export Behavior to your Content Type ===


# In the Grooper repository tree, navigate to your Content Type
# In the Grooper Repository's Node Tree, navigate to your Content Type
#*<li class="fyi-bullet">Most typically Export Behaviors are added to '''Content Models''' but can be added to '''Content Categories''' and '''Document Types''' for more Document Type-specific export scenarios.
# If the Content Type does not already have an '''Export Behavior''', add one:
# If the Content Type does not already have an '''Export Behavior''', add one:
#* Right-click the Content Type and select '''Add Behavior'''
#* Right-click the Content Type and select '''Add Behavior'''
Line 264: Line 260:
=== Step 2: Add a Data Export definition ===
=== Step 2: Add a Data Export definition ===


# Select the [[Content Type]] in the tree
# Select the Content Type in the Node Tree.
# In the [[Property Grid]], locate the '''Export Behavior''' section
# In the Property Grid, locate the '''Export Behavior''' section
# Click the ellipsis button ('''...''') next to the "Export Definitions" property
# Open the "Export Definitions" editor (Click the '''...''' button)
# In the '''Object Collection Editor''' dialog that appears:
# In the '''Object Collection Editor''' dialog that appears:
#* Click the '''Add''' dropdown button
#* Click the '''Add''' dropdown button
Line 285: Line 281:
=== Step 4: Configure Table Mappings ===
=== Step 4: Configure Table Mappings ===


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


# In the Data Export properties, click the ellipsis button ('''...''') next to "Table Mappings"
# In the Data Export properties, open the "Table Mappings" editor (Click the '''...''' button)
# In the Object Collection Editor:
# In the Table Mappings Editor:
#* Click '''Add''' to create a new [[Table Mapping]]
#* Click '''Add''' to create a new Table Mapping
#* Configure the following properties for each 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
#** '''Source Scope''': Select the Data Model (or Data Section or Data Table from your Data Model) to export
#** '''Table Name''': Enter the name of the target database table
#** '''Table Name''': Enter the name of the target database table
#** '''Column Mappings''': Map each field to a corresponding database column
#** '''Column Mappings''': Map each field to a corresponding database column
Line 299: Line 295:
# Click '''OK''' when all mappings are configured
# Click '''OK''' when all mappings are configured


{{Note|You can add multiple table mappings to export data to several tables. For example, export invoice headers to an "Invoices" table and line items to an "InvoiceLineItems" table.}}
*<li class="fyi-bullet">Note: You can add multiple Table Mappings to export data to several tables. For example, export invoice headers to an "Invoices" table and line items to an "InvoiceLineItems" table.
 
===== Choosing the right Source Scope =====
 
The "Source Scope" property in a Table Mapping determines which Data Elements from your Data Model will be exported and how many rows will be created in the database. Selecting the correct scope is critical to achieving the desired export structure.
 
'''Available scope option:'''
 
; Data Model
: Exports '''one row per document''' to the database table. Choose this scope when exporting document-level information that appears once per document.
: '''Examples:''' Invoice header data (invoice number, date, vendor, total amount), loan application details, purchase order summary information.
 
; Data Section
: Exports '''one row per section instance''' to the database table. Choose this scope when exporting repeating sections that may appear multiple times within a document.
: '''Examples:''' Multiple addresses on a form, repeating claim details in an insurance document, multiple signatories on a contract.
 
; Data Table
: Exports '''one row per table row''' to the database table. Choose this scope when exporting line item data organized in a table structure.
: '''Examples:''' Invoice line items, purchase order details, transaction histories, itemized charges.
 
===== Decision guide =====
 
Ask yourself these questions to determine the appropriate scope:
 
'''Question 1: How many times does this data appear in a document?'''
* '''Once per document''' → Use '''Data Model'''
* '''Multiple instances, but not in a table format''' → Use '''Data Section'''
* '''Multiple rows in a table structure''' → Use '''Data Table'''
 
'''Question 2: What does each row in my database table represent?'''
* '''A document''' → Use '''Data Model'''
* '''A repeating section or entity within a document''' → Use '''Data Section'''
* '''A line item or table row''' → Use '''Data Table'''
 
===== Common scenarios =====
 
'''Scenario 1: Exporting invoice headers and line items'''
 
You have an invoice with header information and multiple line items. You need two [[Table Mapping]]s:
 
# '''Header Mapping'''
#* Source Scope: Data Model (the root invoice Data Model)
#* Target Table: "Invoices"
#* Result: One row per invoice document
# '''Line Items Mapping'''
#* Source Scope: LineItems (a Data Table in your Data Model)
#* Target Table: "InvoiceLineItems"
#* Result: Multiple rows per invoice, one for each line item
 
'''Scenario 2: Exporting loan applications with multiple borrowers'''
 
You have a loan application that can have one or more borrowers (co-applicants). You need two Table Mappings:
 
# '''Application Mapping'''
#* Source Scope: Data Model (the root loan application Data Model)
#* Target Table: "LoanApplications"
#* Result: One row per loan application document
# '''Borrower Mapping'''
#* Source Scope: Borrowers (a multi-instance Data Section in your Data Model)
#* Target Table: "Borrowers"
#* Result: One or more rows per application, one for each borrower section
 
'''Scenario 3: Exporting simple form data'''
 
You have a single-page form with customer information (name, address, phone, etc.) and no repeating elements. You need one Table Mapping:
 
# '''Customer Mapping'''
#* Source Scope: Data Model (the root customer Data Model)
#* Target Table: "Customers"
#* Result: One row per document
 
*<li class="fyi-bullet>Tip: When exporting to multiple related tables, use Return Values to capture auto-generated IDs from parent tables and include them in child table mappings to maintain referential integrity.


=== Step 5: Configure Custom Statements (Optional) ===
=== Step 5: Configure Custom Statements (Optional) ===
Line 305: Line 372:
Use custom statements to execute additional SQL logic after table mappings are processed.
Use custom statements to execute additional SQL logic after table mappings are processed.


# In the Data Export properties, click the ellipsis button ('''...''') next to "Custom Statements"
# In the Data Export properties, open the "Custom Statements" editor (Click the '''...''' button)
# In the Object Collection Editor:
# In the Custom Statements Collection Editor:
#* Click '''Add''' to create a new [[Custom Statement]]
#* Click '''Add''' to create a new Custom Statement
#* Enter your SQL statement in the "Statement" property
#* Enter your SQL statement in the "Statement" property
#* Use expressions to reference fields from your [[Data Model]] (e.g., <code>@InvoiceNumber</code>)
#* Use expressions to reference fields from your Data Model (e.g., <code>@InvoiceNumber</code>)
# Click '''OK''' when finished
# Click '''OK''' when finished


Line 323: Line 390:


If you need to dynamically route exports to different databases based on document data, configure the "Alternate Database" property.
If you need to dynamically route exports to different databases based on document data, configure the "Alternate Database" property.
*<li class="attn-bullet">Be aware: This feature is only supported for MS SQL Data Connections. The alternate database must have the same schema as the primary database.


# In the Data Export properties, locate the "Alternate Database" property
# In the Data Export properties, locate the "Alternate Database" property
# Click the ellipsis button ('''...''') to open the expression editor
# Open the expression editor (Click the '''...''' button)
# Enter a code expression that evaluates to the target database name
# Enter a code expression that evaluates to the target database name
# Click '''OK'''
# Click '''OK'''
{{Note|This feature is only supported for MS SQL [[Data Connection]]s. The alternate database must have the same schema as the primary database.}}


'''Example expression:'''
'''Example expression:'''
Line 341: Line 407:
=== Step 7: Set the Link Name (Optional) ===
=== Step 7: Set the Link Name (Optional) ===


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


# In the Data Export properties, locate the "Link Name" property
# In the Data Export properties, locate the "Link Name" property
Line 350: Line 416:


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


=== Troubleshooting ===
=== Troubleshooting ===


* '''Connection errors''': Verify that your [[Data Connection]] is configured correctly and that the database server is accessible
* '''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
* '''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
* '''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
* '''Duplicate key violations''': Configure "Match Columns" and "Match Action" to handle duplicates appropriately


Line 370: Line 436:
* [[Data Connection]]
* [[Data Connection]]
* [[Export]] activity
* [[Export]] activity
<!---
==About==
{|class="download-box"
|
[[File:Asset 22@4x.png]]
|
You may download and import the file(s) below into your own Grooper environment (version 2024).  There is a '''Batch''' with the example document(s) discussed in this tutorial, as well as a '''Project''' configured according to its instructions.
<br>
Please upload the '''Project''' to your '''Grooper''' environment before uploading the '''Batch'''. This will allow the documents within the '''Batch''' to maintain their classification status.
* [[Media:2025_Wiki_Data-Export_Project.zip]]
* [[Media:2025_Wiki_Data-Export_Batch.zip]]
|}
The most important goal of '''Grooper''' is to deliver accurate data to line of business systems that allow the information to be integrated into impactful business decisioning. [https://en.wikipedia.org/wiki/Table_(information) Tables] in [https://en.wikipedia.org/wiki/Database databases] remain, to this day, one of the main vessels by which this information is stored.  '''''Data Export''''' is one of the main ways to deliver data collected in Grooper.
{|cellspacing=10 cellpadding=5
|-style="text-align:center"
|
'''''Data Export''''' allows us to take a document...
|
...and its extracted data...
|
...and map that data to column locations in a database table.
|-
|valign=top|
[[File:Data-export-about-01.png|border]]
|valign=top|
[[File:2023_Data-Export_01_About_01.png|border]]
|valign=top|
[[File:Data-export-about-03.png|border]]
|}
There are three important things to understand when using and configuring '''''Data Export''''' to export data to a database:
* The '''Export''' activity.
* '''Data Elements'''
* '''Data Connections'''
=== The Export Activity ===
'''Grooper's''' '''Export''' activity is the mechanism by which Grooper-processed document content is delivered to an external storage platform.  Export configurations are defined by adding '''''Export Type''''' definitions to '''''Export Behaviors'''''.  '''''Data Export''''' is the '''''Export Type''''' designed to export '''Batch Folder''' document data collected by the '''Extract''' activity to a [https://en.wikipedia.org/wiki/Microsoft_SQL_Server Microsoft SQL Server] or [https://en.wikipedia.org/wiki/Open_Database_Connectivity ODBC-compliant] database server.
For more information on configuring '''''Export Behaviors''''', please visit the full '''[[Export]]''' activity article.
=== Data Elements ===
'''''Data Export''''' is the chief delivery device for "collection" elements. Data is collected in Grooper by executing the '''Extract''' activity, extracting values from a '''Batch Folder''' according to its classified '''Document Type's''' '''Data Model'''.
A '''Data Model''' in Grooper is a digital representation of document data targeted for extraction, defining the data structure for a '''Content Type''' in a '''Content Model'''.  '''Data Models''' are objects comprised of '''Data Element''' objects, including:
* '''Data Fields''' used to target single field values on a document.
* '''Data Tables''' and their child '''Data Columns''' used to target tabular data on a document.
* '''Data Sections''' used to divide a document into sections to simplify extraction logic and/or target repeating sections of extractable '''Data Elements''' on a single document.
With '''Data Models''' and their child '''Data Elements''' configured, Grooper collects values using the '''Extract''' activity. 
Depending on the '''Content Type''' hierarchy in a '''Content Model''' and/or '''Data Element''' hierarchy in a '''Data Model''', there will be a collection, or "set", of values for varying data scope of a fully extracted '''Data Model's''' hierarchy.  That may be the full data scope of the '''Data Model''', including any inherited '''Data Elements''' inherited from parent '''Data Models'''.  It may be a narrower scope of '''Data Elements''' like a child '''Data Section''' comprised of its own child '''Data Fields'''.
Understanding this will be important as '''''Data Export''''' has the ability to take full advantage of Grooper's hierarchical data modeling to flatten complex and inherited data structures.  Understanding '''Data Element''' hierarchy and scope will also be critical when exporting data from a single document to multiple different database tables to ensure the right data exports to the right places.
=== Data Connections ===
'''''Data Export''''' uses a configured '''Data Connection''' object to establish a link to SQL or ODBC compliant database tables in a database and intelligently populate said tables.  Once this connection is established, collected '''Data Elements''' can be mapped to corresponding column locations in one or multiple database tables.  Much of '''''Data Export's''''' configuration is assigning these data mappings.  The '''Data Connection''' presents these mappable data endpoints to Grooper as well as allowing data content to flow from Grooper to the database table when the '''Export''' activity processes each '''Batch Folder''' in a '''Batch'''.
Furthermore, not only can Grooper connect to existing databases using a '''Data Connection''', but it can create whole new databases as well as database tables once a connection to the database server is established.
We discuss how to create '''Data Connections''', add a new database from a '''Data Connection''', and add a new database table from a '''Data Connection''' in the [[#Configuring a Data Connection]] tutorial below.
{|class="fyi-box"
|
'''FYI'''
|
We have improved integration with PostgreSQL, Db2, MySQL, and Oracle using the ODBC connection type in version 2021!
While not fully supported in previous versions of Grooper, you can now connect to these data sources with a '''Data Connection''' seamlessly in Grooper, allowing for full database export operations via '''''Data Export'''''.
|}
== How To ==
In the following tutorials, we discuss an an example of how to setup an '''''Export Behavior''''', using '''''Data Export'''''.
In this example are a couple of different document formats whose data will be collected by a single '''Content Model'''.  This will help illustrate two key ''distinctions'' mentioned above.
* The first document (the "Employee Report) will demonstrate the ''flattening'' of a data hierarchy.
* The second (the "Personnel Information Report") will give us an avenue to export to multiple tables at once.
{|
|
[[File:Employee Report Pic.png|center|middle|500px]]
|
[[File:Personnel Information Pic.png|center|middle|544px]]
|}
The example documents have already been classified and their data extracted.  Before we get into '''''Data Export''''' configuration specifics, there's a few things we need to understand about "the story so far"
* The forms used in this example and how they are formatted.
* The '''Content Model''' used in this example, its '''Document Types''' and how their '''Data Models''' represent the data we want to collect.
* The extracted index data, which will be exported to a database.
=== Understanding the Forms ===
==== Document 1:  Employee Report ====
The thing to understand about this document is some of its data share a "one-to-many" relationship.
Some of the data is described as "single instance" data.  These are individual fields like "Employee Last Name", "Employee First Name" and "Employee ID".  For each document, there is only one value for each of these fields.  These values are only listed once, and hence only collected once during extraction.
Some of the data, however, is described as "multi-instance" data.  The "Earnings" table displays a dynamic amount of rows, for which there may be a varying number of data for its columns ("Code Desc", "MTD", "QTD", "YTD") depending on how many rows are in the table.  There are multiple instances of the "YTD" value for the whole table (and therefore the whole document).
The single instance data, as a result of only being listed once on the document, will only be collected once, but needs to be married to each row of information from the table, in one way or another.  The "one" "Employee ID" value, for example, pertains to the "many" different table rows.
This document is meant to show how to flatten data structures.  While the single instance data is only collected once, it will be reported many times upon exporting to a database table.
[[File:database_export_002.png]]
==== Document 2:  Personnel Information Report ====
The second document is essentially one big table of personnel information (name, address, email, phone number and the like). 
While we ultimately want to collect data from all rows in this table, there are potentially two sets of information here.  Some of it is generic personnel information, but some of it is "personally identifiable information" or PII.
This information should be protected for legal reasons.
As a result, we will export collected data to two database tables (with the assumption that the second table is "protected".)
This document is meant to demonstrate how to export to multiple tables via one '''''Export Behavior'''''.
{|class="attn-box"
|
&#9888;
|
DISCLAIMER:  The author of this article is not a lawyer.
For educational purposes we divided "Personnel Information" into two sets:  "non-PII" and "PII"
* non-PII:  Employee ID, Phone Number, E-mail address, IP Address, Gender, ZIP
* PII: First Name, Last Name, SSN, Street Number, Street Name, City, State.
'''''DO NOT TAKE THIS TO MEAN THIS INFORMATION IS OR IS NOT PERSONALLY IDENTIFIABLE INFORMATION.'''''
This division was done purely for educational purposes to demonstrate a concept.
PII gets tricky in the real world.  For example, an IP Address would not normally qualify as PII by itself, but could when combined with other personal information.  Please consult your own legal department to determine what data you're collecting is PII and should be protected more securely.
|}
[[File:database_export_003.png ]]
=== Understanding the Content Model ===
The '''Content Model''' provided for this tutorial is named "Example Model - Data Export".  This '''Content Model''' is designed to extract the data for these two different kinds of documents, each represented by its own '''Document Type'''.
==== The Employee Report Document Type ====
# The "Employee Report" '''Document Type'''' represents our "Employee Report" document.
#* This is the one we will use to demonstrate flattening a data structure that shares a one-to-many relationship on the document.
# It has its own child '''Data Model''' with '''Data Elements''' already configured for extraction.
# For the individual fields, represented once in the document, there are three corresponding '''Data Field''' elements:
#* "Last Name"
#* "First Name"
#* "Employee ID"
# For the tabular data, a '''Data Table''' named "Earnings" is established, with four '''Data Column''' elements, corresponding to the columns on the document:
#* "Code Desc"
#* "MTD"
#* "QTD"
#* "YTD"
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmbz6912f004eu30ihxjs6zdw?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Understanding the Content Model" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
==== The Personnel Info Report Document Type ====
# The "Personnel Info Report" '''Document Type''' represents our "personnel information" documents.
#* This is the one we will use to demonstrate exporting multiple table structures to multiple databases from a single document.
# It too has its own child '''Data Model''' with '''Data Elements''' already configured for extraction.
# The "non-PII" '''Data Table''' will extract data from each table row on the document for non-protected personnel information, as described by its child '''Data Column''' elements:
#* "Employee ID"
#* "Phone Number"
#* "EMail"
#* "Gender"
#* "Zip"
# The "PII" '''Data Table''' will extract data from each table row on the document for protected personnel information, as described by its child '''Data Column''' elements:
#* "Employee ID"
#* "First Name"
#* "Last Name"
#* "SSN"
#* "Street Number"
#* "Street Name"
#* "City"
#* "State"
# This set-up uses a single table extractor to collect each row on the document, but reports it to two different '''Data Table''' objects in Grooper (the "non-PII" '''Data Table''' and the "PII" '''Data Table''').  This will ultimately allow us to parse data from these columns, and place the PII related information into a separate database.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmbzaaefn00qpu30iu9q833ue?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - The Personnel Info Document Type" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
=== Verifying Index Data ===
Before the '''Database Export''' activity can send data, it must have data!
It's easy to get in the habit of testing extraction on a '''Data Field''' or a '''Data Model''' and feel good about the results, but it must be understood that the information displayed when doing so is in memory, or temporary. When testing a '''''Data Export''''' configuration, it's a good idea to ensure extracted data is actually present for document '''Batch Folders''' whose data you want to export.
When the '''Extract''' activity runs, it executes all extraction logic for the '''Data Model''' tied to a '''Batch Folder's''' classified '''Document Type.''' For each '''Batch Folder''' document, it creates "Index Data" and marries it to the '''Batch Folder''' via a '''[https://en.wikipedia.org/wiki/JSON JSON]''' file called ''Grooper.DocumentData.json''.
A couple of ways to verify its existence are as follows:
==== Option 1 ====
# First, navigate to the '''Batch''' object in the node tree.
# From there, click on the "Viewer" tab.
# Click the "Open in review page" button.
# Click the "Data Viewer" tab and from there you can review review the data.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmbzhs1k500gh010iqe38fcva?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Verifying Index Data - Option I" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
{|class="fyi-box"
|
'''FYI'''
|
Were the document classified (the '''Batch Folder''' assigned a '''Document Type'''), but not successfully extracted (the '''Extract''' activity not applied), the data structure would be present, but the fields empty.
At that point the '''Batch Folder''' would have the '''Document Type's''' '''Data Model''' associated to it, but would not have applied its extraction logic to collect values (That's what the '''Extract''' activity is for).
|}
==== Option 2 ====
Another means of verifying is to actually view the file created by the '''Extract''' activity and stored in the Grooper repository's file store location.
# In this case, select a '''Batch Folder''' that is a child of the '''Batch'''.  In this case the "Employee Report(1)" is selected.
# Click the "Advanced" tab.
# Select the JSON file in the files panel.
# Click the "Display file" button.
A new tab will open in your browser and you will be shown the contents of the JSON file. This is the file the '''Extract''' activity generates when it processes a '''Batch Folder'''.  It serializes all the extracted '''Data Elements'''' values for the document's '''Data Model'''.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmbzl50e000iz010i42zm7qvw?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Verifying Index Data - Option II" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
=== Configuring a Data Connection ===
In order for the '''''Data Export''''' to run, it first needs an established connection to a database and subsequent table(s).
'''Grooper''' can connect to an existing database and import references to its tables using a '''Data Connection''' object.  Once connected to the database server, you can even have '''Grooper''' create a database AND create tables based on '''Data Model''' structures present in '''Grooper'''!
In the following tutorial we will cover how to:
# Create a new '''Data Connection''' object.
# Connect to a database server using the '''Data Connection'''.
# Create a new database from the '''Data Connection'''.
# Create a database table using the '''Data Models''' in our example '''Content Model'''.
It is worth noting that this article cannot tell you specifics about permissions in your own environment. The configuration for this article uses Microsoft SQL Server and has given the active Active Directory user full DB Admin privileges to the SQL environment.
==== Create a Data Connection ====
New '''Data Connections''' can be added to your Project, or within a created Folder object within the Project.
# Right click the Project.
#* If you want to keep things better organized, as we do in this example, right-click the Project and create a Folder object called, "Connections"; here, you can right-click the folder and add the Data Connection here.
# Select "Add" then "Data Connection..."
# The "Add New Data Connection" window will appear.
# Give the new '''Data Connection''' a name.
#* We've named ours "DB Export"
# Press "OK" when finished.
# This will add the new '''Data Connection''' to the '''Global Resources''' folder.
# Next, we will configure connection settings so that Grooper can interoperate with our SQL server.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc0nx89z02a3t40i1isnw78y?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Create a Data Connection" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
==== Configure Connection Settings ====
Regardless whether you want to connect to an existing database or create a new one from Grooper, your first step is always the same.  You must first connect to a database server.
Grooper can connect to Microsoft SQL servers or any ODBC (Open Database Connectivity) compliant data source.  For the purposes of this tutorial, we will connect to a SQL server.
# However, you can choose to connect to a SQL server or ODBC server using the '''''Connection Settings''''' property.
# Using the dropdown menu, choose either ''SQL Server'' or ''ODBC''
#* Again, we will be connecting to a SQL server.  So, ''SQL Server'' is selected.
Next, we need to define settings to access the database server.  All you really need to do this is the server's name and access rights.
# Expand the '''''Connection Settings''''' property.
# Using the '''''Server Name''''' property, enter the SQL server name.
#* I was a little on the lazy side for this article and just connected to the SQL Express instance created for the Grooper repository database on my local machine.  This is almost assuredly ''not'' what you want to do in a production environment, but it will work for testing purposes.
# You will also need access rights to the SQL server.  Using this '''Data Connection''' object, Grooper is going to act as if it were a user, giving Grooper the capabilities to do things like add and drop tables, run queries, and more.  Just like a user needs SQL access credentials, so does Grooper.
#* If the active Windows user has Active Directory rights to the SQL server, Windows will pass through your credentials.  You can leave the '''''User Name''''' and '''''Password''''' blank in that case.
#* Otherwise, you'll need to enter a '''''User Name''''' and '''''Password''''' to access the database server here.
# Go ahead and hit "Save" at this point.
That's it!  You're officially connected to the database server now.  We can now connect to existing databases, import references to their tables (Keep this in the back of your mind.  This will be important later.), create new databases, and new database tables.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc0urn3q000jyo0iouufq6tw?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Configure Connection Settings" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
==== Create a New Database from the Data Connection ====
At this point, you have two options:
# Connect to an existing database
# Create a new database
If you wanted to connect to an existing database, it's very easy.
# Select the '''''Database Name''''' property.
# Either type in the database's name or select it from the drop down menu.
#* FYI: Grooper has to ping the database in order to populate the dropdown list.  Depending on the size of your SQL server, it's often quickest to just type in the database name.
However, now that we're connected to the database, you can also create a brand new database!
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc1zx59j011ry50iv97atjwk?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Create a New Database from a Data Connection" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
===== Create a New Database from the Data Connection =====
# To create a new database, press the "Create Database..." button.
# This will bring up the "Create Database" window.
# Using the '''''Database Name''''' property, name the database whatever you like.
#* We named ours "Export_Example_DB"
# Press the "Execute" button to create the database.
# You will see the newly created database's name populate the '''''Database Name''''' property.
# However, as a newly formed, infant database, it has no database tables.  You can see, the "Database Tables" panel is empty.  Next, we will add some database tables using the data structures of the '''Data Models''' in our example '''Content Model'''.
{|class="fyi-box"
|
'''FYI'''
|
If you connect to the server in Microsoft SQL Management Studio, you can verify the database is created.
See here, the "Export_Example_DB" is added to the list of databases.  Through the '''Data Connection''' Grooper has a direct connection to this SQL environment to add and alter databases.
|}
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc237f2r01j7y50imj25p8sm?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Create a New Database from the Data Connection" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
==== Create Database Tables from the Data Connection ====
We will end up creating three database tables by the end of this section:
# A table for the "Employee Report" '''Document Type's''' extracted '''Data Elements''' with its "one-to-many" related data elements flattened to a single table structure.
# A table for the "Personnel Info Report" '''Document Type's''' extracted "non-PII" '''Data Table'''.
# A table for the "Personnel Info Report" '''Document Type's''' extracted "PII" '''Data Table'''.
===== Table 1:  Employee Report Data =====
# To create a new database table, press the "Create Table..." button.
# This will bring up the "Create Table" window.
# Database tables are created from Grooper using '''Data Elements''' from a '''Data Model'''.  The '''Data Model's''' '''Data Fields''' and/or '''Data Columns''' will form the columns of the SQL table, housing extracted values from each document folder upon export.  First, you must define the '''Content Type''' (e.g. a '''Content Model''' or one of its '''Document Types''') whose '''Data Model''' you want to use to create the database table.
# The '''''Data Element''''' property's dropdown menu will present you a mini Node Tree view to select a '''Data Container''' from your '''Data Model'''.
#* In our case, we will select the "Earnings" '''Data Table''' from our "Data Export - Content Model"'s '''Data Model'''.
#* Why didn't we choose the parent '''Data Model'''?  It's all about '''Data Model''' hierarchy and scope.  We want access to all the '''Data Elements''' in the "Earnings" '''Data Table'''.  If we chose the parent '''Data Model''', we would only have access to its immediate '''Data Elements''', which would not include any of the '''Data Elements''' we want to export.
For the way we will want our table structured, we will need to consider the '''''Create Mode''''' property. In the case of the "Earnings" table, ''Simple'' would create a table based only on the this one table's structure. ''Recursive'' is for more complex tables that have interconnected referencing, which this does not have. However, ''Flatten'' will be our best option. This will take the information from the '''Data Columns''' from this table, and ''flatten'' it with '''Data Fields''' that are siblings to the parent '''Data Table'''. In this case, there are three '''Data Fields''' in the "Header Info" '''Data Section''', which is a sibling to the "Earnings" '''Data Table'''. These three fields will be ''flattened'' into a single row of information with the other '''Data Columns''' from the "Earnings" '''Data Table'''.
# Click the drop-down for the '''''Create Mode''''' property.
# Select the ''Flatten'' option.
# Here's where '''Grooper''' is doing the hard work for us. Notice in the "Review/Edit SQL Statement" window the SQL Statement required to create our table is already written for us.
# However, you can edit this statement, if need be.
#* For example, at the time of writing this article there was a bug involving the "Employee ID" '''Data Field'''.  It's '''''Value Type''''' in Grooper is set to ''Int16''.  This was not properly converting to a "smallint" SQL data type for the corresponding SQL column.  So we added <code>smallint</code> to the SQL Statement after <code>[Employee ID]</code>.
# Click the "Run SQL Statement" button to create the table.
# Upon successfully creating the table, the "Database Tables" panel will have our newly created table listed.</li>
#* It will initially display with a red dot on the icon. This will change to a green dot when we import this table's reference (more on that later).
# The "Table Columns" panel will display the data structure of the table, listing the SQL table's columns, their data types and other information.
# The "Data Preview" panel will display data within the table.
#* Because this table was just created, it will not have any data to display.  We haven't exported any data to it yet!
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc2fycat014xwu0iy634u2fm?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Table 1: Employee Data" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
===== Table 2: Personnel Info Report non-PII Data =====
For the other two tables, it's mostly a repeat of the same steps, just taking care to select the appropriate '''Content Type''' and '''Data Element''' scope.
# To create the second database table, right-click the '''Data Connection''' and choose "Create Table".
# Click the drop-down for the '''''Data Element''''' property ...
# ... and choose the "non-PII" '''Data Table'''.
# Click the "Execute" button.
#* We don't need to worry about the '''''Create Mode''''' property in this case as we only need the contents of the selected '''Data Table'''. ''Simple'' would be a perfectly fine setting, and is technically more accurate here, but leaving it at ''Flatten'' won't technically do anything.
# Success!  The table is now successfully created.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc4wcp9m005w140io4wm5gni?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Table 2: Personnel Info Report non-PII Data." allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
===== Table 3: Personnel Info Report PII Data =====
This database table can be created with the exact same steps as described above with just one key difference:
# The '''''Data Element''''' property will be set to the "PII" '''Data Table''' ''not'' the "non-PII" '''Data Table'''.
We now have our third database table added to the SQL database.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc533ign020uy80ilcvhqbph?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Table 3: Personnel Info Report PII Data" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
=== Configuring Export Behaviors for Data Export ===
'''''Data Export''''' is one of the '''''Export Type''''' options when configuring an '''''Export Behavior'''''.  '''''Export Behaviors''''' control what document content for a '''Batch Folder''' is exported where, according to its classified '''Document Type'''.  As such, in order to configure a '''''Data Export''''', you must first configure an '''''Export Behavior''''' for a '''Content Type''' (a '''Content Model''' or its child '''Content Categories''' or '''Document Types''').
In our case, we want to perform two different kinds of export, depending on the document '''Batch Folder's''' classified '''Document Type'''.
* For the "Employee Report" '''Document Type''', we want to export its collected '''Data Elements''' to our first database table.
* For the "Personnel Info Report" '''Document Type''', we want to export its collected '''Data Elements''' (which are collected using an entirely different '''Data Model''' and have an entirely different data structure) to our second and third database table.
The basic idea behind '''''Export Behaviors''''' is, based on kind of document you're looking at, you can tell Grooper how you want to export it.
'''''Export Behaviors''''' can be configured in one of two ways:
# Using the '''''Behaviors''''' property of a '''Content Type''' object
#* A '''Content Model'''
#* A '''Content Category'''
#* Or, a '''Document Type'''
# As part of the '''Export''' activity's property configuration
When the '''Export''' activity processes each '''Batch Folder''' it will execute the '''''Export Behaviors''''', according to their configuration settings.
{|class="fyi-box"
|
'''FYI'''
|
In general, users will choose to configure '''''Export Behaviors''''' either on the '''Content Type''' object it applies to ''or'' local to the '''Export''' activity step in a '''Batch Process'''. 
This may just boil down to personal preference.  There is no functional difference between an '''''Export Behavior''''' configured on a '''Content Type''' or an '''''Export Behavior''''' configured on an '''Export Step''', upon completing their configuration.  In either case, they will accomplish the same goal.
However, it is possible to configure '''''Export Behaviors''''', in both locations.  If you do this, you will need to understand the '''Export''' activity's '''''Shared Behavior Mode''''' property options.  This will effect if and how two '''''Export Behaviors''''' configured for the same '''Content Type''' will execute.  Please visit the '''[[Export]]''' article for more information.
|}
==== Add an Export Behavior ====
===== Option 1: Content Type Export Behaviors =====
An '''''Export Behavior''''' configuration can be added to any '''Content Type''' object (i.e. '''Content Models''', '''Content Categories''', and '''Document Types''') using its '''''Behaviors''''' property.  Doing so will control how a '''Document Type''' "behaves" upon export.
# Select the '''Content Type''' whose '''''Export Behavior'''' you want to configure in the Node Tree.
#* We will start by configuring an '''''Export Behavior''''' for the "Employee Report" '''Document Type'''.
# To add an '''''Export Behavior''''', first select the '''''Behaviors''''' property.
# Then,m press the ellipsis button at the end of the property.
# This will bring up the '''''Behaviors''''' collection editor window.
# Press the "Add" button.
# Select ''Export Behavior''.
#* FYI: Children '''Content Type''' objects will inherit export settings from their parent '''Content Type's''' '''''Export Behavior''''' configuration
#* Also, you can only configure one '''''Export Behavior''''' per '''Content Type''' object.  However, you can configure an '''''Export Behavior''''' for any '''Content Type''' in a '''Content Model'''.  Functionally, this is how you add multiple '''''Export Behaviors''''' for a single '''Content Model'''.
#** For example, our two '''Document Types''' need different '''''Export Behavior''''' configurations.  We would ''''not''''' want to configure their parent '''Content Model's''' '''''Export Behavior'''''.  That would apply that single export configuration to ''all'' '''Document Types'''.  That's not going to work for us.  The "Employee Report" documents' data need to go to one location and the "Personnel Info Report" documents' data need to go somewhere entirely different.  Instead, we will end up configuring the '''''Behaviors''''' property of both '''Document Types''' individually.  Thus, we end up with two '''''Export Behavior''''' configurations for the '''Content Model'''.
# You will see the '''''Export Behavior''''' added to the '''''Behaviors''''' list.
# Selecting it, you can now add one or more '''''Export Definitions''''' with the '''''Export Definitions''''' property.
#* Next, we will add a '''''Data Export''''' definition.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc56jr1g01hsxf0ij1et0m4q?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Option 1: Content Type Export Behaviors" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
===== Option 2: Export Activity Export Behaviors =====
'''''Export Behaviors''''' can also be configured as part of the '''Export''' activity's configuration.  These are called "local" '''''Export Behaviors'''''.  They are local to the '''Export''' activity step in the '''Batch Process'''.
'''''PLEASE NOTE''''' that the following instructions are only to be followed if you choose to set '''Export Behaviors''' on the '''Batch Process Step''' instead of on the '''Content Type''' itself. You do not need to follow these instructions if you choose to set the '''Export Behaviors''' on the '''Content Types''', which is how this article will continue. It's worth understanding that if you set '''Export Behaviors''' on '''Content Types''' themselves, you as a result do not need to choose which '''Content Type''' the '''Export Behavior''' will apply to. However, when setting '''Export Behaviors''' on '''Batch Process Steps''', the first thing you configure after adding the '''Export Behavior''' is which '''Content Type''' the behavior should apply to. You can set multiple '''Export Behaviors''' that affect multiple '''Content Types''' on the '''Batch Process Step''', or you can configure individual '''Export Behaviors''' on individual '''Content Types'''. Again, in the case of this article, we will be setting the '''Export Behaviors''' on each '''Content Type''', instead of on the '''Batch Process Step'''.
# For example, here we have a working '''Batch Process''' selected in the Node Tree.
#* This is a simple '''Batch Process''' that could have been used to process these documents, recognizing their text, classifying the document '''Batch Folders''', and extracting data from them.  The last step in this '''Batch Process''' is an '''Export''' step.
# Select the '''Export''' step of the '''Batch Process'''.
# To add an '''''Export Behavior''''', select the '''''Export Behaviors''''' property.
# Then, press the ellipsis button at the end of the property.
# This will bring up the '''''Export Behaviors''''' collection editor window.
# Press the "Add" button to add a new '''''Export Behavior'''''
# An '''''Export Behavior''''' will be added to the list.
# With the '''''Export Behavior''''' selected you ''must'' define which '''Content Type''' the behavior applies to using the '''''Content Type''''' property.
#* Note in both cases, a '''Content Type''' is involved in configuring '''''Export Behaviors'''''.
#* Whether local to the '''Export''' activity or as part of a '''Content Model's''' configuration, Grooper needs to know what to do upon export, given a certain '''Content Type'''.  Once Grooper knows what kind of document it's looking at, we can then inform it what to do in terms of exporting its document content.
# Using the dropdown menu, select which '''Content Type''' scope should utilize the '''''Export Behavior''''' by selecting either a top-level parent '''Content Model''' or one of its child '''Content Categories''' or '''Document Types'''.
#* Keep in mind you can only select a single '''Content Type''' here.  You can only configure one '''''Export Behavior''''' per '''Content Type''' object.
#* Again, children '''Content Type''' objects will inherit export settings from their parent '''Content Type's''' '''''Export Behavior''''' configuration.  However, multiple '''''Export Behaviors''''' may be added locally to the '''Export''' activity.
#** For example, since both our '''Document Type''' needed a unique '''''Export Behavior''''' configuration, we would add one '''''Export Behavior''''' to the list for each one.
# Here, we have selected the "Employee Report" '''Document Type'''.  This '''''Export Behavior''''' would then only apply to '''Batch Folders''' of this '''Document Type'''.
# Once a '''Content Type''' is selected, you can add one more more '''''Export Definitions''''' with the '''''Export Definitions''''' property.
#* We will discuss adding a '''''Data Export''''' definition next.
# Click the "+" button.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc5971de01zk0b0i9n5xm9vy?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Option 2: Export Activity Export Behavior" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
==== Add an Export Definition ====
# We will choose to configure the '''''Export Behavior''''' using "Option 1", adding it to the "Employee Report" '''Document Type'''
# We will add the ''''Export Behavior''''' to its set of '''''Behaviors''''' properties.
Regardless if you choose to configure the '''''Export Behavior''''' on a '''Content Type''' object, or if you configure it local to to '''Export''' activity's configuration, your next step is adding an '''''Export Definition'''''.
# Once you've added an '''''Export Behavior''''', select the '''''Export Definitions''''' property.
# To add an '''''Export Definition''''', press the ellipsis button at the end of the property.
# This will bring up an '''''Export Definition''''' list editor to add one or more '''''Export Types'''''.
#* Next, we will add a '''''Data Export''''' definition to the list.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc5cxj7d001m1l0ivlocd903?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Add an Export Definition" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
==== Add a Data Export ====
'''''Export Definitions''''' functionally determine three things:
# '''Location''' - Where the document content ends up upon export.  In other words, the storage platform you're exporting to.
# '''Content''' - What document content is exported:  image content, full text content, and/or extracted data content.
# '''Format''' - What format the exported content takes, such as a PDF file or XML data file.
'''''Export Definitions''''' do this by adding one or more '''''Export Type''''' configurations to the definition list.  The '''''Export Type''''' you choose determines how you want to export content to which platform.  In our case, we want to use a '''Data Connection''' to export extracted document data ("Content") to a database table ("Location" and "Format").  We will add a '''''Data Export''''' to the definition list.
# To do this, press the "Add" button.
# Choose ''Data Export'' from the list.
# This will add an unconfigured '''''Data Export''''' to the '''''Export Definitions''''' list.
# For all '''''Data Export''''' configurations, the first step is configuring the '''''Connection''''' property.
# Use the dropdown menu to select a '''Data Connection''' from the node tree.
#* This will provide Grooper with the information required to connect to the external database upon export.
# With the '''Data Connection''' established, the next step is to map data from Grooper to a table in the database.
# Next, we will review using the '''''Table Mappings''''' property to map '''Data Elements''' in a '''Data Model''' to corresponding column locations in a database table.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc96uirg0001140i1z483ok0?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Add A Data Export" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
==== Table Mappings Example 1: Flattening a Data Model ====
We will continue configuring our "Employee Report" '''Document Type's''' '''''Data Export''''' first.  For this document, we have to deal with a "one-to-many" relationship between data that exists once on the document, and the dynamic data in the multiple rows in the "Earnings" table.
For this example, we will flatten the '''Data Model's''' element hierarchy.  By flattening the '''Data Model''', we can output all the document's data to a single database, despite the fact some of its data is present once on the document (the single instance '''Data Field'''' values) and some is more dynamic (mutli-instance '''Data Column''' values from our '''Data Table''').
We will essentially re-output the single instance values, marrying them to every row instance output for the table on the document.  Think of the single instance values as "document level".  These single values ("Last Name", "First Name", "Employee ID") pertain to the whole document.  Therefore, they also pertain to every row of information in the "Earnings" table.
[[File:Data-export-how-to-export-behaviors-13.png]]
We accomplish this feat through '''''Table Mappings'''''.  This will allow us to hook up the right '''Data Elements''' in a '''Data Model''' to the right database table columns upon exporting collected values.  Much like data hierarchy scope was important when we created this database, so will it be when we configure '''''Table Mappings''''' for export.
# With the '''''Data Export''''' selected, select the '''''Table Mappings''''' property.
# Press the ellipsis button at the end.
# This will bring up the "Table Mappings" collection editor.
# Press the "Add" button to add a new set of mappings.
# Use the '''''Source Scope''''' property to select the appropriate '''Data Element''' scope in the '''Content Type's''' '''Data Model'''.
# In our case, we're choosing the "Earnings" '''Data Table'''.
#* Selecting this '''Data Table''' as the scope will not only allow mapping to its '''Data Columns''', but all '''Data Fields''' up its logical hierarchical tree path (i.e. "Last Name", "First Name", and "Employee ID").
Next we need to define which database table we're mapping to.  There are now three databases in the database connected to our '''Data Connection'''.  Each of their table references have been imported to Grooper.  We need to pick which one we want to use.
# Choose which database table you want to map using the the '''''Target Table''''' property.
# Select a database table using the dropdown menu.
#* In our case the "Employee_Report_Earnings" database table.
#* FYI:  Without importing the database table's reference, creating the child '''Database Table''' object for our '''Data Connection''', you would not see the database table listed here.
Now that we have a source data scope selected (where we're mapping from) and a target database table selected (where we're mapping to), we will map the '''Data Elements''' in our '''Data Model's''' scope to columns in the targeted database table.
# Select the '''''Column Mappings''''' property.
# Press the ellipsis button at the end.
# This will bring up the "Column Map" editor.
# Each database table column will be listed as a property in the grid.
# Using the dropdown menu, you can select a corresponding '''Data Element''' from the '''Data Model's''' scope.
#* FYI:  Since we selected the "Earnings" '''Data Table''' as our scope, its direct children are listed as their simple '''Data Element''' names.  For example, "MTD" for the "MTD" '''Data Column'''.
#** For '''Data Elements''' inherited from a parent scope, they will be listed as ''"Scope Source"."Data Element"''.  For example, the "Last Name" '''Data Field''' was inherited from the "Employee Report" '''Document Type's''' base '''Data Model'''.  So, it is listed as "Employee_Report.Last_Name".
#* For the '''''Last Name''''' database column, we have selected the ''Employee_Report.Last_Name'' '''Data Element'''.
There is also a mapping shortcut to automatically assing mappings if a '''Data Element's''' name matches a database column's name.
# Right-click any property in the grid.
# Select "Auto-Map..."
# Database table columns will be automatically mapped to '''Data Elements''' as long as their names match.
#* Furthermore, since we created this database table from Grooper, using the same '''Data Model''', ''all'' of these column names and '''Data Element''' names will match.
# Press "OK" to finish configuring mappings.
We now have everything we need to export data from "Employee Report" documents to a database, using '''''Data Export'''''.
# Press "OK" on this and all subsequent windows to finalize the '''''Export Behavior''''' configuration.
# Don't forget to save the '''Content Type''' when you're done.
With this '''''Export Behavior''''' in place, whenever the '''Export''' activity processes an "Employee Report" '''Batch Folder''', data will be exported to the database as we've designed through these settings.
Before testing the '''Export''' step, in the next tab, we will configure another '''''Data Export''''' for the "Personnel Info Report" '''Document Type''' with its own set of database table mappings to export their extracted data to two different database tables.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmc9cy5ek00qfx40hwgvvvv1u?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Table Mappings Example 1: Flattening a Data Model" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
=== Table Mappings Example 2: Exporting to Multiple Database Tables ===
Despite the fact you can only set up one '''''Export Behavior''''' per '''Content Type''', you can add as many '''''Export Definitions''''' as you need.
In this example, we will take the extracted data from our "Personnel Info Report" '''Document Type''' and export PII information to one database table and non-PII information to a different table, using two '''''Data Export''''' definitions.
# We want to export data for the "Personnel Info Report" documents.  We will configure the '''''Export Behavior'''''' using the "Personnel Info Report" '''Document Type'''.
# We've already added the '''''Export Behavior''''' to the '''''Behaviors''''' collection list.
# We've added a single '''''Data Export''''' definition.
# We've already pointed the '''''Connection''''' property to our "DB Export" '''Connection Type'''.
# Next, we will assign the '''''Table Mappings'''''.
# Using the "Add" button, we've added a '''''Table Mapping''''' for the first of the two '''Data Table''' elements used to collect information from the report..
# For the '''''Scource Scope''''', we've selected the "non-PII" '''Data Table'''.
# We want to all the data Grooper collects for that '''Data Table''' to the "Personnel_Info_Report_non_PII" database table.  We have selected that database table for the '''''Target Table'''''.
# All that's left is to assign our '''''Column Mappings'''''.
# Next, we mapped our source '''Data Elements''' (The '''Data Columns''' in the "non-PII" '''Data Table''' in this case) to our target database table columns.
#* Since the names all matched, we just used the "Auto Map" feature.
# Press "OK" to finish.
Now we just need to set up mappings for the "PII" '''Data Table'''.  In this case, we're exporting to the same database, just two different database tables within it.  Since we're already collecting mappings for the database connected to using the "DB Export" '''Data Connection''', all we need to do as add another set of '''''Table Mappings'''''.
# Press the "Add" button to add a new set of mappings.
# For the second set of mappings, we've selected the "PII" '''Data Table''' for the '''''Source Scope'''''.
# Using this mapping, will push the extracted PII information to the "Personnel_Info_Report_PII" database table, which we have selected for the '''''Target Table'''''.
# Last but not least, we've mapped our source '''Data Elements''' to their target database table columns using the '''''Column Mappings''''' property.
{|class="fyi-box"
|
'''FYI'''
|
Here, we added two sets of table mappings to two database tables in the same database.  What if you wanted to add two sets of mappings to database tables in ''different'' databases?
That's easily doable.  You would need two '''Data Connections''', one to connect Grooper to each individual database.  Then, you would need to add two '''''Data Export''''' definitions, each one using one of the two '''Data Connections'''.  From there, you'd add the individual table mappings to each individual '''''Data Export'''''.
|}
We now have everything we need to export data from "Personnel Info Report" documents to two tables in a database, using '''''Data Export'''''.
# Press "OK" on this and all subsequent windows to finalize the '''''Export Behavior''''' configuration.
# Don't forget to save the '''Content Type''' when you're done.
Next, we will apply the '''Export''' activity to the document '''Batch Folders''' in our sample '''Batch''', which will export data according to their '''Document Type's''' '''''Export Behavior''''' using the '''''Data Export''''' definitions.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmcaodfm900tny10i760ljrrg?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Table Mappings Example 2: Exporting to Multiple Database Tables" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
=== Applying the Export Activity and Reviewing the Results ===
==== Process the Export Step ====
With the two '''''Export Behaviors''''' configured, we can now test our export.  '''''Export Behaviors''''' are executed by the '''Export''' activity.
# We will use this example '''Batch Process''' to demonstrate our '''''Data Export''''' configurations detailed in the previous tutorial.
# The '''Export''' activity in our '''Batch Process''' will apply our '''''Export Behavior''''' to every '''Batch Folder''' in the '''Batch'''.
# FYI:  Because we configured the '''''Export Behavior''''' on '''Content Type''' objects (using the two '''Document Types'''' '''''Behaviors''''' property editor), we ''do not'' have to configure the '''Export''' activity's local properties.
#* We've given Grooper all the information it needs to export content.  The '''Export''' activity will go through each '''Batch Folder''' in the '''Batch'''.  It will see the '''Batch Folders''' are classified with one of the '''Document Types''' in our '''Content Model''' and use their '''''Export Behavior''''' configuration settings to export document content.
We will test our export using the '''Export''' activity's "Activity Tester" tab.
# Expand the '''Batch Process''' to reveal its child '''Batch Step''' nodes.
# Select the '''Export''' activity step.
# Switch to the "Activity Tester" tab.
# Select the documents at the appropriate scope, and press the "Test the Activity" button.
#* You could also select the "Data Export - Batch" root "Batch Folder" and use the "Submit a job" button to process these documents through a job, which will allow it to funtion with multiple threads.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmcc038xt04034s0iox26blal?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Process the Export Step" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>
==== Review the Export ====
You can easily review our exported data in one of two ways:
* In the "Data Preview" panel of any of the '''Database Table''' reference objects.
* Connecting the database table in Microsoft SQL Server Management Studio.
# Here, we have the "DB Export" '''Data Connection''' object selected.
# You can see in the "Data Preview" window, now there's data there!
#* This is information Grooper collected and exported, using the '''''Data Export''''' table mappings we configured for the "Employee Report" '''Document Type's''' '''''Export Behavior'''''.
Using SQL Server Management Studio, we can also verify the data was exported.
# This is the exact same database table we have selected.
# Running a SELECT statement, we can see the exact same data we saw from Grooper is present in the database.
<div style="position: relative; box-sizing: content-box; max-height: 80vh; max-height: 80svh; width: 100%; aspect-ratio: 1.7777777777777777; padding: 40px 0 40px 0;"><iframe src="https://app.supademo.com/embed/cmcc28dwf04io4s0i3uq62i87?embed_v=2" loading="lazy" title="2025 - Data Export (Export Definition) - Review the Export" allow="clipboard-write" frameborder="0" webkitallowfullscreen="true" mozallowfullscreen="true" allowfullscreen style="position: absolute; top: 0; left: 0; width: 100%; height: 100%;"></iframe></div>

Latest revision as of 17:42, 6 February 2026

This article is about the current version of Grooper.

Note that some content may still need to be updated.

20252024 20232021

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 database Data Connection. This Export Definition is added to an Export Behavior on a stacks Content Model to automate the export of structured data to a database during the output Export step in a settings Batch Process.

  • Most typically Export Behaviors are added to Content Models but can be added to Content Categories and Document Types for more Document Type-specific export scenarios.

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. (Only if configured) Evaluates the "Alternate Database" expression to determine the target database
  3. Executes each "Table Mapping" in sequence, exporting rows to the specified tables
  4. (Only if configured) Executes any "Custom Statements" defined in the "Custom Statements" property
  5. (Only if configured) If any Table Mapping has "Return Values" configured, saves the updated data back to the document

Configuration basics

Data Export is primarily configured using the Connection and Table Mappings settings.

More advanced scenarios are accommodated by the Alternate Databases, Match Columns (and Match Actions) and Custom Statements settings.

Primary configuration settings

These settings are required for all database export scenarios.

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.

Advanced configuration settings

These settings are optional and allow for more advanced database export scenarios.

Match Columns and Match Actions

These settings are configured in the Table Mapping settings of a Data Export. Match Columns and Match Actions work together to control how Data Export handles duplicate records during the export process.

Match Columns

The "Match Columns" property specifies one or more database columns to check for existing records before inserting new data. When Match Columns are configured, the export process queries the target table to see if a row already exists with matching values.

How it works:

  1. Before exporting each row, a SELECT query is executed against the target table
  2. The query searches for existing rows where the specified Match Columns match the current record's values
  3. If a match is found, the "Match Action" determines what happens next
  4. If no match is found, a new row is inserted normally

Configuration:

  • Select one or more columns from the target database table
  • Multiple columns create a composite match key (combined using AND logic)
  • The columns you select should uniquely identify records or represent your duplicate detection criteria

Example:

To prevent duplicate invoices from being inserted, set "Match Columns" to InvoiceNumber. The system will check if an invoice with that number already exists before inserting.

For a more complex example, you might match on multiple columns like CustomerID and OrderDate to identify duplicate orders.

Match Action

The "Match Action" property determines what happens when a duplicate record is detected. This property is only visible when "Match Columns" are specified.

Available options:

Skip
No changes are made for matching records. Use this option to prevent overwriting existing data—only new records are inserted.
Example: When exporting invoices, skip any that already exist in the database to avoid duplicates.
Update
The existing row is updated with new values from the current record. Only columns in "Column Mappings" (excluding Match Columns and Return Values) are updated.
Example: When exporting customer information, update the address and phone number for existing customers while inserting new customers.
Error
An exception is raised and the export process is halted immediately. Use this option to enforce strict uniqueness and catch unexpected duplicates.
Example: When exporting employee records, raise an error if a duplicate SSN is detected to prevent data quality issues.
Examples

Preventing duplicate invoices:

  • Match Columns: InvoiceNumber
  • Match Action: Skip
  • Result: Only new invoices are inserted; existing invoices are ignored

Updating customer information:

  • Match Columns: CustomerID
  • Match Action: Update
  • Result: Updates address, phone, and other fields for existing customers; inserts new customers

Enforcing uniqueness:

  • Match Columns: SSN, DateOfBirth
  • Match Action: Error
  • Result: Stops export if a person with the same SSN and date of birth already exists


  • Note: When Match Columns are used, the "Rows Updated" statistic is tracked instead of "Rows Exported" to reflect the number of existing records processed.

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 Data Connection must utilize an MS SQL database.

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 Model with:

  • Connection: Set to your accounting database connection
  • Table Mappings:
    • Map invoice header fields from Grooper to an "Invoices" database table
    • Map line items Data Table from Grooper to an "InvoiceLineItems" databse 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
    • Most typically Export Behaviors are added to Content Models but can be added to Content Categories and Document Types for more Document Type-specific export scenarios in larger Content Models with a larger variety of Document Types.
  • 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's Node 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" collection in the Content Type's property grid.

Step 2: Add a Data Export definition

  1. Select the Content Type in the Node Tree.
  2. In the Property Grid, locate the Export Behavior section
  3. Open the "Export Definitions" editor (Click the ... button)
  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
  • Tip: If you haven't created a Data Connection yet, you'll need to add one to your Project first. Right click the Project or any of its subfolders, add a new Data Connection, and configure the server, database, and authentication settings.

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, open the "Table Mappings" editor (Click the ... button)
  2. In the Table Mappings Editor:
    • Click Add to create a new Table Mapping
    • Configure the following properties for each mapping:
      • Source Scope: Select the Data Model (or 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
  • Note: You can add multiple Table Mappings to export data to several tables. For example, export invoice headers to an "Invoices" table and line items to an "InvoiceLineItems" table.
Choosing the right Source Scope

The "Source Scope" property in a Table Mapping determines which Data Elements from your Data Model will be exported and how many rows will be created in the database. Selecting the correct scope is critical to achieving the desired export structure.

Available scope option:

Data Model
Exports one row per document to the database table. Choose this scope when exporting document-level information that appears once per document.
Examples: Invoice header data (invoice number, date, vendor, total amount), loan application details, purchase order summary information.
Data Section
Exports one row per section instance to the database table. Choose this scope when exporting repeating sections that may appear multiple times within a document.
Examples: Multiple addresses on a form, repeating claim details in an insurance document, multiple signatories on a contract.
Data Table
Exports one row per table row to the database table. Choose this scope when exporting line item data organized in a table structure.
Examples: Invoice line items, purchase order details, transaction histories, itemized charges.
Decision guide

Ask yourself these questions to determine the appropriate scope:

Question 1: How many times does this data appear in a document?

  • Once per document → Use Data Model
  • Multiple instances, but not in a table format → Use Data Section
  • Multiple rows in a table structure → Use Data Table

Question 2: What does each row in my database table represent?

  • A document → Use Data Model
  • A repeating section or entity within a document → Use Data Section
  • A line item or table row → Use Data Table
Common scenarios

Scenario 1: Exporting invoice headers and line items

You have an invoice with header information and multiple line items. You need two Table Mappings:

  1. Header Mapping
    • Source Scope: Data Model (the root invoice Data Model)
    • Target Table: "Invoices"
    • Result: One row per invoice document
  2. Line Items Mapping
    • Source Scope: LineItems (a Data Table in your Data Model)
    • Target Table: "InvoiceLineItems"
    • Result: Multiple rows per invoice, one for each line item

Scenario 2: Exporting loan applications with multiple borrowers

You have a loan application that can have one or more borrowers (co-applicants). You need two Table Mappings:

  1. Application Mapping
    • Source Scope: Data Model (the root loan application Data Model)
    • Target Table: "LoanApplications"
    • Result: One row per loan application document
  2. Borrower Mapping
    • Source Scope: Borrowers (a multi-instance Data Section in your Data Model)
    • Target Table: "Borrowers"
    • Result: One or more rows per application, one for each borrower section

Scenario 3: Exporting simple form data

You have a single-page form with customer information (name, address, phone, etc.) and no repeating elements. You need one Table Mapping:

  1. Customer Mapping
    • Source Scope: Data Model (the root customer Data Model)
    • Target Table: "Customers"
    • Result: One row per document
  • Tip: When exporting to multiple related tables, use Return Values to capture auto-generated IDs from parent tables and include them in child table mappings to maintain referential integrity.

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, open the "Custom Statements" editor (Click the ... button)
  2. In the Custom Statements 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.

  • Be aware: This feature is only supported for MS SQL Data Connections. The alternate database must have the same schema as the primary database.
  1. In the Data Export properties, locate the "Alternate Database" property
  2. Open the expression editor (Click the ... button)
  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
  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

Retrieved from "https://wiki.grooper.com/index.php?title=Data_Export_(Export_Definition)&oldid=32394"