Guides and Tutorials

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.

This how-to requires knowledge about:

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

For more informations on the add() parameter, you can take a look on the Calendar Java Class documentation.

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.

A chain called upon the modification of a document should not include a save-related operation. A save operation calls document modification events and the chain would get into an infinite loop.

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: {}

 


3 months ago manonlumeau NXDOC-1831: remove opensocial parameters
4 years ago Solen Guitter 30
5 years ago Solen Guitter 29
5 years ago Solen Guitter 28
5 years ago Solen Guitter 27
5 years ago Solen Guitter 26 | Add screen shots
5 years ago Solen Guitter 25 | More detailed steps and link update
5 years ago Solen Guitter 24 | fix formatting
5 years ago Solen Guitter 23
5 years ago Manon Lumeau 22
5 years ago Solen Guitter 21
5 years ago Alain Escaffre 20
5 years ago Solen Guitter 18
5 years ago Solen Guitter 19
6 years ago Solen Guitter 17
6 years ago Solen Guitter 16 | Added excerpt
7 years ago Frédéric Vadon 14 | typo
7 years ago Frédéric Vadon 15 | Migrated to Confluence 4.0
8 years ago Solen Guitter 13 | Added related tutorials
8 years ago Solen Guitter 12 | added related howtos
8 years ago Frédéric Vadon 11
8 years ago Alain Escaffre 10
8 years ago Frédéric Vadon 8
8 years ago Frédéric Vadon 9
8 years ago Frédéric Vadon 7
8 years ago Frédéric Vadon 6
8 years ago Frédéric Vadon 5
8 years ago Frédéric Vadon 4
8 years ago Frédéric Vadon 3
8 years ago Frédéric Vadon 2
8 years ago Frédéric Vadon 1
History: Created by Frédéric Vadon

We'd love to hear your thoughts!

All fields required