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 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 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 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 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 when activated, only orders that have a total amount of more than 100€ will be shown definition you use the 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 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 select id from tm contact where email is not null another example is a filter for the contacts that live in belgium 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 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 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 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 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 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 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 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 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 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 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 select 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