Using internal FileHold data in a dropdown list

FileHold provides the option for administrators to create either FileHold managed or database dropdown lists for metadata. The FileHold managed lists provide a nice user interface for adding, renaming, deleting, and reordering the dropdown values. The database dropdown provides great flexibility, but no additional help for managing the list. If you already have another application that manages the list, this works well, but if not, you must find another way.

FileHold provides many features for users to manage data in the form of documents. Documents can be actual electronic documents or simply references to documents that are stored elsewhere (offline). With a little bit of knowledge about how FileHold stores this data, it can be leveraged as values in database dropdown lists. After all, it is stored in the FileHold database. This means that document type (schema) and folder permissions can be used to control how users can update values in the list without making them library administrators.

This technique can be used in any case where there is information in the FileHold database that can be repurposed in a dropdown list.

The rest of this text is intended for FileHold library administrators and SQL database administrators. We are unable to support this material under your FileCare subscription, but our professional service team would be happy to provide any assistance that may be needed.

You should note that when you configure the system in this way you are bypassing the normal controls that the document management system places on permissions. You will need to configure the system to ensure that information can only be seen by those it is intended for. While most database tables in FileHold are very stable, it is possible that they may change from version to version. Before upgrading the software, check to see if there have been any changes to tables you rely on.

Database dropdown field review in the document management software

We will start with a little refresher into the workings of the database dropdown field. These are defined like all metadata fields. After selecting the database dropdown metadata field type you will have the option to configure the field properties.

In this simple example from FileHold 12, the dropdown would display a list of all FileHold ids in the system. Care should be taken with the amount of data that is returned by this lookup for FileHold version 12 or earlier as all records are delivered to the client for selection. Starting with FileHold 14 you can enter a custom query instead of a table. This will make it possible to join multiple tables without using an SQL view.

The scenario

The document management system is storing product designs. Each design has a unique document control number. Many individual design documents may be associated with the design. A master design document is assigned a unique control number managed by the document management system. This control number is included in the name of the documents using custom naming for when they are used outside of the document management system. Some of these documents represent higher level groups of documents. The challenge is how to create the parent child relationship between the master design document that has the control number and all the subsidiary design documents.

For example, if the control number for the master design is CFG21980, the name of document with the title Mechanical Design should be CFG21980-MechanicalDesign.

Linked documents handle the problem of creating the parent and child relationships very nicely, but there is no method to include the linked relationship in the filename automatically generated by the document management system.

An alternate method is to add a metadata field to the child documents for entering the control number of the parent documents. This method works fairly well, but it is up to the user to find a way to locate the correct control number.

Of course, the document management system contains a list of control numbers in its internal database. If we could list those control numbers, or better yet, the document names, the user would have much less chance of making an error.

Underneath the covers of the document management system there is a fairly complex set of database tables and inter-relationships, but with a little bit of detective work we can remove these complexities and create a database view that can be easily used with the database dropdown.

Database detective work

Documents exist at a number of different levels in FileHold. You can see this in the user interface. At the top level you have the current document, you can drill in to see the document versions including the current document, and then you can drill further to see metadata changes to the document.

Document view

The Documents table gives us a list of documents in the system with a unique DocumentId column. The value in the DocumentId column will match the FileHold ID you see on the screen.

Document version view

For each document there is at least one entry in the DocumentVersions table. Like the documents table, this table has a unique DocumentVersionId for each document version, though this identifier is only internal to the system. There is a column in this table called IsLast, which is a quick way to tell if the record is the current document version.

Document metadata version view

For each document version there is at least one entry in the MetadataVersions table. This is where its starts to get interesting. The DocumentName is contained in this table. Like the document versions table, the IsLastForDocumentVersion column indicates which row is the latest.

Document control numbers

Much like the FileHold ID, document control numbers provide a unique method to connect with one-and-only-one document. A big difference is that they do not depend on the document management system. When we are using a document control number the Documents table no longer necessary for our query.

The document control number is broken into two parts: the prefix, in the DocumentControlFields table, and the number, in the DocumentControlNumbers table. The document control number contains only a link to the document, so we will need to look to the document schema, in the DocumentSchemas table, to make the connection to the prefix.

