Document Management Software Help : Docsvault

Fetching Index Values From External Database

Fetching Index Values From External Database

Previous topic Next topic No directory for this topic  

Fetching Index Values From External Database

Previous topic Next topic Topic directory requires JavaScript  

To find your documents effectively and handle various document life cycle events in a document management system, you often need to index/profile them. The Docsvault Advanced Profiles add-on allows you to fetch value lists for filling up profiles from external databases like your CRM, ERP, Accounting and other LOB applications. The Advanced Profiles Add-on allows two types of index fetching methods:

 

Primary/External Index: For Indexes defined as Primary/External Indexes their list of values is fetched from external SQL based databases. Example: list of customers, vendors, etc.  

 
Secondary/Dependent Index: For Indexes defined as Secondary/Dependent Indexes their index values are filled/filtered up automatically as per rules based on a value selected in a previous index field. Example filling up of customer information automatically based on the customer name or ID selected in previous index field or filter out the list of vendors by region or type of products selected in a previous index field.

 

You define the auto filling rules for primary and secondary indexes when creating profiles in the Docsvault Server Manager dialog. Once set properly, when profiling a document in Docsvault a user can select a value from the list that displays values dynamically retrieved from an external database and can also auto populate specified secondary/dependent index fields.

 

note Note:

This feature is only available as an add-on module and requires separate purchase and activation.

 

 

Defining Auto Fill Rules

 

Connecting External Database involves following 3 steps:

 

1.Installing ODBC drivers
You will first need to install proper ODBC drivers for the database that you are trying to link with. Almost all SQL based databases offer ODBC drivers to interact with them.

 

2.Creating a System DSN for the external ODBC data source
A DSN (Data Source Name) contains all information that is needed for opening a data source like Server name or IP address, user name, password and special data source parameters, etc. You can create System DSN via ODBC Data Source Administrator (Data Sources (ODBC)) dialog found under Start > Control Panel > Administrative Tools in your OS. Since the setting of DSN are specific to your SQL data source, you should refer to your SQL data source documentation for further information on this.

 

 

3.Connecting with external data and setting Auto Filing Rules

  Follow the steps below to create a relation with the external data source in Docsvault

 

oGo to Docsvault Server Manager > Profiles/Indexes node

oFrom the Profile tab, create a new 'Profile' or open an existing 'Profile'

oAssign a new 'Profile Name' and a short 'Description' about it

oSelect each index from 'Available Indexes' list and click on Add button. The selected index will get added to the 'Assigned Indexes' section.  

 

advProfile-1

 

oFrom 'Assigned Indexes' section, select the index and click on 'Auto Fill Rules' list box

Primary/External Index: Select this option to set the index field as external primary index field

Secondary/Dependent Index: Select this option to set the index field as dependent secondary index field

 

 

Setting Up Primary Indexes

 

oSelect the index field and click Primary/External Index from 'Auto Fill Rules list box.

advprofile-2

Setting up Primary Index Query

 

 

oSelect Data Source: Click on Add button to create a new DNS connection if necessary

 

dnsconnection

 

oQuery: Enter a query in the format shown in the example below.

 

Examples:

The following example displays data structure of table Employee

 

Table Employee

 

EMP_No

Emp_Name

Position

Join_Date

Dept

Salary

7369

Keith Smith

Sales Executive

12/17/2011

Marketing

$75296

7370

Bob Allen

Clerk

02/01/2011

Admin

$41473

7371

Alicia Martin

Manager

03/02/2010

Marketing

$108132

7372

Joe Blake

Sales Executive

04/04/2011

Marketing

$70482

 

SQL Query Syntax:

This is syntax for MS SQL database to select primary index field.
 

