1.877.833.1202

Database Lookup on the Schema

The Lookup tab in the schema properties allows you to retrieve database information into additional metadata fields of a schema based on the value entered into the mapped metadata field. Once the user enters a value for the metadata field that is used as the “look up” value, a query can be executed on an external database to retrieve additional values that are mapped to the other metadata fields in the schema.

Up to five lookup parameters can be configured in a schema lookup with the custom query option enabled. This allows users to lookup multiple values from several metadata fields. For example, a customer is classifying construction documents for a property. Construction is related to a lease and can include one or more jobs. The customer would like the document to have the lease id, lease name, job id, and job name metadata information populated. When the lease id and the job id is selected from the drop-down list in the metadata pane and the Lookup button is pressed, then the system retrieves both the lease and job names. The custom query must be formatted properly in order to perform this type of multiple lookup.

Multiple database drop-down lookup fields

This mechanism does not require any synchronizing with the FileHold database because it is derived directly from the external database. The lookup is performed on the FileHold server in order to provide more security as the connection is made using the FH Service account.

The lookup can be performed when adding or editing the metadata of a single document. You cannot use the lookup when tagging multiple documents. See Entering Metadata Using Database Lookups for more information.

After performing a lookup, users can still modify any of the retrieved values. The system will not verify if the values match the original data record and it will not update those values when the record is modified in the external database.

When creating metadata fields for the schema that is using database lookups, any type of metadata field can be used (text, number, drop down list) as long as it matches the format of the lookup column. For example, if you have a number type metadata field mapped to a lookup column that uses letters then the lookup functionality will not work. In this case, using a text field for most metadata fields will work best as it accepts any letter or number combination.

Schema lookups can be configured using an Microsoft Access database, Microsoft Excel table, or text file. See Solution Design Resources for more information.

To configure database lookup for the schema

  1. In the Web Client, go to Administration Panel > Library Configuration > Document Schemas > Schema Name > 8. Lookup.
  • Alternatively, in FDA go to Administration > Manage Schemas > Schema Name > Lookup tab.
  1. To enable schema database lookup, select the Enable Schema Lookup check box.
  2. Select the database source:
  • To connect to a SQL database server, select FileHold Configured SQL Server Connection.

  • To use ODBC, OLE DB, SQL, or Oracle select User Configured Database Server Connection. In order to use this setting, you will need a license with this feature enabled. Upon request, a new license file can be sent to you at no charge. To request a license with this feature enabled, please email licensing@filehold.com.

