Create a drop down menu - Database managed

A metadata field that is a drop down menu - database managed provides connectivity to external databases which allows retrieving drop down items from a table or a view. The following data providers are supported:

  • Microsoft SQL
  • .Net framework data provider for ODBC
  • .Net framework data provider for OLE DB
  • .Net framework data provider for Oracle
  • .Net framework data provider for SQL Server

Database managed drop down menus are used in situations where values for the menu are stored and managed outside of the metadata setup dialog. A typical scenario is a customer using Microsoft CRM and needing their users to associate a client with documents that are filed into the document management system. The list of clients can be displayed as a drop-down menu that is dynamically populated from a central list of customers taken from an existing CRM system. If the names of the clients are already stored in a Microsoft SQL server this lookup could be done directly, if not, the CRM system could export (on a scheduled basis) the list values from another SQL server. This allows them to link or tag documents with clients by using a controlled list of clients that is always current. It is possible to use internal FileHold data in a database lookup drop down list.

When configuring database drop-down lists, up to four “search by” fields can be set to help users choose the correct value from the list. Often the single lookup value is not sufficiently descriptive so selecting the correct value from the list can be difficult due to having similar names, long names or numbering. “Search by” fields help simplify the selection process. The list of “search by” fields are selectable from the list of columns in the view or table. There is an option to allow to search by the “lookup by” field name in the event that this differs from the “retrieve from” field.

Once the “search by” fields are configured, the end user has the ability to click the “search” button in the metadata or advanced search pane. In the search by window, the user has ability to view the search by information, and filter and sort the data in order to select the correct value from the list. For example, when putting employee information into the system, the lookup is done on the unique employee number field. However, remembering the employee number for each employee is not practical. Using this solution, the user can see the employee’s first and last name, title, department and so on to ensure the correct employee number is selected.

Search by metadata - Metadata pane FDA

Database managed drop down menus is a topic that requires advanced knowledge. Library administrators should consult with Windows system administrators and or database administrators when they are setting up these fields. The integration of FileHold with external systems is not included in FileCare. FileHold professional services is available to assist with this sort of integration work when needed.

FileHold does not directly retrieve data from the external database for a variety of reasons, but primarily the design wishes to avoid a situation where users would be unable to add a document or do other common functions using that metadata field if the external database was not available at the time of the request.

Instead, the document management server application has a scheduled task that runs hourly, and can also be run manually on demand, that populates all database managed drop down menus. We do not recommend that this task be run at intervals less than the 1 hour default. When you make changes to your external database you need to wait for the hourly scheduled task to run or go to the FileHold server and run the scheduled task manually. The name of this task is FileHold database lookup fields synchronization.

When there are duplicates values in Lookup by field, the values are being duplicated during each scheduled task synchronization process in the FileHold synchronization table causing it to grow extremely large. In order to ignore the duplicate key validation, the key “DbLookupIgnoreDuplicateKeyValidation” in the library manager web config file can be enabled. By default, this is set to false. When enabled, values are merged into a single entry, values in other fields of this row (such as field caption) are assigned randomly from the group with the same Lookup by. A message is added to the Event log, so the lookup field with the issue can be identified.

Once the scheduled task completes, you then see the information updated in the drop-down menu field as it is used in the product. Refresh the desktop client if using the FileHold Desktop Client or ensure that you have your browser cache disabled so that the Web Client is showing you current information. The exact behavior in the Web Client is dependent on the browser used.

Database update scheduled task

The lookup is performed on the FileHold server in order to provide more security as the connection can be made using integrated authentication using the FH Service account, not the account of the user running the FileHold client application. Since the lookup is made on the server, the database server can be behind a firewall or if one of the additional data providers is used, it only needs to be installed on the server.

The document management system uses an intermediary Microsoft SQL database located within the FileHold Database(s), and a table is created for each individual drop down - lookup metadata field.

Adding, deleting, or changing drop-down list values are logged in the Library Audit Log.

To create a drop-down database lookup metadata field

  1. In the Web Client, go to Administration Panel > Library Configuration > Metadata Fields.
  • Alternatively, in the FDA, go to Administration > Manage Schemas > Metadata Fields.

  1. In the Add Metadata Field pane, enter a Name for the metadata.

  2. Enter a Description for the metadata.

  3. Select a Drop Down Menu - Database Lookup field type.

  4. From the Web Client, click Manage Menu Items.
  • From the FDA, click the Field Properties tab.

  1. Select the database source:

  • To connect to a SQL database server, select SQL Server.
  • To use ODBC, OLE DB, or Oracle select Custom Data Providers. This option must be enabled in your license. To request a license with this option enabled, please email [email protected].

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 connection. In some cases FileHold may be able to support an incompatible data source through a product change. See Understanding connecting to external data for more information.

  1. 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).

  2. Enter the database username and password. If you choose 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'). If you are using integrated authentication the FileHold Service Account must have permission to the database being looked up. If the source database system is SQL Server, this can be done in the database software management console looking at the database Security > Logins properties.

  3. Click Verify Connection.
  • If the username and password is correct, the message “Connection successful” displays.
  • If the username and password is incorrect, the message “Login failed for user “<username>” displays.
  • 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 selected 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 has permissions. For SQL Server this will mean at least a "data reader" of the 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 the simple single table approach does not provide the flexibility you need to return the information from the database, select this option.

