Question:
With Active Import, it was possible to define field assignments even if the data type was not the same for 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):
In DocuWare there are also the fields called documentnumber, postalcode and documentdate, but the fields are created for better usage with other data types:
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.
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 follow:
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 DW.
Now a database connection to this DB "extdata" can be set up in the DW Administration to be able to use this view when configuring an AUTOINDEX workflow:
All external fields can now also be assigned to the corresponding DW fields.