Document plug-in part 2: document validation

A few months ago, I introduced several use cases for the document plug-in in part 1 that centered around updating metadata in the document. For part 2, we will cover the plug-in’s other trick: validating document metadata. I will present several new use cases that along with the corresponding hybrid SQL to put them into action.

This article is intended for a FileHold administrator. Skills with the SQL language are also useful.

Use case: Conditional value requirement

It is a simple matter to declare the value of a metadata field to be required in the document schema configuration, but you will need the plug-in to handle a situation where the field is only required in certain circumstances.

The ### update-check # modifier of the document plug-in will make it possible to provide the conditional logic. It must return two columns to the plug-in “IsOkayToUpdate” and “UpdateFailureMessage”. When the first column is 0, the document can be added or modified as normal. When a 1 is returned, the user will get an error message with the extended error information provided by in the second column.

Conditional field entry requirements arise in several circumstances. Often there is a hierarchy of values where some branches of the tree are longer than others. Drill down menu fields can handle this situation well effectively predefining the conditional situation by how the branches of the tree are defined. However, a drill down menu is not satisfactory in every situation. For example, the value in any of the nodes cannot be predefined.

Any example of a conditional field entry requirement is a human resources request form where the data that needs to be collected differs according to the request type. A paternity leave request likely has different data collection requirements than a name change request.

Another scenario is a soft list. The user must choose a value from a list of possible options in a dropdown menu. It is possible that not every value is captured in the list and the system administrator would like to allow users to enter values ad-hoc when the user chooses the value Other from this list. In this case, and only this case, they are required to make an entry in the Other field.

No special knowledge of the system tables is required as we only need to check the dropdown value for Other and when it exists check that a value has been entered in the Other field. We can also enhance the error checking by preventing a value in the Other field when Other is not selected. Check out part 1 if you want to erase the value in the Other field when it should not be there.

Let’s assume we have a dropdown menu called Condition and a text field called Other condition.

We are going to define a variable, @otherOk, in SQL which will have one of three values:

• 0 – Other condition should have a value but does not,
• 1 – Other condition should not have a value but does, and
• 2 – All is good.

We will put the logic into a stored procedure to simplify testing, configuring the document schemas and making it easy to use across many similar dropdown fields.

A stored procedure is optional for many cases, but unless the hybrid SQL is really short it is recommended. Aside from the maintenance complexity and security hazards, eventually the hybrid SQL will exceed the size of the description field. Hybrid SQL supports only SELECT and EXEC statements.

CREATE PROCEDURE [dbo].[zfh_ValidateOtherCondition]
    @other nvarchar(100),
    @otherCondition nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    declare @otherOk as int = ( 
        select case when @other = 'Other' and @otherCondition = '' then 0
                    when @other <> 'Other' and @otherCondition <> '' then 1 
                    else 2 end
    )
    select 0 [IsOkayToUpdate], 'The ''Other condition'' field must have a value.' [UpdateFailureMessage]
        where @otherOk = 0
        union all
    select 0, 'The ''Other condition'' field must be blank.'
        where @otherOk = 1
        union all
    select 1, 'Success'
        where @otherOk = 2
END

Add the following to the document schema description.