We will need the DocumentSchemaId from the metadata versions table to get the DocumentControlFieldId in the document control fields table, which will get us to the control number prefix and provide us with the unique record. There is one problem using the prefix directly as this is normally a changeable value in the document management system. If we use it as our unique key, it can never be changed; even accidentally. If we use the document control field id directly we will not have this problem.

The SQL

There is no way to connect a database dropdown field to a complex set of tables like this. However, we can point the dropdown field to a database view and we can create a database view with this complex structure quite easily. In our example we have created a document type called Package with a document control number called ConfigurationId.

The control number is an automatically assigned value with a prefix of CFG. We can add a few documents with the document schema of Package.

The following SQL will get us this list from the database.

SELECT MetadataVersions.DocumentName, CAST(DocumentControlFields.DocumentControlFieldId AS varchar) + '-' + DocumentControlNumbers.Number AS [ConfigurationId] 
FROM MetadataVersions INNER JOIN DocumentVersions ON MetadataVersions.DocumentVersionId = DocumentVersions.DocumentVersionId 
INNER JOIN DocumentControlNumbers ON DocumentVersions.DocumentId = DocumentControlNumbers.DocumentId 
INNER JOIN DocumentSchemas ON MetadataVersions.DocumentSchemaId = DocumentSchemas.DocumentSchemaId 
INNER JOIN DocumentControlFields ON DocumentSchemas.DocumentControlFieldId = DocumentControlFields.DocumentControlFieldId 
WHERE (MetadataVersions.IsLastForDocumentVersion = 1) AND (DocumentVersions.IsLast = 1) AND (DocumentSchemas.Name = N'Package')

This view from SQL Management Studio is exactly the list we need for creating our database dropdown field. The first step is to add this as a database view. For the purpose of the example below we will call it z_PackageList. We will be able to use the database view when we configure the dropdown field as though it was a table.

The following screen shows the configuration of the metadata field.

If a new Package document is added to the system it will be added to the dropdown list after the database synchronization task runs. This normally happens once every hour, but it can be run manually from the Task Scheduler Library on the FileHold server if needed. The task name is FH database lookup fields synchronization. Likewise, if a package document is deleted or renamed the dropdown list will update accordingly.

Metadata pane with dropdown list example

Advanced use of internal FileHold data in a dropdown list in the document management software

There may be situations where it is desirable to be able to use two values from the database lookup. For example, you would like to select a value using a human readable name and use a control number as part of an automatically generated filename. This is all possible with a little change to the configuration.

We need to start with a small change to the SQL we use to create our view.

SELECT MetadataVersions.DocumentName, CAST(DocumentControlFields.DocumentControlFieldId AS nvarchar) + '-' + 
DocumentControlNumbers.Number AS ConfigurationId,
MetadataVersions.DocumentName + ' ~ ' + CAST(DocumentControlFields.Prefix AS nvarchar) + '-' + 
DocumentControlNumbers.Number AS DropDownCaption
FROM MetadataVersions INNER JOIN
DocumentVersions ON MetadataVersions.DocumentVersionId = DocumentVersions.DocumentVersionId INNER JOIN
DocumentControlNumbers ON DocumentVersions.DocumentId = DocumentControlNumbers.DocumentId INNER JOIN
DocumentSchemas ON MetadataVersions.DocumentSchemaId = DocumentSchemas.DocumentSchemaId INNER JOIN
DocumentControlFields ON DocumentSchemas.DocumentControlFieldId = DocumentControlFields.DocumentControlFieldId
WHERE (MetadataVersions.IsLastForDocumentVersion = 1) AND (DocumentVersions.IsLast = 1) AND (DocumentSchemas.Name = N'Package')

This will produce a new column in our view: DropDownCaption.

Now we can update the field definition from before to display our new DropDownCaption field for the field caption.

Database dropdown configuration example 2

The metadata panel values will be updated accordingly.

Finally, we can configure our custom naming to extract the configuration number for our document name. Note the field mask for the first profile field: R10. This tells the software to use the last 10 characters from the field.