Creating Drop-down Metadata Fields

When creating metadata, you can populate the information using a drop-down menu. Using a drop-down menu not only cuts down on simple mistakes like spelling errors but it also forces the user to stick to a controlled vocabulary when describing a document in the document management system.

Three Types of Drop Down Menus that can be Created:

1. Drop down menu - FileHold managed

2. Drill down, drop down menu

3. Drop down menu - Microsoft SQL Database lookup

• Important Microsoft SQL Performance Settings

The first two types are created by the Library Administrator while the last is created leveraging an external Microsoft SQL database so that specific information can be used in a drop-down menu within document management system.

Drop Down Menu - FileHold Managed

These menus are created by the Library Administrator so that users can select from a list of choices rather than typing.

To create a FileHold managed drop-down menu

1. In Library Administrator, go to 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 Drop Down Menu - FileHold Managed.

5. Click Manage Menu Items. You can add, delete, and organize the order of the hierarchy by moving the items up and down.

6. If multiple selections are allowed, select the check box.

7. If there are duplicates in the list and would like them removed, select the check box.

8. Enter an initial value, if applicable.

 

Drill Down, Drop-Down Menu 

The Drill Down Drop-Down Menus are created by the Library Administrator.  When a user makes a selection in the first list, a second, related list will appear. For example, there could be a drop-down menu that contains a list of the organization's positions and when a position is picked, a list of all people that hold that position displays.

These are created almost exactly the same way as the regular drop down menus.

To create drill down drop-down menus

1. In Library Administrator, go to 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 Drill Drop Down Menu.

5. Click Manage Menu Items.

6. Type in the first entry for the drop-down menu and click Add.

7. Continue to add menu items until all selections are added.

8. Select one of the existing drop-down menu items and to create the sub menu items.

9. Type in the first sub entry and click Add. The item will appear under the selected menu item and the selected menu item will display a plus sign next to it.

10. Continue to add sub menu items to all the menu items until completed. 

11. Click Save.

 

 Drop Down Menus - Microsoft SQL Database Lookups

FileHold provides connectiivty to external Microsoft SQL 2005 and 2008 Database(s) within metadata drop-down menus for database lookup / dynamic drop-down menus. The document management system uses an intermediary Microsoft SQL database located within the FileHold Database(s), and a table is created for each individual Microsoft SQL FileHold Database lookup metadata field.

FileHold does not connect directly to the external SQL 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 Microsoft SQL Lookups. We do not recommend that this task be run at intervals less than the 1 hour default.

Please note that when you make a change to your Microsoft SQL Database information, you need to either wait for the hourly scheduled task (FileHold database lookup fields synchronization) or go to the FileHold server and run the scheduled task manually.

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. Internet Explorer's automatic browser cache is especially problematic.

Sample Database Lookup Use Case Study

A customer is using Microsoft CRM and needs their users to associate a client with documents that are filed into the document management system. The list can be displayed as a drop-down menu (pick list) 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.

To create a drop-down database lookup menu

1. In Library Administrator, go to 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 Drop Down Menu - Database Lookup.

5. Click Manage Field Source. 

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

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

8. Select the database to be used from the list.

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

10. Select the field caption and field ID from the list. In many databases, lookup tables use both a field caption and a field ID to identify a piece of data. The field caption value is the column of the table that contains menu items for users to select from while the field ID value contains the menu item IDs. If there is only one column in the table it must contain a field caption value.

11. If multiple selections are allowed, select the check box.

12. Select the Remove Duplicates check box to remove any duplicate values from the metadata list.

13. Select the Alpha Order check box to have the list alphabetically sorted.

14. Click Preview to view a preview of the metadata field.

15. Click Save.

16. Select the Allow Multiple Selections check box if multiple metadata values are allowed.

17. Enter an initial value, if applicable.

 

 

 

 

 

 

 

 

 

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.

Field Caption and Field ID

In many databases, lookup tables use both a field caption and field ID to identify a piece of data. The 'Field Caption' value is the column of the table that contains menu items for users to select from while the 'Field ID' value contains the menu item IDs. If there is only one column in the table it must contain a 'Field Caption' value.

Format of the 'Server Name' Field Value

The server name field will contain the name of the machine hosting the database you are looking up from. The format for the server name is the name only and it does not require forward slashes. If the database is hosted on the same server as FileHold you can also use '(local)', please note the parentheses around 'local'.

Format of the Database Username Field

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' and not ''//domain/username'.

If you choose to use Integrated Authentication, please be advised that this means that you will use the FileHold Service account's credentials to connect to the SQL Database that you are looking up. In this case, the FileHold Service account must have data reader or higher abilities under the Security Logins in SQL Management Studio in Microsoft SQL Database server.

In addition, your Microsoft SQL Database server may need to be configured to allow external logins and other security considerations may also need to be considered.

When adjusting any of these settings you should then confirm them using the SQL Server Management Studio by looking at the Security > Logins properties and user mappings for the database.

Displays 'Cannot Connect to Specified Source' Error

This error relates to a connection to the database issue and has to do with the database name, server name, db admin user name or the db admin password being incorrect.

This message does not refer to 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 a datareader or higher (db_owner) of the database being looked up if you are using Integrated Authentication.

This can be done in the database software management console looking at the database Security > Logins properties.

NOTE: The Library Administrator may need to work with the System Administrator or someone in your IT department with extensive SQL Server experience to make sure that the server settings are correct and functioning. This is an advanceds topic.

Non Microsoft SQL Database Lookups?

FileHold's Microsoft .NET database lookup technology supports lookups with Microsoft SQL Databases as this is what Microsoft supports.

However, various FileHold customers successfully perform Database lookup from Oracle, Pervasive, Sybase, DB2, and other non Microsoft databases. 

They do this by installing a database driver for these non Microsoft databases on the FileHold database server. This could also be done on other Microsoft SQL Servers. The customer then uses Microsoft SQL and the 3rd party database driver to bring the information to the SQL Database.

Often, this is scheduled or triggered in the Microsoft SQL Server so that this synchronization between the Microsoft SQL Server and 3rd party Database server is done on an hourly or nightly basis.

Once this is all done, then you will create a FileHold Database lookup Metadata field to connect to the Microsoft SQL Server database where this information resides.

With this method you use database drivers and Microsoft SQL Server to integrate and lookup from 3rd party systems. Various ERP, Financial systems, CRM, Accounting, Project Management and other systems have all been successfully integrated with.

Microsoft SQL Drop Down Menu - Important Performance Considerations

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 FileHold session, and is updated constantly throughout the session. A very large database lookup can cause performance issues on the PC and on the FileHold server when consumed thousands of times per hour by a group of active FileHold users.

FileHold recommends that you redesign your view or table / database lookup so that it focused the lookup on relevant or active data.

As a safeguideline 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.

Look for the <add key="MaxDropDownSourceItems" value="1000"/>

If needed, you can adjust this to value 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.

 

 

 

See Also:

Adding Metadata to Schemas

Creating Metadata Fields

Metadata Field Types

Document Schemas