Interrogating History

This article provides a brief introduction to selecting histories with the History Worker. It focuses on the getHistories() function, which is used to return one or more histories from the database using a range of filters. Full API documentation can be found in the worker documentation for your release of iCM.

Filters

Filters are used throughout the history service to determine which histories are selected by calls to the various functions. The filter object can include a number of operators.

The boolean operators AND, OR and NOT create structures that evaluate a range of comparison operators (or further nested boolean operators). They evaluate to true or false.

Comparison operators compare the value of a property in a history (defined by a key) to a given value. The comparison operators are EQ (equals), NEQ (not equal), LT (less than), LTE (less than or equal), GT (greater than) and GTE (greater than or equal). If you don't set an comparison operator the filter checks that the specified property exists.

When used on their own filters check the "id", "labela", "labelb", "labelc", "labeld", "labele", "created" and "lastupdated" properties of a history. This is known as the default context. Two context modifiers can be included in your filter:

1. SUBJECT will look in the subject data packet
2. EVENTEXISTSWHERE will check all events in a history. The EVENTEXISTSWHERE modifier can be refined to only look at the INTOP (first) or INTAIL (last) number of events. We recommend using a top-level filter along with EVENTEXISTSWHERE to improve performance (created or lastupdated are useful if you need to query across different histories)

The first examples query the histories written in the Recording History from Stand-Alone Forms article. Take a look back at that article to see the "shape" of the histories.

Example 1 - AND

This filter returns all histories that have a labela of WRITEHISTORY and a labelc of "reporting". Both comparisons must be met for a history to be selected.

"filter": {
    "AND": [{
        "key": "labela",
        "EQ": "WRITEHISTORY"
    }, {
        "key": "labelc",
        "EQ": "reporting"
    }]
}

Example 2 - AND and NOT 

This filter returns histories that have a labela of WRITEHISTORY and do not have a labelc of null. It could be used to return all "WRITEHISTORY" histories that do have a labelc value. You could also use the NEQ operator.

"filter": {
    "AND": [{
        "key": "labela",
        "EQ": "WRITEHISTORY"
    }, {
        "NOT": {
            "key": "labelc",
            "EQ": null
        }
    }]
}

Example 3 - OR

This filter returns all histories for two forms/processes (by convention we use labela to record the form/process/service a history relates to). The OR operator will return any history that meets at least one of the comparisons.

"filter": {
    "OR": [{
        "key": "labela",
        "EQ": "WRITEHISTORY"
    }, {
        "key": "labela",
        "EQ": "chainedevents"
    }]
}

Example 4 - NEQ

NEQ matches everything that EQ doesn't. It should be used with caution and rarely in isolation, as it has the potential to return a very large and unexpected number of histories.

Let's assume that one history contains these events:

    {"name":"Ray", "surname":"Davies"},
    {"name":"Dave", "surname":"Davies"},
    {"name":"Twiggy"}

While another history contains these events:

    {"name":"Liam", "surname":"Gallagher"},
    {"name":"Noel", "surname":"Gallagher"},
    {"name":"Bono"}

This filter will match the first two events of the first history. It will not match the last event of the first history and it will not match any event from the second history.

    {"key":"surname", "EQ":"Davies"}

NEQ would return everything else. For example, this filter would return every history in the database without an event that includes "surname":"Davies" (ie it will return all of the histories that don't have any surname keys at all):

"filter": {  
    "EVENTEXISTSWHERE": {
        "key": "surname",
        "NEQ": "Davies"
    }
}

This filter will only return histories that have events with the "surname" key that doesn't have the value "Davies". Using the example history events above, it would return one history, the one without the Davies brothers in it.

"filter": {  
    "EVENTEXISTSWHERE": {
        "AND": [{
            "key": "surname",
            "NEQ": "Davies"
        }, {
            "key": "surname"
        }]
    }
}

Example 5 - EVENTEXISTSWHERE

In this example we are shifting the context and drilling down into events. The filter is still only concerned with histories that have a labela of WRITEHISTORY, but the event data must also include a property called "nameField" with a value of "Tim".

Note that shifting the context to events can result in expensive database queries. Where possible you should also use a top-level history filter along with the event query to improve performance.

"filter": {
    "AND": [{
        "key": "labela",
        "EQ": "WRITEHISTORY"
    }, {
        "EVENTEXISTSWHERE": {
            "key": "nameField",
            "EQ": "Tim"
        }
    }]
}

Example 6 - EVENTEXISTSWHERE with Form Data

When histories are generated by form submissions, you can optionally store form data in a history event. Form data is always stored in a property called formData, you can see an example in the History Events and Summary Events article. In this example the form data includes a field called POSTCODE, which is what we want to filter on.

"filter": {
    "AND": [{
        "key": "labela",
        "EQ": "WRITEHISTORY"
    }, {
        "EVENTEXISTSWHERE": {
            "key": "formData.data.POSTCODE",
            "EQ": "PL6 7TL"
        }
    }]
}

Example 7 - EVENTEXISTSWHERE with Form Data from Repeating Pages

When a form contains repeating pages, the data from a repeating page is structured as an array of objects (ie an object ofr each instance of the page). The array name is the name of the page, and the keys of each object the field names. So that this data can be queried, a generic array index is used.

Given this data:

"formData": {
    "data": {
        "PAGE1": [{
            "NAME": "Tim",
            "AGE": "41"
        }, {
            "NAME": "Leo",
            "AGE": "40"
        }]
    },
    "formName": "TIMSFORM",
    "typeName": "FORM_TIMSFORM",
    "formDefinitionType": "FORMDEFINITIONEX"
}

To filter by a particular age, use the generic index [*]:

"filter": {
    "AND": [{
        "key": "labela",
        "EQ": "WRITEHISTORY"
    }, {
        "EVENTEXISTSWHERE": {
            "key": "formData.data.PAGE1[*].AGE",
            "EQ": "41"
        }
    }]
}

This will match against the AGE key in any instance of PAGE1. It's not possible to target a specific instance in the array.

Example 8 - Multiple Operators

This example is slightly artificial, but shows how the different operators can be used together.

The filter will only select histories created since the 1538052735426 timestamp (lunchtime on 27/09/2018). Those histories must also have either a labela of "Chained Tasks" or an event in the first two events of the history with a property called "nameField" with a value of "Tim". This means we'll get back all the "Chained Tasks" histories created since the 1538052735426 timestamp, plus any history created since then that has "nameField": "Tim" no matter what other properties they have.

"filter": {
    "AND": [{
        "key": "created",
        "GT": 1538052735426
    }, {
        "OR": [{
            "key": "labela",
            "EQ": "Chained Tasks"
        }, {
            "EVENTEXISTSWHERE": {
                "AND": [{
                    "INTOP": 2
                }, {
                    "key": "nameField",
                    "EQ": "Tim"
                }]
            }
        }]
    }]
}