### update-check #
exec zfh_ValidateOtherCondition [[md|Condition], [[md|Other condition]]

Use case: Validate date values

Employee records often contain dates related to the employee’s information or their employment information. For example, a document may have the employee’s start and termination date. One thing we know for sure, no employee can be terminated before they start so it would be nice to add a validation criterion to the metadata panel to avoid a fat fingered entry of an obviously wrong termination date.

As with the previous use case, no knowledge of FileHold tables is needed for this one as only a simple calculation is necessary. We can even enhance the validation a bit by comparing the start date to the incorporation date of the company as no start date could be before that.

For clarity, we will use a stored procedure again and pass the hybrid SQL metadata values as parameters. Make sure to include the data format that SQL likes (year, month and day) for text dates to avoid any conversion or localization issues. We will also assign the results of the date tests to variables as we need them in three places.

CREATE PROCEDURE zfh_ValidateEmployeeDates
    @startDate datetime,
    @terminationDate datetime
AS
BEGIN
    SET NOCOUNT ON;
    declare @originDate datetime = '20200101'
    declare @startOk bit = ( 
            select case 
                when datediff( day, @origindate, @startDate ) < 0 then 0 
                else 1 end 
        )
    declare @terminationOk bit = ( 
            select case 
                when datediff( day, @startDate, @terminationDate ) < 0 then 0 
                else 1 end 
        )
    select 0 [IsOkayToUpdate], 'Start date before origin date.' [UpdateFailureMessage]
        where @startOk = 0
        union all
    select 0, 'Termination date before start date.'
        where @terminationOk = 0
        union all
    select 1, 'Success'
        where @startOk = 1 and @terminationOk = 1 
END

Add the following to the document schema description.

### update-check #
exec zfh_ValidateEmployeeDates [[md|Start date]], [[md|Termination date]]

Use case: Prevent duplicated document names

For many customers, document names have a special meaning in certain contexts that make it inappropriate for them to be duplicated. This may be a simple check on users adding the same document twice or something deeper. FileHold does not normally care about the specific content of a document name and it is valid to use the same name more than once for any given context.

We will use the standard [[sys|document-name]] merge tag to create the necessary hybrid SQL.

The key is finding a query that will count the cases where there is a match to your desired criteria. Anytime this query produces more than zero results, there is already a matching document in the system and the new or changed document should be rejected. We will need join the Documents, DocumentVersions, and MetadataVersions tables from the library manager database in order to get the document name and we will want only the very latest version of the document name.

SELECT count(*) as MatchingDocumentCount 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
WHERE mv.DocumentName = [[sys|document-name]]

As we will want to use this count twice to return our result columns, it will be simpler to make this a common table expression (CTE) and we will return our two result cases with a UNION though a CASE could be made to work equally well here. We will also package it into a stored procedure as before. 

CREATE PROCEDURE zfh_ValidateDuplicateName
    @documentName nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    WITH MatchingDocuments (MatchingDocumentCount)
    AS ( SELECT count(*) 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
       WHERE mv.DocumentName = @documentName )
    SELECT 0 [IsOkayToUpdate], 'Document name ''' + @documentName + ''' already exists.' [UpdateFailureMessage]
       WHERE ( SELECT * FROM MatchingDocuments ) > 0
       UNION ALL
    SELECT 1, 'Success'
       WHERE ( SELECT * FROM MatchingDocuments ) = 0
END

If we join the library object tables in our MatchingDocuments query, we can include library restrictions in our critiera. For example, we want our duplicate check limited to the Human Resources cabinet.

INNER JOIN Folders f on f.FolderId = d.FolderID
INNER JOIN Drawers drw on drw.DrawerId = f.DrawerId
INNER JOIN Cabinets c on c.CabinetId = drw.CabinetId and c.CabinetName = 'Human Resources'

Similarly, it might be logical to restrict our duplication check to include the document schema. The document schema ID is already in the metadata versions table and we can get the ID for the current document to use in our WHERE clause like @documentSchemaId = mv.DocumentSchemaId.

declare @documentSchemaId as int = (
   SELECT DocumentSchemaId FROM DocumentSchemas 
      WHERE Name = [[sys|document-schema]]
);

As always, put the modifier in your document schema description to activate the plug-in.

### update-check # exec zfh_ValidateDuplicateName [[sys|document-name]]

Obviously, these example solutions provide very simple feedback to the user, but they could be easily enhanced to increase validation options and error message responses.

If you would like to add the document plug-in to your system, just contact your account manager at sales@filehold.com. Self-hosted customers can deploy these or their own solutions on their own or with the help of the professional services team. Cloud customers will need a small engagement with the professional services team to deploy the plug-in.