--- title: Filter definitions slug: ref/filter-definitions docTags: createdAt: 2023-12-06T14:02:44.518Z --- 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](https://api.archbee.com/api/optimize/zGnEynQy6cgPdn1J1kZsn/xvN8YPOPh9bvP8rEEYeTO_image.png "Filters") Filters allow non-technical users to create powerful selections on the main entities in Ticketmatic. These selections can be viewed, exported and analysed. :::hint{type="info"} A filter definition always belongs to one of the main entities: - order - contact - event - ticket - payment ::: Filter definitions are managed in the Filter definitions module in the Settings app. Click the button below to go there: :::CtaButton{label="Go to filter definitions" externalHref="https://apps.ticketmatic.com/#!/settings/filters" openInNewTab="true"} ::: *** ## 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: ```sql select 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: ![Large order](https://api.archbee.com/api/optimize/zGnEynQy6cgPdn1J1kZsn/9A45KgPsDnn_j0PfflL99_image.png) When activated, only orders that have a total amount of more than 100€ will be shown. *** ## Definition You use the [public data model](https://www.ticketmatic.com/docs/db) 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. :::hint{type="info"} Following types are available: - Toggle - Checklist - Optionset - Date range - Number range - Text - Tickets bought ::: ### 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. ```sql select id from tm.contact where email is not null ``` Another example is a filter for the contacts that live in Belgium: ```sql select 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: ```sql select 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: ```sql select 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: ![Sales channels](https://api.archbee.com/api/optimize/zGnEynQy6cgPdn1J1kZsn/7cqNZNwR0gZWdIzE6dgme_image.png "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: ![Optionset](https://api.archbee.com/api/optimize/zGnEynQy6cgPdn1J1kZsn/PezIeUPa4qKhmMIjRam4m_image.png "Optionset") ### 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: ```sql select 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: ![Daterange](https://api.archbee.com/api/optimize/zGnEynQy6cgPdn1J1kZsn/udQkV-wMMxk-pDOl0LVuJ_image.png "Daterange") ### 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: ```sql select 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: ```sql select 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: ![Numberrange](https://api.archbee.com/api/optimize/zGnEynQy6cgPdn1J1kZsn/FKLjGcVMyP0xzxh2_yYd7_image.png "Numberrange") ### 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: ```sql select 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. ![Ticketsbought](https://api.archbee.com/api/optimize/zGnEynQy6cgPdn1J1kZsn/ODCU_J4vPcxpu9AdKojDb_image.png "Ticketsbought") 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. :::hint{type="info"} 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: ```sql select id from tm.ticket where tickettypeid in (select id from tm.tickettype where eventid in ({ticket_eventids})) ``` :::hint{type="success"} For more examples, take a look at the filters that are by default available in your account. :::