Export filtering in the Cool Data Mover
By default, the Cool Data Mover exports all records from selected ‘Move’ entities in the package. However, sometimes there is the need to export a subset of records instead of the complete table content. For this, the Cool Data Mover offers the possibility to limit the number of records that will be exported. This is called filtering.
Cool Data Mover documentatie en tips zijn altijd in het Engels
There are three different types of filters: tenant filters, custom filters, and entity filters. Each will be explained in detail in the next sections. It is possible to use any combination of filters (entity, tenant and/or custom) during export.
Tenant filters
A tenant filter ensures that, for multi-tenant entities, only records belonging to a selected tenant are exported.
The possibility to use tenant filters is activated in the package settings by checking the ‘tenant filtering’ checkbox and saving the package.
On the export screen you’ll get the option to use a tenant filter and subsequently select the user provider and tenant (a tenant always belongs to a user provider).
Using a tenant filter only has effect on multi-tenant entities. For multi-tenant entities only the records of the selected tenant will be exported. For normal (non-multi-tenant) entities the tenant filter has no effect, and all records will be exported.
Custom filters
A custom filter is used to initially select required records from a single entity. Those records are exported. On top of that, also related records (records that ‘are pointed to’ by already selected records and records that ‘point to’ already selected records) of entities in the package are exported.
For example: suppose you’ve defined a package including entities Customer, Order, OrderLine, If you define a custom filter on entity Customer (say CustomerNumber=123456), not only that customer record is exported but also the Order records and subsequent OrderLine records for that customer. Effectively all data related to the selected customer is exported.
The possibility to use tenant filters is activated in the package settings by checking the ‘custom filtering’ checkbox and saving the package.
This will bring up an extra ‘Filters’-tab next to the ‘Settings’ tab on which custom filters can be defined. First, you will have to select an entity on which to define the filter and specify a name and optionally a description. After adding the filter, you can start specifying criteria by using AND’s and OR’s on its attributes, operators and selection values as illustrated below.
On the export screen you’ll get the option to use a custom filter and subsequently select the desired filter.
Entity filters
In some situations, the filter mentioned above are not fine-grained enough and a different approach is needed. For this, the Cool Data Mover offer Entity filters. An Entity filter limits the number of exported records by applying the filter to the respective entity.
An Entity filter can be defined for every entity in the package by clicking the filter icon as shown below. The definition of the filter is similarly to the way Custom filter are defined. The filter only applies to the selected entity.
As shown as a warning on the image above, applying entity filtering can have an impact on the subsequent import. The filter above restricts the export of Country record to all country except the Netherlands, but since no entity filter is defined for the Airport entity, all Airport records are exported. However, during the import of those Airport records the foreign key Airport.Country might refer to the Netherlands (NL). In those cases, either the Airport record is skipped (when Airport.Country is mandatory) or the Airport.Country attribute is nullified (when Airport.Country is optional).
Overview of which filters are applied
The overview on the export screen already showed which records are to be exported from which entities. This overview is enhanced to shown which filters will be applied during export.
Depending on which type of filters are applied, the overview shows additional columns to indicate which filters are applied to which entity. When hoovering over the information icons in those columns, details on the filter are shown.
