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.
|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.|
|EmployeeId||A unique and not changing identifier for each employee.|
|EmployeeName||The name of the employee.|
|CustomerId||A unique and not changing identifier for each customer.|
|CustomerName||The name of the customer.|
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 Manager 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.