Filters

It is often relevant to only work on a subset of all the data available in the CRM system. Such subsets can be defined with filters.

For example instead of working with all deals the user can select to see my deals.

Pre-defined filters are called filter sets. They can be selected in the filter menu and further refined using column filters directly in the table view. They can then be saved back. Administrators can create shared filters available to all users, while other users can create private filters only they can see.

The Filter set picker. Here you save and re-use filter sets.

Filter Editor (JSON)

Each filter is represented in the webclient in the form of a JSON. This filter is then passed to Lime Query to fetch the matching records. For advanced scenarios it is possible to edit this JSON structure directly by using the Filter Editor. The filter editor comes in handy when you want to create a filter that is not possible to achieve using column filters.

The editor is opened by clicking the button in the header.

The code editor. Lets you build any kind of filter you want.

Below are some examples of filters that are not possible to create using column filters, and thus you need the Filter editor.

Filter based on the logged in user

In order to build filters that matches against the currently logged in user, you use $me in the exp part of a filter operation.

{
    "key": "coworker",
    "op": "=",
    "exp": "$me"
}

Filters can contain expressions like $me or $me.x.y where x.y denotes a path of objects related to current coworker. $me.office.name would be the name of the office the user belongs to etc.

Example: Others' deals at my office

Wanted behavior: Show all deals where the sales responsible belongs to the same office as the logged in user, excluding the deals for the currently logged in user.

How to configure:

{
    "op": "AND",
    "exp": [
        {
            "key": "coworker.office",
            "op": "=",
            "exp": "$me.office"
        },
        {
            "key": "coworker",
            "op": "!=",
            "exp": "$me"
        }
    ]
}

Filter with relative dates

When building date filters, it's common to want to compare a dynamic date relative to the current date. Such filters changes its result set over time.

To support this filters has the following dynamic values:

- $previous_day(x)
- $yesterday
- $today
- $tomorrow
- $next_day(x)
- $previous_week(x)
- $this_week
- $next_week(x)
- $previous_month(x)
- $this_month
- $next_month(x)
- $previous_quarter(x)
- $this_quarter
- $next_quarter(x)
- $previous_year(x)
- $this_year
- $next_year(x)

For example: if today is the 2022-09-15, $previous_month(3) would be 'June' (or in fact 2022-06-01 00:00:00)

Simply use them as if they where a constant value in the exp part of a filter operation.

{
    "key": "expecteddate",
    "op": ">",
    "exp": "$today"
}

Warning

Dates are stored with up to millisecond resolution, therefor doing = (equals) or != (not equals) filter operations on such fields can give unwanted results. Instead use >, <, >=, <= to create upper- and lower bounds for the date property.

Example: Future deals

Wanted behavior: Show all deals where the expected date is later than the current month, also the deal may not be closed or rejected already.

How to configure:

{
    "op": "AND",
    "exp": [
        {
            "key": "expecteddate",
            "op": ">",
            "exp": "$this_month"
        },
        {
            "op": "!",
            "exp": {
                "key": "dealstatus",
                "op": "IN",
                "exp": [
                    "agreement",
                    "rejection"
                ]
            }
        }
    ]
}

Example: Deals next month

Wanted behavior: All deals that are expected to be closed during next month, exclude those already closed or rejected.

How to configure:

{
    "op": "AND",
    "exp": [
        {
            "key": "expecteddate",
            "op": ">=",
            "exp": "$next_month(1)"
        },
        {
            "key": "expecteddate",
            "op": "<",
            "exp": "$next_month(2)"
        },
        {
            "op": "!",
            "exp": {
                "key": "dealstatus",
                "op": "IN",
                "exp": [
                    "agreement",
                    "rejection"
                ]
            }
        }
    ]
}

Filter using an existing filter

When filtering on a belongsto property it is possible to apply another pre-existing filter on that property. This is done using the IN operator together with the type=filter attribute.

Let's say we have a concept of "active customers", we could filter deals based on if the customer is active or not like this:

{
    "key": "company.buyingstatus",
    "op": "IN",
    "exp": [
        "active"
    ]
}

but if we already have a filter on companies for this (webclient.company.active) we can reuse it:

{
    "key": "company",
    "op": "IN",
    "exp": "webclient.company.active",
    "type": "filter"
}

If, later on, the definition of an "active customer" is changed, our filter would use that new definition immediately.

Example: Deals in Region One

Wanted behavior: All deals where the responsible coworker is included in the coworker filter region_one.

How to configure:

{
    "key": "coworker",
    "op": "IN",
    "exp": "webclient.coworker.region_one",
    "type": "filter"
}