SELECT [FIELDNAME], [LOOKUPFIELD] FROM OPENQUERY ([DNS],

"SELECT [FIELDNAME], [LOOKUPFIELD]

FROM [SERVERNAME].[DATABASENAME].DBO.[TABLENAME]

WHERE [FIELDNAME]="VALUE"")

 

FIELDNAME: Specify the column to retrieve by the SELECT statement

LOOKUPFIELD: Specify the lookup column name for easy identification. This is optional.

DNS: Specify DNS connection. e.g DIPDNS

SERVERNAME: Specify the SQL server/instance name e.g k2Yserver or PCSQL/SQLExpress

DATABASENAME: Specify the database name e.g k2Ydatabse

TABLENAME: Specify the table name from where to retrieve the data e.g employee

FIELDNAME="VALUE": Specify a filter condition to return only the desired rows. The WHERE clause is optional.

 

note Important Notes:

Data Types: When you load data from the external table, the data types of the external table may not match the index's data type in Docsvault. In such cases you must perform conversions by using SQL operators in the SQL statement that is selecting from the external table.
Any conversion errors that occur between the index data type in Docsvault and the external table cause the row with the error to be ignored. To avoid problems with conversion errors causing the operation to fail, the data type of the column in the external table should match the data type of the index in Docsvault.

Date Format: Following syntax should to used to convert date time data type field to ISO8601:
             CONVERT(nvarchar, [datetime], 126) as alias

 

 

To query the k2Ydatabse database for the list of employees from the employee table enter this statement in the Query text box and then press Test Query button. Results will be displayed in the pop up window along with the look up column.

 

SELECT [emp_no],[emp_name]

FROM OPENQUERY([DipDNS],"SELECT [emp_no],[emp_name]

FROM [k2Ydatabse].DBO.[employee]")

 

oClick on Test Connection. On successful connection, query will be executed and a pop up window will display the results of the query for preview.
This indicates that the relation has been successfully established between Docsvault and the external database. Click on OK to verify the query syntax.

oIndex Mapping:  This section allows you to map external database field with Docsvault index field. For e.g In the above example, external [emp_no]
filed is mapped with Docsvault index field "Employee No". The external field [emp_name] will act as a lookup field.

oClick Save to save the configuration.

 

note Important Notes:

A profile can have multiple primary external indexes.  

Please follow the proper SQL query syntax as shown in examples above.

oOnly "Select" type queries can be used

oQueries return results along with lookup column

oQueries are executed in real time from the Docsvault Server side and bring up most current values even when index values are
listed on the Docsvault Client side.

 

 
 

 

Specifying Primary/External Indexes at the Index level instead of the Profile level

 

   If you wish to use the same index as a primary field in multiple profiles you can setup its external link in the 'Edit Index' dialog instead of this Profile dialog.

 

oGo to Docsvault Server Manager > Profiles/Indexes node

oFrom the Indexes tab click on New to bring up Create Indexes dialog box as shown below

oEnter a Index Name and a short Description for it

oUnder Index Type, select From External Source radio button

oSelect Data Source: All System DSNs created on your machine are listed in this drop down menu. You can now choose the DSN to use for this connection.

oQuery: Enter a query to select the index column as in the example below:

    Examples:

The following example displays list of employee name in the Employee Name index

 

SELECT[emp_name] FROM OPENQUERY ([DipDNS],"SELECT[emp_name] FROM [k2Ydatabse].DBO.[employee]")

 

external-sourceindex

Index values from External Database

 

 

 

Setting Up Secondary Indexes

 

oSelect the index field and click Secondary/Dependent Index from 'Auto Fill Rules list box.

oSelect Data Source: All System DSNs created on your machine are listed in this drop down menu. You can now choose the DSN to use for this connection.

 

advprofile-3

Setting up Secondary Index Query

 

oQuery: Enter a query to select the secondary columns as in the example below:

SQL Query Syntax Example:

To query the k2Ydatabse database for the list of dependent fields of employees from the employee table, enter this statement in the Query text box and then press Test Query button.

SELECT[emp_name],[position],[dept], convert(nvarchar[join_date],126) as join_date, [salary] FROM OPENQUERY ([DipDNS],"SELECT[emp_name],[position],[dept], [join_date], [salary] FROM [k2Ydatabse].DBO.[employee] WHERE [emp_no]='{Employee No}'")

 
In the pop up window, you can test the result by entering the value for the primary field. On successful connection, query will be executed and result will be displayed in the Query Result section.

oIndex Mapping:  This section allows you to map external database field with Docsvault index field as shown in the above screen shot.

oReal Time Lookup: Enable 'Use Dependent Indexes for Real Time Lookup...' option to fetch index values for secondary or dependent indexes in real time when a document's profile is displayed.

oClick Save to save the configuration.