Question:
With Active Import, it was possible to define field assignments even if the data type was not the same for the external source (database).
This means that a database field of the type Text could also be assigned to a Date field in DocuWare as long as the field content fits. Database types were cast automatically by Active Import.
How can this be achieved if AUTOINDEX is used?
Answer:
Because the source database may be filled by other applications, and you do not want to change the field types, you can use a database view to use the data with the same data types as the target fields in DocuWare.
Example:
External database with index information in the table "extdata.dbo.Indexdata" (in MSSQL):
| Column name | Data type | Content |
|---|---|---|
| ID | Nchar(10) | 1 |
| Barcode | Numeric(18,0) | 123456 |
| Documentnumber | Varchar(50) | 45612 |
| Name | Varchar(50) | Maier AG |
| Postalcode | Varchar(50) | 81785 |
| Documentdate | Varchar(50) | 15.09.2014 |
In DocuWare there are also the fields called documentnumber, postalcode and documentdate, but the fields are created for better usage with other data types:
| DocuWare Field | Data type |
|---|---|
| Documentnumber | Numeric |
| Postalcode | Numeric |
| Documentdate | Date |
Therefore, it is not possible to assign the external fields to the DocuWare fields in the AUTOINDEX workflow configuration. Because of this, an MSSQL database view is created which converts the contents of the DocuWare fields and makes an assignment possible.
Further details on the below-mentioned commands can be found here: CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn
First, a basic explanation:
The view should contain all fields of the external DB, so the basic statement looks like this:
SELECT
ID ,
barcode ,
documentnumber,
name ,
postalcode ,
documentdate
FROM
extdata.dbo.Indexdata
To convert a text field into a numeric field, the CAST command is used:
CAST (<field name> AS <desired data type>) AS <column name in the view>
For the field "documentnumber", the SELECT statement should be adapted as follows:
SELECT
ID ,
barcode ,
CAST (documentnumber AS int) AS documentnumber,
name ,
postalcode ,
documentdate
FROM
extdata.dbo.Indexdata
For date fields, CONVERT must be used instead of CAST:
CONVERT ([datetime], <field name>, 104) AS <column name in the view>
The complete statement to create the view:
CREATE VIEW AIX_VIEW
AS
SELECT
ID ,
Barcode ,
CAST (documentnumber AS int) AS documentnumber,
name ,
CAST (postalcode AS int) AS postalcode ,
convert ([datetime], documentdate, 104) AS documentdate
FROM
extdata.dbo.Indexdata
After this statement has been successfully executed, the DB "extdata" now contains a view called "AIX_VIEW", which automatically contains the contents of the source table "Indexdata" if queried, but which supplies the data types matching to the field in DocuWare.
Now a database connection to this database "extdata" can be set up in the DocuWare Administration to be able to use this view when configuring an AUTOINDEX workflow:
All external fields can now also be assigned to the corresponding DocuWare fields.