How to Fetch Documents with a Query on Date Parameters

In this how-to we will see how to make it possible to process a list of document depending on a date parameter. An example would be how to display or do an operation on all documents expiring in less than 3 months.

Creating a Dedicated Metadata

The first step is to add a date-type metadata to our document type. We will use this new metadata as a landmark for our future queries. Let's say we want to recall the expiring date 3 months before it happens.

To know more about how to define a  document type and add metadata check out the page How to Define a Document Type.

1. Create a new document type called "mydocumenttype".
2. In the Schema tab of the document type, add two date-type metadata:
   • "expiring_date",
   • "recall_expiring_date".
     ×
     

Filling in the Metadata

It would be possible to let users fill the recall date manually. But it is more interesting to update it when the document is created or updated.

Filling in the Metadata When the Document Is Created

Let's create an automation chain that first copies and saves the "expiring_date" to the "recall_expiring_date" metadata. Then the chain withdraws three months to the "recall_expiring_date".

Automation Chain

Create the following automation chain:

- Context.FetchDocument
- Document.SetProperty:
    xpath: "mydocumenttype:recall_expiring_date"
    save: "true"
    value: "@{Document[\"mydocumenttype:expiring_date\"].clone()}"
- Context.RunScript:
    script: "Document[ 'mydocumenttype:recall_expiring_date'].add(2,-3);"
- Document.Save

Event Handler

You can then bind the automation chain to any event or action you want. For example, to fill in the "recall_expiring_date" when the document is created,  create a new event handler with the following properties:

• Events: Document created Make sure that the expiring date is mandatory at the creation.
• Current document has one of the types: mydocumenttype

Updating the Metadata Automatically When the Document Is Modified

It is also possible to have the "recall_expiring_date" updated when a user modifies the document. The chain will be similar to the one used for creation of the document, but doesn't include any "save" step.

Automation Chain

Create the following automation chain:

- Context.FetchDocument
- Document.SetProperty:
    xpath: "mydocumenttype:recall_expiring_date"
    save: "false"
    value: "@{Document[\"mydocumenttype:expiring_date\"].clone()}"
- Context.RunScript:
    script: "Document[ 'mydocumenttype:recall_expiring_date'].add(2,-3);"

Event Handler

Create a new event handler with the following properties:

• Events: Before document modification
• Current document has one of the types: mydocumenttype

Using the Metadata in a Query

The "recall_expiring_date" is most commonly useful in the two following cases:

• To display all documents expiring soon, using a content view;
• To make operations on the documents after a predefined period of time, using automation chains.

Listing Expiring Documents Using a Content View

In content views the way to fetch all documents for which the "recall date" is outdated is to define the query filter.

1. On the Query & form tab of your content view, define your query filter.

   ecm:mixinType != 'HiddenInNavigation' AND ecm:isVersion = 0 AND mydocumenttype:recall_expiring_date <= DATE ?
   
   

2. Define the date query parameter:

   #{currentDate.toString()}
   
   

   You will probably want to add other criteria to you query filter. Adding ecm:currentLifeCycleState will only fetch documents in the appropriate life cycle state. Adding ecm:path will only fetch documents located at the specified folder.

3. Click on the Results tab and select the relevant information to display on the result table. For instance:

   • Title with link,
   • Expiring date,
   • Recall expiring date,
   • Life cycle state.You can then leverage this new content view in a tab on a custom folder document type for instance.
     ×
     

Processing Expiring Documents Using an Automation Chain

If you want to make an operation on all the documents that expire soon, you will use an automation chain. This chain will start with the Fetch > Query operation It will produce a list of documents that operations accepting documents can use.

Let's take the example of an automation chain does the following step:

1. It fetches all visible and deleted documents whose "recall expiring date" is outdated;
2. It deletes these documents (moves them to trash).

The chain will look like that:

- Document.Query:
    query: "SELECT * FROM Document WHERE ecm:mixinType != 'HiddenInNavigation' AND ecm:isVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND mydocumenttype:recall_expiring_date<= DATE '@{CurrentDate.format(\"yyyy-MM-dd\")}'"
    language: NXQL
- Document.SetLifeCycle:
    value: delete
- Seam.Refresh: {}