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.
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.
Only one database lookup is allowed per schema. 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
- 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.
- To enable schema database lookup, select the Enable Schema Lookup check box.
- 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 firstname.lastname@example.org.
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 email@example.com for more information.
- 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.
- 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.
- 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.
- Select the Database to be used from the list.
- 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 firstname.lastname@example.org. Note that FileHold does not provide free support on writing custom database queries. For assistance on custom queries, contact email@example.com.
- 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.
- 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 firstname.lastname@example.org 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 Name from the list and click Verify Query. 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 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.
- 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.
- From the Web Client, click Save.
- From the FDA, click OK.