030-030 MetaServer Export – Export to Database

Don’t forget to add a fitting description of your action.

01 – Type: the default database type is ODBC. You can also directly export to a SQL Server database.

1) Data source: select the data source you want to use. An ODBC data source needs to be defined first using the ODBC Data Source Administrator tool in Windows. For a step by step instructions on how to define an ODBC Data Source in Windows, have a look at this guide.

Select data source from field: you can use this option to switch databases dynamically using a field value.

To access this setup window, press the “…” button next to Data Source. You can select the field containing your database name by pressing the drop-down arrow.

Note: Selecting a data source from a field is an advanced function often used by BPO (Business Process Outsourcing) companies to dynamically switch database in the same workflow depending on the customer they are processing documents for.

2) User name: some databases require to login. If so, enter the User name in this field.

3) Password: some databases require to login. If so, enter the Password in this field.

4) Timeout: when the database does not respond in the specified time, the action will fail. A time-out is often an indication that the database server is down. If any issues occur, please contact your database administrator.

1) Server: enter the name of your SQL Server.

2) Database: specify the name of the SQL Server database you want to connect with.

3) User name: enter the user name in this field (if applicable). Please make sure that user has the proper access rights to the SQL Server database. If you’re not sure, please contact your database administrator.

4) Password: enter the user’s password in this field (if applicable). Please make sure that user has the proper access rights to the SQL Server database. If you’re not sure, please contact your database administrator.

3) Extra: directly connect through a SQL Connection string. If you’re not sure how to connect to your SQL Server database, please contact your database administrator.

02 – Table: a database typically stores data in one or more tables, such as a document types table, a suppliers table, a products table, etc. Specify the table that you want to update.

03 – Lookup field: press the drop-down arrow to choose between 2 options:

1) You can update existing records by selecting a specific database lookup field and MetaServer field holding the lookup value

2) If you don’t want to update existing records and only want to keep adding new records, you can choose to do no lookup, always add records.

TIP: If you want to update existing records and add new records, define a Lookup field. MetaServer will automatically add a new record if the Lookup does not match any existing records.

Press the Field Mapping tab to open its setup window. Here, you can map your database fields.

01 – Database field: this column shows all existing database fields in the currently connected database table.

02 – Type: this column shows what the database field type is. Strings can almost contain any value.  But DateTime, Integer and Decimal fields can only be updated with very specific data. For example, trying to update a Decimal field with a value containing letters will return an error.

03 – Value: in the value column, you can enter  fixed values or use the drop-down arrow to compose the value using MetaServer fields and metadata for each database field.

04 – Date format: the “Date format” is relevant when you map a DateTime field type.

DateTime type fields will only accept valid dates and times in a consistent format. It’s very important to enter the correct input DateTime format as it was extracted by MetaServer. This way, the Export to Database action can automatically convert the DateTime values and correctly update the database.

For example:
You want to export the extracted Inspection Date that has a “MM/DD/YYYY” format to a Date type field in a Microsoft SQL database.
You specify the following input format in the Date format input field:
{ Date, MM }/{ Date, DD }/{ Date, YYYY }

Result: knowing the input format, the database export action will automatically convert a date like 07/04/2018 to a database compliant DateTime format like: “2018-07-04 00:00:00”.

05 – Upload image: you can upload your processed PDF to your database if your table includes a BLOB field (Binary Large Object). This is also known as a “VARBINARY(MAX)” or “IMAGE” field in MsSQL.

If you want to upload your processed PDF, press the dropdown button to select an available BLOB field from your table.

06 – How to update a field with the location of the PDF File: if you want to update a field with the path to the exported PDF file, use an Export to Folder action followed by an Export to Database action.

Map the export path with any of your fields in the DB. Then, use the { Export File Full Path and Name } variable as the value to update your field with the path to the exported PDF file.

You can have multiple Export to Folder actions in your workflow that each will update the export paths, and every one of them can be followed by their own Export to Database action.

TIP: Microsoft Access features a Hyperlink field type.  We use it for the “Exported File Link” in our sample MS-Access Statistics table. With this type of field, you can generate hyperlinks that directly open the exported PDF file.

The value you need to pass to the field has a simple syntax: LINK_NAME#PATH

In our case, we pass it the following value: Open PDF#{ Export File Full Path and Name }

TIP: you can copy the current settings and paste them in another setup window of the same type. Do this by pressing the Settings button in the bottom left of the Setup window and by selecting Copy. Then open another setup window of the same type and select Paste.

After export in our CB – STATISTICS workflow, the Microsoft Access database located in C:\CaptureBites\DB\CaptureBitesDatabase.mdb has been updated with new records:

Before export

After export