This option must be enabled in your license. The FileHold professional services team is available to assist you to create a custom query. Your FileCare subscription does not include support for creating or diagnosing issues with a custom query. A simple way to test a query is to use your database management query interface or similar tool. When the query is working correctly for you there, you can copy and paste it to the metadata field configuration. It can be easier to maintain your query by incorporating it into a view or stored procedure or similar concept in your source database system.

  1. Select the table to be used in the list.

  2. Select the Lookup By and Retrieve From fields from the lists. In many database designs there is a key value that uniquely identifies the record and a value that is displayed for the user. Lookup By is the column with that unique value and Retrieve From is the column with the value to display. It is important that Lookup By is unique otherwise the field could have strange behavior. 

If you change the Lookup By 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.

The maximum length of a Lookup By or Retrieve From value is 100 characters. You need to ensure that your source data does not exceed this size for the Lookup By or there may be unexpected results. The problem is most likely to be present when your Lookup By and Retrieve From are the same column. Create a view or use a custom query to truncate the column if necessary.

  1. In the Search by fields, select the columns to display in the view when the user clicks the Search button in the metadata or advanced search pane. Often a single lookup value is not sufficiently descriptive so selecting the correct value from the drop-down list can be difficult due to having similar names, long names or numbering. Search by fields help simplify the selection process by displaying additional information from the metadata pane. The list of “search by” fields are selectable from the list of columns in the view or table. Up to four Search by fields can be selected.
  2. Select the Search by the lookup by field check box to display the values from the lookup by column. This option is available in the event that this differs from the “retrieve from” field.

  3. Select the Remove Duplicates check box to remove any duplicate field caption values from the dropdown list.

This option should NOT be used most of the time. If there are duplicates in the field caption they should be corrected in the database. For example, if the list is of employees and remove duplicates is enabled and there are two employees called "John Smith". One of the John Smiths can never be selected. Simply leaving this box unchecked is not a complete solution as you should also ensure there is some difference in every employee's name. Remove duplicates is performed in memory, so may be expensive in RAM or processing resources for very large lists. Unlike a database query, trailing spaces and letter case are taken into account so "John Smith" and "John smith" are not the same. Always the first duplicate record is removed, however, for the single table case there is no way to guarantee which record is first. When a custom query is used, it can be designed so the first record is known.

  1. Select the Alpha Order check box to have the list alphabetically sorted. The sort is done according to the collating rules in the database. This option is not available for the custom query case, so the custom query needs to provide the correct sort order.

When Alpha Order is enabled, the first synchronization may take multiple hours for a million records to complete. The performance is impacted since the system needs to alphabetically order the values in the drop-down list.

  1. Select an option for when a value is deleted at source:

  • Delete values – If the value is deleted from the source database, the value is also removed from the metadata field in FileHold. For a deletion, this means the value is removed from any documents that may have been associated with the value. FileHold considers a deletion a permanent change and the original value no longer exists anywhere in FileHold.

  • Delete values unless there are associated documents – If there are documents that contain the values that have been deleted from the source database, the values remain untouched in FileHold. NOTE: If you assign a value then remove the value, it still exists in the metadata versions and is not deleted. When the document(s) are eventually deleted, the value is removed.

  • Do not change values – Does not change the metadata field values in FileHold when deleted from the source database. Upon upgrade, if the Prevent Deletion option was enabled, this option is selected.

If the Do not change values check box is enabled and changes are needed in the settings, such as the Lookup By field, you should first disable the check box. Synchronize the data by clicking Apply. Then modify the settings and enable Do not change values again. If you already have documents in the library using this database managed drop-down field with Do not change values enabled AND changes are needed to the lookup settings, it is recommended that you create a new metadata field with new lookup settings. Then add the new metadata field to the schema, move the old metadata field values into the new metadata field values. Once the values are moved over, the old metadata field can be removed from the schema.

  1. If using the Web Client, click Preview to view a preview of the metadata field.
  • If using the FDA, select the Preview tab.
  1. If multiple selections of values are allowed, select the Allow Multiple Selections check box.
  2. Enter an initial value, if applicable.

  3. Click Save from the Web client.

  • Click OK from the FDA. 
Database drop down field properties

If you reuse a database lookup field in multiple document schemas then the system allows users to search across all document schemas that are using this lookup metadata field. However, as always, users are able to search for and find files that they do not have permissions to access. Access is based on Cabinet, Folder and Document type memberships.

Special considerations

FileHold assumes that the data extracted from the external system is valid for use in FileHold. Ensure that the contents of the field are compatible with XML and do not contain XML control characters as this could disrupt the synchronization process or interfere with rendering the web client screens. If the field is using the multi-select option the field contents should not contain commas or semi-colons.

When the list retrieved from the database is presented to the user in the drop down list, only the first 100 items are displayed in the FDA and the first 25 items in the Web Client. As the user scrolls through the list or starting typing the value, more values will be loaded into the view but is limited to viewing 100 (FDA) or 25 (Web Client) values at a time. To change the number of items displayed in the Web Client drop down list, the parameter <add key="ItemCountPerRequest" value="n"/> can be added to the web.config file in C:\Program Files\FileHold Systems\Application Server\WebClient\ (Windows Server 2008) where n is the number of drop down list items.

If you are creating a drop down list to be used with a database lookup at the schema level, see Database Lookup for more information.

Instead of doing a look-up from a database, you can also use Microsoft Access, Excel, or a text file to import list items. See Solution Design Resources for more information.

 

Related Knowledge Base Article(s):
Knowledge Base
Free Webinar Free Trial Videos Features