Schema lookup part 1 - Using document schema lookup for intelligent numbering

FileHold contains a robust document control numbering system. It guarantees to give a document a unique number regardless of how or where it is added to the system. It is composed of a both a prefix and number. The number can be automatically or manually generated. It is efficient and effective, but it does not contain any more intelligence than the prefix.

Sometimes document numbering standards used in quality systems or engineering projects have some intelligence incorporated in them. These are a little harder to create and maintain but may be required for legacy or other interoperability purposes.

For example, an engineering project has contributors from unrelated organizations around the world, but the documents identifiers must be prepared in a uniform fashion. FileHold could be the central source for global numbering using the offline document format to hold finished numbers and ensure non-repeating sequential values for each “intelligent” criteria. The offline document format is handy, because it works a little like a database but with a simple user interface and all the power of FileHold for searching, etc.

Let’s assume our numbering system has four criteria plus a sequential number: a project code, the work breakdown structure ID, the responsible organization and the type of document. An individual preparing a new document would request an official document number from the document control team by adding an offline document from the Document number request schema to FileHold. They would fill out the four key fields above.

When the document is added to FileHold a workflow is automatically triggered to send a notification to the document control team. The document control team changes the schema to a Document control number and presses the lookup button to generate the next sequential number. They save the document and complete the workflow task. The requestor gets notified their approved document control number is complete.

Up to this point, everything is just straightforward FileHold configuration and operation. The magic is behind the lookup button. The power of the lookup button is that it can execute arbitrary SQL statements and it is those statements that will form the document number according to our numbering system criteria. To complete our example we are going to create a T-SQL stored procedure that finds the last document control number and determines the next number.

Fair warning, if letters like “SQL” make you queasy, it might be time to get off this ride and hand off what follows to your local technical wiz or the FileHold professional services team. The example that follows helps to illustrate the description above, but does not claim to suit every purpose or demonstrate perfect SQL, etc.

We will start with the schema configuration for the document control number.

Image

We are going to create a stored procedure called zGenerateDocumentNumber and pass it four parameters. It will look to see if a document number has ever been created for the four key fields. If it has, it will calculate the next number in sequence. If there is no document, it will start the numbering from 1.

The code example makes some assumptions about internal values for the document schema ID and the metadata field IDs. These will differ on your system. The document schema IDs can be found in the library manager database DocumentSchemas table. Likewise, the metadata definitions in DocumentSchemaFieldDefinitions.

USE [ch_librarymanager]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[zGenerateDocumentNumber]
(
       @ProjectCode nvarchar(10),
       @WbsID nvarchar(20),
       @ResponsibleOrg nvarchar(4),
       @DocumentType nvarchar(2)
)
AS
declare @SequenceNo int
BEGIN
       SET NOCOUNT ON;
       SELECT @SequenceNo = SequenceNo.FieldData
       from Documents d
         inner join DocumentVersions dv on dv.DocumentId = d.DocumentId and dv.IsLast = 1
         inner join MetadataVersions mv on mv.DocumentVersionId = dv.DocumentVersionId and mv.IsLastForDocumentVersion = 1
         inner join MD1026 ProjectCode on ProjectCode.MetadataVersionId = mv.MetadataVersionId
         inner join MD1027 DocumentType on DocumentType.MetadataVersionId = mv.MetadataVersionId
         inner join MD1028 SequenceNo on SequenceNo.MetadataVersionId = mv.MetadataVersionId
         inner join MD1031 ResponsibleOrg on ResponsibleOrg.MetadataVersionId = mv.MetadataVersionId
         inner join MD1032 WbsID on WbsID.MetadataVersionId = mv.MetadataVersionId
       where mv.DocumentSchemaId = 1007 -- document number schema id
             and ProjectCode.FieldData = @ProjectCode
             and WbsID.FieldData = @WbsID
             and ResponsibleOrg.FieldData = @ResponsibleOrg
             and DocumentType.FieldData = @DocumentType;

       if @SequenceNo is null
             SET @SequenceNo = 0;

       select
              upper( @ProjectCode ) as 'ProjectCode'
             ,upper( @WbsID ) as 'WbsID'
             ,upper( @ResponsibleOrg ) as 'ResponsibleOrg'
             ,upper( @DocumentType ) as 'DocumentType'
             ,format( @SequenceNo + 1, '00#' ) as SequenceNo
             ,upper( @ProjectCode + '-' + @WbsID + '-' + @ResponsibleOrg + '-' + @DocumentType + '-' + format( @SequenceNo + 1, '00#' )) as DocumentNumber;
END

With the stored procedure in place you can verify the query and assign the results to metadata fields.

Image

When this is all setup, your document control managers will be able to do things like this.

Image

Note that we are converting the input values to upper case. This is optional and demonstrates the ability to replace input parameters with new values from the lookup. For convenience, the document number is also returned so it can be assigned to a metadata field. However, a similar result is often possible using custom naming by combining the metadata fields using the custom naming feature. Finally, the fields are all text based, but this same method will work for dropdown fields, etc. The query will just be a little more complex to dereference the dropdown menu IDs to caption values from the DocumentSchemaFieldChoices table.

As you might be able to guess, this solution relies on one person working on a document request at one time. If you enable the workflow task reservation feature, you can achieve this. If you need a more automated solution, something more like the built-in document control number feature can be implemented, but that would require a custom plug-in instead of leveraging simplicity of the lookup button.

The schema lookup feature is available with all FileHold licenses at no extra cost, but you will need to request the custom providers and queries option to be added in order to define a lookup using custom SQL.