Using filters to show only subsets of data

Since version 1.2.x, ConfiForms plugin supports 'Lucene like' syntax for filtering issues (Not everything is supported, but the basics and the syntax, as prefixing the field to search on, using wildcards). You can learn more about Lucene here. In the tutorial below we show some examples of how you can use this powerful functionality.

Filtering is supported by 'ConfiForms TableView' and 'ConfiForms ListView' macros, as well as by REST API (See ConfiForms REST API)

Filtering engine is quite powerful and understands 'AND', 'OR', '<', '>' and grouping of conditions. And is case sensitive for AND and OR operators. Also, as of version 1.4.1 of ConfiForms plugin, a NOT notation was added. The syntax is as follows: !fieldname:*something*, which interprets to find records that do not have 'something' in their field named 'fieldname'

Also it has a number of reserved words and operators to ease the comparison of numbers and dates. As well as to search and filter empty values.

[empty] To match empty values for particular field. Example: field1:[empty] - will match records where field1 is empty (does not have a value, but the form defines this field)
[now] Current time and date, useful with '<' and '>' for comparing with dates stored. Example: someDateField:<[now] - will match records where field 'someDateField' has value which is in the past compared to now (current time)
[today] Same as [now], but without time
[tomorrow] To compare against tomorrow's date
[yesterday] To compare against yesterday's date.
[dateyyyyMMdd] To compare against given date in the format: yyyyMMdd, example: [date20150131] to give a date as Jan 31 2015
'<' and '>' (and '<=' and '>=') Can be used together with date and datetime fields, as well as to compare values for numeric fields stored
! To reverse the filter condition. Example: !field1:[empty] - will find records that have 'field1' field filled

Again, this tutorial will be based on 'Creating a simple form'. But we have added one more field (nrOfPages), that stores numbers.. and it is for storing number of pages in a book.

Let's enter some data:

  • bookTitle: Programming in Objective-C
    bookAuthor: Stephen G. Kochan
    nrOfPages: 100
    bookISBN: 0321967607

  • bookTitle: Android Programming: The Big Nerd Ranch Guide
    bookAuthor: Bill Phillips
    nrOfPages: 200
    bookISBN: 0321804333

  • bookTitle: Windows Store App Development: C# and XAML
    bookAuthor: Pete Brown
    nrOfPages: 150
    bookISBN: 1617290947

  • bookTitle: TBD
    bookAuthor: Sam White
    nrOfPages: 10
    bookISBN:

Here are some examples on how the data could be filtered (Either by setting the 'Filter to apply on entries dataset' macro parameter for 'ConfiForms TableView'/'ConfiForms ListView' macros or by giving q parameter to filtering REST API)

Show books with word 'programming' bookTitle:*Programming*
Show books with words starting with 'Progr' bookTitle:Progr*
Filter by ISBN bookISBN:1617290947
Show Windows and Android books bookTitle:*Android* OR bookTitle:*Windows*
Show books by authors, who's name or surname starts with 'P' bookAuthor:P*
Show books having either 'Android' OR 'App' in their title and book author must have a name which ends with 'Phillips' (bookTitle:*Android* OR bookTitle:*App*) AND bookAuthor:*Phillips
Find a book without bookISBN bookISBN:[empty]
Find books where author name has 'w' in it's name and number of pages is more than 100 and less than 200 bookAuthor:*w* AND (nrOfPages:>100 AND nrOfPages:<200)
Free text search to look for 'en' in any field stored *en*

How to debug your filters

As ConfiForms plugin supports quite many field types it is not obvious from the first sight how you apply filters on that fields.

The easiest way to understand how the data is stored and therefore can be filtered is to use JSON export (available in admin UI for each form you own). Let's look at the example.

Our ConfiForms form consist of the following fields:

  • Text field
  • Checkbox field
  • File field
  • Checkbox group field
  • WYSIWYG field
  • Date field

When you export your data as JSON you will see the following

  {
    "list": {
      "entry": {
        "ownedByPicture": "/images/icons/profilepics/default.png",
        "recordId": 1,
        "id": "2752c7b2-3a8c-41ce-915f-adf2f69df1bb",
        "ownedByName": "admin",
        "createdByName": "admin",
        "createdBy": "admin",
        "created": 1416810575394,
        "ownedBy": "admin",
        "createdByPicture": "/images/icons/profilepics/default.png",
        "deleted": 0,
        "fields": {
          "checkBoxGroupField": "1:one;2:two",
          "wysiwygField": "editor content",
          "textField": "This is a value for text field",
          "class": "fields",
          "fileField": "spring-aop-4.1.1.RELEASE-javadoc.jar:spring-aop-4.1.1.RELEASE-javadoc.jar",
          "checkBoxField": true,
          "dateField": "Nov 11, 2014"
        }
      }
    }
  }

The fastest way to debug the filter is to use REST API call, for filtering. You can do it directly in your browser and here is how the URL should be constructed:

  http(s)://CONFLUENCE_SERVER/ajax/confiforms/rest/filter.action?pageId=PAGE_ID&f=FORM_NAME&q=QUERY

More about REST API in ConfiForms here

In our case pageId for the page where the form is configured is 1409026 and form's name is filtersForm
The URL will look like this.

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=

This request will return the whole dataset as there is no filters asked ('q' parameter is empty)

Filtering by Text (or WYSISWYG) field

This is the most straightforward and simple syntax. You can apply filter on all fields or on the specific field. You can use wildcards ('*'), both as prefix and as a suffix to your search term.

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=*

This request will return the whole dataset as there is no filters asked ('q' parameter is a wildcard)

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=wysiwygField:editor*
  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=wysiwygField:editor content
  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=editor*
  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=editor content

All requests find a match, first 2 are applied only on the field ('wysiwygField'), last 2 requests search through the whole dataset (all fields)

Filtering by File field

For 'file' field types the filter is applied on the filename. Wildcards are supported

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=fileField:spring-aop-4.1.1*
Filtering by Checkbox field

For 'checkbox' field types the value is saved as boolean. Therefore for filtering you should use true (for checked) or false (for not checked)

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=checkBoxField:true
Filtering by Date field

For 'date' field types (and 'date/time' fields) the value is saved as a timestamp, but ConfiForms has some helpful functions to help you with. See more detailed here

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=dateField:<[now]
Filtering by Checkbox group field

For 'checkbox group' field types, there is a small trick, as you should reference each value separately, see the example below

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=checkBoxGroupField:one AND checkBoxGroupField:two
  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=checkBoxGroupField:one

First request will filter records which have 'checkBoxGroupField' with both values, 'one' and 'two' are selected.
Second request filters records where filed 'checkBoxGroupField' at least has 'one' value selected

Filtering by Metadata fields

You can always filter by metadata fields. Same rules as for 'text' field types. And of course to combine various conditions (using AND/OR)

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=id:2752c7b2-3a8c-41ce-915f-adf2f69df1bb
  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=2752c7b2-3a8c-41ce-915f-adf2f69df1bb
  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=ownedByName:admin

What is important is that you can filter by 'created' meta field the same way as you do for date and date/time fields

  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=created:[today]
  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=created:<[today]
  http://conftest:1990/confluence/ajax/confiforms/rest/filter.action?pageId=1409026&f=filtersForm&q=created:<[yesterday]

Filtering tutorial (obsolete)

Tutorial for version 1.1 is here. But we strongly advice you to upgrade your ConfiForms to latest version to take advantage of much more powerful 'Lucene like' filtering.