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 will allow 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.
IMPORTANT: 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.
Once the scheduled task completes, you will 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.
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.
FileHold 12 provides connectivity to external Microsoft SQL 2005, 2008, and 2012 database(s).
FileHold 14 and greater expand on this capability with an optional feature that can be added to your license allowing the administrator to define custom queries and select custom data providers including the following:
- .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
Creating a Database Managed Drop Down Menu
- In the Web Client, go to Administration Panel > Library Configuration > Metadata Fields.
- Alternatively, in the FDA, go to Administration > Manage Schemas > Metadata Fields.
In the Add Metadata Field pane, enter a Name for the metadata.
Enter a Description for the metadata.
Select a Drop Down Menu - Database Lookup field type.
- From the Web Client, click Manage Menu Items.
- From the FDA, click the Field Properties tab.
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 is only available for FileHold 14 or greater and must be enabled in your license. To request a license with this option 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.
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 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'). In you are using integrated authentication 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.
- 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. Note that FileHold does not provide support on writing custom database queries. This option is only available for FileHold 14 or greater and must be enabled in your license.
Select the table to be used in the list.
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.
In FileHold 12 Lookup By was called Field Id and Retrieve From was called Field Caption. Select the field caption and field ID from the list.
- Select the Allow Multiple Selections check box if multiple metadata values are allowed.
TIP: To select multiple values from a drop-down list, select the check box next to the value name.
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. FileHold does not guarantee which records will be removed in the de-duplication process.
Select the Alpha Order check box to have the list alphabetically sorted.
- Select the Prevent Deletion check box so that existing values in metadata fields are never removed even when deleted from the source database. If disabled, any values in the source database that have been deleted will also be deleted inside of FileHold when synchronization occurs. For a deletion, this means the value will be immediately removed from any documents that may have been associated with the value. FileHold considers a deletion a permanent change in the document schema. The original value will no longer exist anywhere in FileHold.
IMPORTANT: If the Prevent Deletion check box is enabled and changes are needed in the settings, such as the Lookup By field, you should first disable the Prevent Deletion check box. Synchronize the data by clicking Apply. Then modify the settings and enable Prevent Deletion again. If you already have documents in the library using this database managed drop-down field with Prevent Deletion 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.
- If using the Web Client, click Preview to view a preview of the metadata field.
- If using the FDA, select the Preview tab.
Click Save from the Web client.
- Click OK from the FDA.
NOTE: If you reuse a database lookup field in multiple document schemas then the system will allow users to search across all document schemas that are using this lookup metadata field. However, as always, users will not be 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 for All Versions
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.
Special Considerations for FileHold 14 and above
When the list retrieved from the database is presented to the user in the drop down list, only the first 100 items will be 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.
Special Considerations for FileHold 12
If you are planning to use a large SQL driven drop down menu of over three thousand (3000) plus items, the database lookup should be examined carefully to see that you get a smaller result set. FileHold has performed extensive testing and warns that you may experience performance issues when using larger drop down menus.
The reason for this is that database lookups are loaded into each user's PC during their session, and is updated constantly throughout the session. A very large database lookup can cause performance issues on the PC and on the document management server when consumed thousands of times per hour by a group of active software users.
It is recommended that you redesign your view or table / database lookup so that it focused the lookup on relevant or active data.
As a safe guideline to prevent new customers from looking up gigantic databases and causing severe performance issues, FileHold limits Microsoft-SQL Database driven drop-down menus to 1000 items via web.config in LibraryManager. This is done so that the creation of a SQL database lookup is done carefully, and that this information is very clear.
The FileHold Server administrator can adjust the drop down menu beyond this initial limit of 1000 items using Notepad or other text editor in the LibraryManager folder on the web server. Within this folder is a web.config file.
You should first make a copy of this file before making any changes - this web.config file is found in LibraryManager folder within the Application Server folder that runs the FileHold Server application.
Look for the <add key="MaxDropDownSourceItems" value="1000"/>
If needed, you can adjust this value to 2000 or 3000 items. You will have to restart the WWW service on the FileHold Server. All user sessions will be disconnected when you restart the WWW service on the FileHold Server.