The following help article will show library administrators how to setup FileHold Drop Down menus where the menu options are pulled from a third party SQL database.

Sample Database Lookup Use Case
A customer is using Microsoft CRM and wants to have it so when users add and tag documents to the FileHold library they are able to use a 'customer name 'drop down menu (pick list) that has menu options dynamically populated from the central list of customers as taken from the CRM system.This will allow them to link or tag documents with clients by using a controlled list of clients that is always current. If the CRM system is running on Microsoft SQL server this lookup could be done directly from the CRM data store. If not the CRM system could export (on a scheduled basis) the customer list values to a SQL server that FileHold can lookup from.

Where do I go to setup a database lookup drop down menu field?
1) Logon as a user with Library Administration privileges, then go to Library Administration > Manage Schemas > Metadata Fields.
2) Create a new Metadata Field by clicking on "Add Metadata Field"
3) Give the index field the name "Customer List" for example.
4) Select from the drop down menu of Field Type: "Drop Down Menu Database Lookup"
5) Select the "Manage Field Source" button to show the configure field source settings form (see below). This form allows you to configure which database to connect to, the username and password for the database and the table / field to look the menu items up from. Clicking the preview button will allow you to preview how the menu items will appear to the end user by completing the database connection.
6) Once completed, tested (previewed) and saved insert this metadata field into 1 or more document schemas. If you reuse this database lookup field in multiple document schemas then the FileHold software will allow users to search across all document schemas they have membership to and that are using this lookup field.

What is the difference between the Field Caption and Field ID fields?
The 'Field Caption' value should be the column of the table that contains the menu item captions (ie the menu items users select from) while the 'Field ID' value should be the column of the table that contains the menu item IDs (in the event that the lookup table uses both caption and ID). If there is only one column in the table (i.e. caption only) then 'Field ID' is not required. field (a 'Field Caption' value is always required).

What is the format of the 'server name' field value?
The server name field Is the name of the machine hosting the database you are looking up from. The format for the server name is the name only (e.g. 'server-name') and it does not require forward slashses (e.g. '//server-name'). If the database is hosted on the same server as FileHold you can also use '(local)', please note the parentheses around 'local'.

What is the format of the database username field?
If you choose not to use integrated authentication and decide to enter a manual user name the format for the database username field is just 'username' not ''//domain/username'. Ensure that the FileHold Service Account must be the db_owner of the database being looked up. This can be done in the database softwares management console looking at the database Security > Logins properties.

I am getting a 'displays cannot connect to specified source' error, what does this mean?
This is 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. 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 must be the db_owner of the database being looked up. This can be done in the database softwares management console looking at the database Security > Logins properties..