Create a dropdown menu - Database managed

A metadata field that is a "Dropdown menu - database lookup" provides connectivity to external databases for retrieving dropdown items from a remote 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 dropdown menus are used in situations where values for the menu are stored and managed outside the metadata setup dialog. A typical scenario is an organization using a Customer Relationship Management (CRM) system where documents in FileHold should have a customer name from the CRM system assigned. The CRM is the source system for the list of customers. The customers can be selected from a dropdown menu that is dynamically populated from the central list of customers in the CRM system. There are three key requirements to make this possible:

• It must be possible for the FileHold server to access the data in the source system,
• Access to the source system must be possible using one of the five data providers above, 
• Read permission must be given to the source data.

The FileHold database can be used as a source system for a database dropdown menu, making it possible to use internal FileHold data in a database lookup dropdown list.

When configuring database dropdown 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 can be chosen from the list of columns available in the source data. The "lookup by" field can also be used as a search field in the event that it is different 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 the ability to view the "search by" fields, 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.

Image

Database managed dropdown menus is a topic that requires advanced knowledge. Library administrators should consult with Windows system administrators 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 are available to assist with setting up database dropdown metadata fields.

Database dropdown menus have their lists of values synchronized with the source data on a periodic basis, keeping a local copy in the FileHold database. This means the menu is available in FileHold regardless of whether the source data is available or not at the time a user tags a document with the value. This also opens the option to maintain these values after they no longer exist in the source system. For example, if a customer in the CRM is deleted because they cease to be a customer, it is possible documents for this legacy customer still exist in FileHold for compliance reasons. The dropdown menu can be configured to retain the information in FileHold as long as the documents continue to exist in FileHold.

The synchronization process is executed by a Windows scheduled task that runs hourly to populate the local FileHold copy of data from the source system. This means it may take up to 1-hour before a change in the source data is reflected in the dropdown menu. Generally, the frequency of this update should not be increased as there is a performance cost for synchronizing that can impact both FileHold and the source system. When necessary, an administrator can synchronize on demand from the metadata configuration dialog using Synchronize now.

Image

No connection between a user's workstation and the source database is necessary to use database dropdown menu fields. The connection to the source database is between the FileHold server and the source database. This makes it possible to use Integrated Windows Authentication (IWA) in many cases. When using IWA, the source database will be required to give read access to the FileHold service user for the tables/views used in the lookup.

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

To create a dropdown menu - 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.

2. In the Add Metadata Field pane, enter a Name for the metadata.
3.   Enter a Description for the metadata.
4. Select a Dropdown menu - database Lookup field type.
5. From the Web Client, click Manage Menu Items.

• From the FDA, click the Field Properties tab.

6. 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 or configuration change. See Understanding connecting to external data for more information.

7. 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 the parentheses.
8. Enter the database username and password. If you choose not to use integrated authentication and decide to enter the username 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 source database. If the source database system is SQL Server, this can be done on the database software management console looking at the database Security > Logins properties.
9. Click Verify Connection.

• If the authentication details are correct, the message “Connection successful” is displayed. Otherwise, the message “Login failed” is displayed.
• 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 connection string, server name, username, password, or permissions on the source database are incorrect. Make sure your connection is not blocked by a firewall. Ensure the authentication details you are providing in the dialog has matching permissions in the source database.

10. Select the database to be used from the list.
11. 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 with creating a custom query. Your FileCare subscription does not include support for creating or diagnosing issues connecting to the source database. 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.

12. Select the table to be used in the list if you are using the Single Table method.
13. 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 will be displayed to 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 exhibit strange behavior or data loss. 

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 dropdown menu value (retrieve from) or key (lookup by) is 100 characters. Create a view or use a custom query to truncate the column if necessary. Your configuration and queries should ensure the key remains unique within the 100-character limit. The system includes an option to monitor the maximum length of the key and log a message if the length is exceeded, causing a duplicate. This feature is turned off by default for performance reasons, but you can enable it using managed options by setting the value of DbLookup.IgnoreDuplicateKeyValidation to true.

14. 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 dropdown 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 is selectable from the list of columns in the view or table. Up to four Search by fields can be selected.
15. 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 the "lookup by" differs from the “retrieve from” field.
16. 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 "retrieve from" 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. The first duplicate record is always 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.

17. 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 many hours for a million records to complete. Performance is impacted since the system needs to alphabetically order the values in the dropdown menu.

18. 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.

Except for special circumstances, you should never use the option to automatically delete values without consideration for whether the values are attached to documents.

• 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. If you assign a dropdown menu value to a document, then later change the value, it still exists in the metadata versions and is therefore associated with the document. When the document is eventually deleted, the value is removed from the menu.
• Do not change values – Do 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 checkbox. Synchronize the data with Synchronize now. Then modify the settings and enable "Do not change values" again. If you already have documents in the library using this database managed dropdown 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.

• Deactivate values - Make all deleted values inactive when deleted at source. Non-administrative users will not be able to add these inactive values to documents.
• Deactivate values or delete when there are no associated documents - Make all deleted values inactive when documents exist with these values assigned, otherwise delete the values. 

Deactivate values or delete when there are no associated documents is often the best choice for a dropdown menu.

19. If using the Web Client, click Preview to view a preview of the metadata field.

• If using the FDA, select the Preview tab.

20. If multiple selections of values are allowed, select the Allow Multiple Selections check box.
21. Choose the Suggest mode. The Starts with option allows the user to type the first few characters of a value to narrow the list of items available to select from. The Contains option allows the user to type any characters from the item to narrow the list.
22. Enter an initial value, if applicable.
23. Click Save from the Web client.

• Click OK from the FDA. 

Image

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. 

When the list retrieved from the database is presented to the user in the dropdown menu, only the first 100 items are displayed. As the user scrolls through the list or starts typing the value, more values will be loaded into the view but, it is limited to viewing 100 values at a time. You can change the number of items retrieved at one time by modifying the managed option, Dropdown.ItemCountPerRequest.

If you are creating a dropdown 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 menu values that remain fairly static. See Solution Design Resources for more information.