Setting up filter definitions

A filter definition is used in selections to filter a list of items on certain conditions. For example, you can filter a list of contacts on age, or a list of events on location. A filter definition can be created for orders, contacts, events, tickets or payments and is made available in the Ticketmatic apps.

Filters allow non-technical users to create powerful selections on the main entities in Ticketmatic. These selections can be viewed, exported and analysed.

A filter definition always belongs to one of the main entities:

Filter definitions are managed in the Filter definitions module in the Settings app. Click the button below to go there:

Getting started: create a new filter definition

• Click on the Add button on the Order section to create a new filter definition for Orders.
• Select Toggle as type, and use ‘Large order’ as Name

The definition contains the actual sql filter to be used. For this example, type:

1select id from tm.order where totalamount>100

This will create a filter that, when activated, will only select orders that have a totalamount larger than 100€.

Press Save. The filter definition is now available in the Orders app:

When activated, only orders that have a total amount of more than 100€ will be shown.

Definition

You use the public data model to define filter definitions. A filter definition is actually an sql statement that must return a list of ids that conform to the filter.

Each filter definition has a type. Depending on the type, the user will need to provide certain parameters when activating the filter. Following types are available:

Toggle filter

A toggle filter is a simple on/off filter without parameters.

As an example, let’s define a toggle filter that filters the contacts on whether the e-mail address is filled in.

1select id from tm.contact where email is not null

Another example is a filter for the contacts that live in Belgium:

1select contactid from tm.contactaddress where countrycode='BE'

Checklist filter

A checklist filter is a filter where the user selects 1 or more items out of a selected list. The list the user selects from is itself the result of an sql query.

As an example, let’s create a filter to filter orders on sales channel.

Use as definition:

1select id from tm.order where saleschannelid in ({0})

We select the ids of all orders that belong to 1 or more sales channels. The {0} is a parameter that will be replaced by the actual list of ids that the user selects. These ids are retrieved from the Options SQL clause. Enter there:

1select id,name{0} from tm.saleschannel where isarchived is distinct from true order by name{0}

This statement will return the options the user can select from. It should return 2 columns: an id and a caption. In this query, the {0} is replaced by the current language code.

When using this filter in the Orders app, you will see that the user can select one or more of the sales channels:

Optionset filter

An optionset filter is a filter where the user selects exactly 1 item out of a selected list. The list where the user selects from is itself the result of an sql query.

It is very similar to a checklist filter. The only difference is that the user can only select a single item when using the filter:

Daterange filter

A daterange filter is a filter where the user selects a date range (= start and end date)

For example to create a filter for orders that are created between a start and end date, use:

1select id from tm.order where date_trunc('day',createdts) BETWEEN {0} AND {1}

The parameters {0} and {1} and replaced by the start and end date of the range.

This is how the user uses a daterange filter:

Numberrange filter

A numberrange filter is a filter where the user selects a number range (= start and end number)

For example to create a filter for contacts that made a number of orders in a certain range:

1select id from tm.contact where (select count(*) from tm.order where contactid=contact.id) BETWEEN {0} and {1}

The parameters {0} and {1} and replaced by the start and end number of the range.

Another example to create a filter for contacts that have their age in a certain range:

1select id from tm.contact where age(contact.birthdate)>={0}*'1 years'::interval and age(contact.birthdate)<={1}*'1 years'::interval

The parameters {0} and {1} and replaced by the start and end date of the range.

This is how the user uses a numberrange filter:

Text filter

A text filter is a filter where the user inputs a string.

For example, to create a filter for contacts with e-mail addres starting with a string:

1select id from tm.contact where email like '{0}%'

Ticketsbought filter

The ticketsbought filter is an advanced filter specifically for contacts. It allows filtering on all kind of criteria that are related to the tickets bought history.

The user can for example use this filter to select all contacts that bought at least 4 tickets through the Web saleschannels for a certain list of events.

No configuration is necessary for this filter.

Ticket filters

Filters on tickets are used in the Events app, and are always used in the context of a specific event. For reasons of performance, a placeholder {ticket_eventids} should be used to limit the results to the tickets for these events. An example filter would be:

1select id from tm.ticket where tickettypeid in (select id from tm.tickettype where eventid in ({ticket_eventids}))

For more examples, take a look at the filters that are by default available in your account.