Routing Files to a Project Based on Project Metadata

Introduction of Topic

DALIM ES input channels are quite powerful to identify portions of filenames which project to assign the incoming document. However, sometimes the filename does not match at all with the project name and the only link between the document and the project may be a metadata of the project.

In such a case, there is no direct way to assign the document to the project as you first will need to identify the project!

Therefore in this tech blog, we will explain how you can use a temporary "buffer project" to do file routing based on an ES-API query which will get the project id of ES based on a custom metadata such as your favorite ERP generated Jobnumber.

All these tasks are covered by the features of DALIM ES using a little bash script step run by the ES JDF Device.

Ingredients Needed

So, what will you need to achieve this?

DALIM ES with the following license options
- ES Enterprise Project Tracking (ES-5400) to add specific milestone approval steps.
- ES Custom Job Ticket (ES-5300) to add a custom layout to your approval step.
- ES API option (ES-5900) to run queries to identify projects based on their metadata
Some basic skills on workflow editing with DALIM ES.
The opensource "jq" utility for Linux found here https://github.com/stedolan/jq/releases.

No advanced scripting skills are required as all is provide here.

Step by step guide

Let's imagine that you have a custom metadata named like "Jobnumber" which is part of the metadata name space ERP-MDT.

This metadata Jobnumber is set in every project you are creating in ES and may come from your ERP for example.

The files you are receiving are named with this kind of naming convention: Jobnumber_whatever.pdf

Setup

There are a few steps to follow but it is not very complicated as you will see.

Creating the Input channel metadata

The first thing we need is to enable the metadata ERP-MDT:Jobnumber for the input channel.

You will have to go to the System Configuration menu of ES and add the metadata in the RuleSet properties options:

This will allow to set it in the input channel's rule set.

Creating the magic Step Script GetJobID.sh

Take your favorite Linux text editor and create the following file /symlnks/var/6.0/scripts/GetJobID.sh.

Place into it the following code:


    #!/bin/bash
    # DisplayName = GetJobID.sh
    # Interpreter = bash
    # Arguments = Arg1 Arg2
    # Arg1.Type = String
    # Arg1.Name = Jobnumber
    # Arg2.Type = InputFile
    JOBNumber="$1"
     
    if [ -z "$JOBNumber" ]
    then
        echo "Missing parameter 1 (JOBNumber)"
        exit 1
    fi
     
    ### Run Session
    JOBID=$(curl 'http://localhost:8080/Esprit/public/Interface/RPC' --header 'Content-Type: application/json' --user 'admin:adminpassword' --data "[{'id': 1,'method': 'admin.login'},{method:'production.executeSQL', params:{sql:'SELECT ID FROM Job where ERP\\\\-MDT:Jobnumber=\\'$1\\''}},{'id': 3,'method': 'admin.logout'}]" |jq -r '.[1] .result.objectList' |jq .[]|jq .[])
    echo $JOBID
    if [ -z "$JOBID" ]
    then
        echo "JOBID is empty"
        echo ESResult:MetaData/:TWISTMDT/PDFRouting=Failed 
    else
        mkdir -p -m 777 /symlnks/process/es/HotFolders/PDF_INPUT/Jobnumber/$JOBID
        cp "$2" /symlnks/process/es/HotFolders/PDF_INPUT/Jobnumber/$JOBID/
        echo ESResult:MetaData/:TWISTMDT/PDFRouting=Succeed
    fi
    exit 0

As you can read from the scrip code, it executes an API call to ES to find out the JOBID corresponding to the metadata ERP-MDT:Jobnumber which is given as 1st argument.

The script as well sets a return variable TWISTMDT:PDFRouting to either Failed or Succeed.

This metadata could be used as a smartview filter to filter files which have failed to be routed and are waiting in the "buffer project".

Notice as well that the script directly creates a hotfolder entry /symlnks/process/es/HotFolders/PDF_INPUT/Jobnumber/$JOBID and copies the incoming file into it.

Do not forget to set the proper login and password for your API user!

Above we simply have set it for admin user with adminpassword.

Give the script some execution permissions with:


    chmod 755 /symlnks/var/6.0/scripts/GetJobID.sh

And finally, restart tomcat to make it read in the new script:


    sudo /symlnks/common/services/tomcat restart

Install the jq utility

Please follow these installation steps:


wget -O jq https://github.com/stedolan/jq/releases/download/jq-1.6/jq-linux64
chmod +x ./jq
cp jq /usr/bin

Creating the Routing workflow

Please go to the workflow Administration and create a new workflow called "DOC_Routing" for example:

You should place at least an InputFile followed by our nice GetJobID step.

The GetJobID step receives as parameter the so called ERP-MDT:Jobnumber metadata as set up here:

The upcoming links are taking the decision to route the file to a branch that has found successfully the matching Project ID which drives it to a milestone to approve (with automatic release after 4 hours for example), or to a branch where the file will sit forever as long as nobody has not taken care of it.

Your routing workflow is now ready!

Creating the Routing buffer project

Please go to the Customer Administration and create a customer named DocumentRouting.

You should create a new template DocRoutingTPL for example to which you assign the document workflow DOC_Routing.

Now you can create the the buffer project DocumentRouting underneath the DocumentRouting customer.

You are now fully set to accept documents in that project and get them routed to the proper customer project having your favorite ERP-MDT:Jobnumber metadata!

Creating the Input Channel and its rule to automate file input

It would be a pity to automate so much things but have no hotfolder to get the files imported to the buffer project.

Let's create a rule set at first which we will call "ES_ROUTING" and configure as bellow to match names like ([0-9+])_(.*):

The production structure looks like this:

You see here that the Customer is hardcoded to DocumentRouting and that the Project Name is hardcoded too to DocumentRouting.

The Document Name is kept as it comes in with #1_#2

Finally, do not forget to set our custom metadata ERP-MDT/Jobnumber to #1 ! this will be the variable that will be passed to our step script.

The processing rule is just configured to trigger our DOC_Routing workflow:

Now we can create an input channel using that rule set:

Here we have configured the hotfolder to be /symlnks/process/es/HotFolders/PDF_Routing for example.

We assign the ES_Routing rule set to it.

From now on, all files entering there will be processed by our DOC_Routing workflo.

But they will land into a folder which is not surveyed for the moment....

Create the rule set and input channel for final project

Please go to the Rule Set again and create a new rule set ES_INPUT to accept the files out of our routing process:

The production structure looks like this:

Regular expression: Jobnumber/([0-9]+)/(.*)

Project ID: #1

Document Name: #2

The processing rule simply let the file run by the default workflow of the destination project:

Finally, we need to create the according input channel PDF_INPUT:

It will watch the output folder of our script: /symlnks/process/es/HotFolders/PDF_INPUT

It will use our newly defined rule set ES_Routing

Now everything is ready to run production!

Results

Imagine you have a project having the ERP-MDT:Jobnumber set to 1601281 ready to receive files.

Put a file such as 1601281_21MY_V2_DEU_de_5-11.pdf into /symlnks/process/es/HotFolders/PDF_Routing.

After 20 seconds, it will show up in the DocumentRouting project:

It then gets inspected, its TWISTMDT:PDFRouting metadata gets set to Succeed and the file gets routed to the project having that jobnumber.

The "TimeClock" milestone will automatically release itself after 4 hours and the DocumentStep will delete the file.

As a counter example, our file 123_toto.pdf did not get routed. Its metadata PDFRouting is set to Failed:

It shows up in our Routing problem smartview:

DALIM ES