TIP: It is possible to connect FileHold to a wide variety of data sources using ODBC or OLE DB connections, however, there is no way to guarantee any arbitrary data source is fully compatible with FileHold. Make sure you confirm technical compatibility before committing to a solution design using an ODBC or OLE DB. In some cases FileHold may be able to support an incompatible data source through a product change. Contact support@filehold.com for more information.

  1. If you selected FileHold Configured SQL Server Connection, do the following:
  • Enter the Server Name. The Server Name field contains the name of the machine hosting the database from which you are getting the values. The format of the server name is the name only and does not require forward slashes. If the database is hosted on the same server as FileHold, you can use (local) (include parentheses).

  • Enter the username for the SQL server.

  • Enter the password for the SQL server.

  • Select Use Integrated Authentication check box if applicable. If you chose not to use integrated authentication and decide to enter the user name manually, the format for the database username field is just “username” (no quotes) (not ''//domain/username'). The FileHold Service Account must be the db_owner of the database being looked up. This can be done in the database software management console looking at the database Security > Logins properties.

  1. If you selected User Configured Database Server Connection do the following;
  • Select the Data Provider from the list: .Net Framework for Data Provider for ODBC, .Net Framework Data Provider for OleDb, .Net Framework Data Provider for Oracle, .Net Framework for Data Provider for SqlServer.

  • Enter the Connection String to the external database.

  1. Click Verify Connection.
  • If the username and password is correct, you will get the message “Connection successful”.

  • If the username and password is incorrect, you will get the message “Login failed for user “<username>”.

  • If you receive a “Cannot connect to specified source” error, this indicates that there is a connection problem to the database. This means that the database name, server name, db admin user name or the db admin password is incorrect. It does not have to do with the select table, field caption or Field ID values. To troubleshoot this issue; confirm all database-related names are correct and ensure that the FileHold Service Account is at least a "data reader" of the MS SQL database being looked up. This can be done in Microsoft SQL Management Studio looking at the database Security > Logins properties.

  1. Select the Database to be used from the list.
  2. Select one of the following options:
  • Single Table – If you are using a single table or view to connect to, select this option then select the table or view name from the list.

  • Custom SQL Query – If you need to use a specific query to return the information from the database, select this option.  In order to use this setting, you will need a license with this feature enabled. Upon request, a new license file can be sent to you at no charge. To request a license with this feature enabled, please email licensing@filehold.com. Note that FileHold does not provide free support on writing custom database queries. For assistance on custom queries, contact sales@filehold.com.

  1. If Single Table was selected, do the following:
  • In the Select Table field, select the name of the table or view.

  • In the Lookup By field, select the database column to look up in the database. If you are using a drop down database managed metadata field, this value is set to the same value used as the Lookup By field defined in the drop down menu metadata field. This should be a column with unique values or the primary key.

  1. If Custom SQL Query was selected, do the following:
  • Enter the Query. The query must be able to return exactly zero or one rows. The query must include exactly one parameter. This will be the value replaced by the Lookup Using field at runtime. The following example illustrates selecting a single row with the parameter. The query uses internal FileHold data for simplicity. It takes the internal id value for a user and returns the username.

    SELECT [ObjectName] AS 'UserName' FROM [ch_librarymanager].[dbo].[AdamObjects] where ObjectId = @InternalUserId

    FileHold does not provide syntax coloring or any similar assistance with creating the custom query. It is either syntactically correct or it is not. We recommend you prepare your query using a tool that does provide these facilities. You can debug the final query using a tool like the SQL Server Profiler or similar tools for other databases. The exact format of the parameter may vary between data providers. Note that your FileCare agreement does not provide support for writing custom database queries. If you need assistance, contact sales@filehold.com for a professional services price quote.

  • Select the Parameter Type from the list. Your choices are integer or text. If the type is Integer the user will receive an error if they try to use a non-integer for the lookup. If the type is Text the parameter will be enclosed in quotes in the custom query. Regardless of metadata field type, the selected Lookup Using field will be converted to integer or text where possible. FileHold will not attempt to validate if the query is semantically correct only that it meets the basic syntax requirements.

  • Select the Parameter Type from the list. Your choices are integer or text. If the type is Integer the user will receive an error if they try to use a non-integer for the lookup. If the type is Text the parameter will be enclosed in quotes in the custom query. Regardless of metadata field type, the selected Lookup Using field will be converted to integer or text where possible. FileHold will not attempt to validate if the query is semantically correct only that it meets the basic syntax requirements.

  • Select the Parameter Name from the list. You do not need to specify the prefix character. This will be done automatically. For the SQL in the preceding example the parameter name to specify in this field would be InternalUserId.
  • In the Lookup Using column, select the metadata field name. that corresponds to the metadata field used in the schema for the lookup drop down list. If you are using a document control number that is manually generated, you can use that value as the lookup field as long as there is a corresponding match in the lookup database.

WARNING: If you change the Lookup Using field to use a different database column at a later time, this may result in data loss after documents using this field have already been added to the repository.

  • Enter up to 5 parameter types for the lookups. Note that the custom query must support the additional parameters. The following is an example custom query that could be formed with this feature. Assume the database is SQL server and three parameters, PARM1, PARM2, and PARM3 have been defined, the administrator would be able to form a query as follows:
SELECT * FROM table1 t1 JOIN table2 t2 on t1.fld1 = @PARM1 and t1.fld2 = @PARM2 and t2.fld3 = @PARM3.
  • Click Verify Query.
  1. In the Lookup Using field, select the metadata field that corresponds to the metadata field used in the schema for the lookup drop down list. If you are using a document control number that is manually generated, you can use that value as the lookup field as long as there is a corresponding match in the lookup database.
  1. Map the additional Source Column to the Destination Metadata Fields used in the schema.

TIP: It may be desirable to use database dropdown menu fields as destination fields in the lookup in order to automatically update values when they change in the source system. In this case the Source Column should be the same field set for the Lookup By value in the drop down menu configuration.

For example, you have a table with three columns: Id, NameFld, TitleFld. You create two metadata fields Name (from NameFld) and Title (from TitleFld) as database dropdown menus. In the lookup tab you set the Lookup By to Id, Lookup Using to Name. To set the Title field you use Id as the source column. The user will select a value from the drop down name field and press the lookup button. The title field will be retrieved using the id value. If the source data is updated the document information will automatically update the next time the automatic database synchronization runs.

It is not necessary for the fields to be from the same source table as long as the Source Column matching the Lookup By field for the drop down menu is available in the schema lookup. An example is available in the solution design section of the knowledge base.

Database lookup at the schema level

  1. From the Web Client, click Save.
  • From the FDA, click OK.