Automating lookup value changes

The schema lookup feature can use a single metadata field can to be the key to populating many other metadata values. The lookup is performed whenever the user pressed the Lookup button. The user is able to edit the lookup data as needed. There will be no changes recorded to the metadata until the user presses Add or Save.

Starting with FileHold version 16.0, an administrator can define up to five metadata fields as the keys to the lookup when they use a custom query.

FileHold does have one type of metadata field that can automatically be updated based on information in an external database: database dropdown menus. The internal FileHold id for each field value does not change, but the displayed text that goes with that id can. We can leverage this knowledge to create a link between an one time lookup and any associated values in an indirect way that will be automatically updated by FileHold.

For the purposes of this illustration we will look at a scenario used for tracking projects. There is a project tracking database that has three tables. Project document schemas require three metadata values: project name, project description, project manager name, and customer name. Users will select the project name and use it as the basis for looking up the other values.

This article assumes the reader is familiar with database management and solution design. FileHold professional services are available as an option where expertise is required.

Projects table

Column	Description
ProjectId	A unique and not changing identifier for each project.
ProjectName	The name of the project.
ProjectDescription	Full description of the project.
ProjectManagerId	The employee id for the project manager.
CustomerId	The customer id.

Employees table

Column	Description
EmployeeId	A unique and not changing identifier for each employee.
EmployeeName	The name of the employee.

Customers table

Column	Description
CustomerId	A unique and not changing identifier for each customer.
CustomerName	The name of the customer.

Configuring FileHold

We will start by creating a dropdown menu metadata field for each of for values: project name, project description, employee name, and customer name. The external database with the values for the dropdown menus will be on the local database server named ProjectTracking. For each field we will use the unique id field as the Lookup By value.

Project Name field definition

Project Description field definition

Project description metadata field descriptoin

Project Manager field description

Project manager metadata field description

Customer field definition

Project document schema

The fields we just created are assigned to the document schema.

With the metadata fields in place we can setup the lookup definition. According to the definition below FileHold will perform the lookup by searching the ProjectId column on the external table. It will get the ProjectId value from the hidden Lookup By value from the Project Name database dropdown menu field. With the record retrieved from the Projects table we can map the values to our other document schema fields. Since our destination fields are all database dropdown menu fields we will use the hidden id fields to make the assignment. This is the only way that FileHold maps to database dropdown menu fields. The id fields in the source column each match the Lookup By values from the metadata field definitions. The Project Description field needs to be mapped in the same way as the others even though it is created from the same table as the Project Name as they are completely independent fields in FileHold.

The following diagram illustrates the Project Name selection being used to get the hidden ProjectId. This is used to find the database lookup record, which is then used to search for the FileHold metadata field values by the hidden Lookup By ids and map the values back to the document.

In the FileHold user interface this mapping is provided in the following dialog.

Document metadata panel

Now we can test our configuration. When the user adds or edits the document they simply need to select the project name and press the lookup button. The other three field values are populated automatically from the external database. The user does not need to select these values manually. Since we have created these fields as database dropdown menus that are automatically synchronized with the external database any changes to the external database will automatically be reflected on the document. No user intervention is required.