Select filters

When data is to be loaded from a data source, exported to a file, or transferred to a target data source during a data transfer, it is sometimes useful to include only some part of the data, i.e. to include only selected entities or fields, for example only selected tables or columns from a database data source. This process is called filtering the data.

Filters can be defined in a Select Filters window that you see for example when the Calculator is accessed at the Export action properties window, or on the Data transfer properties window. In the Select Filters window you see the source data fields on the left, and operations on the right. Double clicking a field at the top of the window brings this element into the calculation window, making it easy to build up a calculation, although you can also type directly into this window if you wish. Similarly double-clicking an operator adds this to the filter expression, see Operators overview for links to discussion of the operators, or Calculator overview for more on the Calculator. You can access help on each operator from the Select Filters screen by selecting an operator then pressing the Function Help button, or alternatively by holding the mouse over an operation to view the online tooltips.

Make sure you have configured the source data source definition properly by setting the correct type for all the fields (especially the ones you want to apply the filter to!).  This is because the filtering process depends on the type of field being filtered.  

There are limitations to what operators can be applied for certain field types, as shown below:

Note: For the LIKE operator there is no need to include the ‘%’ character as in SQL statements. The list of values specified in the IN operator is assumed to be comma separated. If an operator for a field type is not supported by is in the filter, then the filter will just let everything pass through. Also a row (which may have more than one record) will be let through to the data transfer action if and only if all the records satisfy the filter(s) criteria.

Care must be taken when filtering fields of the DATE and TIME types. Please make sure that the date and time formats are supported or else the parsing will fail - do not expect the system to parse all the obscure date and time formats out there!

Note that record level filtering will definitely slow down the data transfer action, as each record will be inspected and evaluated against all the defined filters.

Related topics include Export action properties, Data transfer properties