Learn about: Creating a Filter Data step

Introduction

The Filter Data is a General tool which is used to create a task step that takes a recordset produced by a database query and based on set rules provides a filtered output recordset for subsequent use by other Task Steps.

This can be achieved by one of two ways:

  • A Repository Filter – The data filter utilises the recordset stored in a repository created by internal memory. When a task runs it compares the results of a new recordset to ones already held by the repository. Processing options are applied and any new data is filtered to create an output recordset.
  • A Visual Basic Filter – A script can be created to execute once for each row in a recordset. If the expression returns ‘true’ for example, the row will be transferred to the output recordset.

Creating a Repository Filter

  1. From the task browser under General tools drag and drop the Filter Data tool into the task planner. This will automatically open the General tab.
  2. Using the drop down menu Input Recordset, select a recordset produced by the database query step.
  3. Provide an appropriate name and description

Capture1

Main Tab

The Main tab displays active filters, once created they will appear in the table. From here you can Add, Remove and Edit a filter by selecting the relevant buttons at the bottom.

Capture2

  1. Select Add to begin creating a filter. The following will be displayed3
  2. Select Repository Filter followed by OK.

Filter Properties – General Tab

The General tab requires a repository from within TaskCentre to be selected by using the Browse button.The chosen repository will have a filter applied against it which will be configured by using processing options in the next tabs along.

4

Name – Provide a name for the filter that will appear in the filter list.

Repository – A repository is selected by using the Browse button. This will load the location of where the repositories are stored within TaskCentre

Description – Provide a filter description

Mapping

The Mapping tab is used to select the recordset columns to be used by the filter which are then mapped to the repository columns. It contains a table which is automatically populated when a repository is selected.

Capture3

Repository Column – Displays the repository column name

Recordset Column – Displays the recordset column name to which the recordset column is mapped. This is initially selected if the column names are the same. However, clicking on the name displays a drop down list of all available columns in the recordset available for selection.

Parameters – Displays the explicit form of the recordset column parameter.

Processing

The Processing tab is used to configure which rows are processed by the filter through a number of selectable option check boxes.

Capture4

Process all rows – All rows in the recordset are processed and no filtering takes place.

Process new rows – All rows in the recordset are compared against those in the repository. Only new rows are included in the output recordset.

Process changed rows – All rows in the recordset are compared against those in the repository. Only rows that have changed are included in the output recordset.

Process unchanged rows – Only rows that have not changed are included in the output recordset.

Process rows last processed more than – All rows in the recordset are compared against those in the repository. This option is used in conjunction with two fields below it which enable a time period to be selected. Only rows that have been previously processed more than the selected time period are included in the output recordset. For example, if a period of 1 minute is selected then only rows that were last processed more than 1 minute previously will be included.

Process rows no longer present – All rows in the recordset are compared against those in the repository. Only rows that appear in the repository but not in the recordset are included in the output recordset.

Process based on a rule – All rows in the recordset are compared against those in the repository. An ‘Edit’ button is provided to open the script editor to enable a rule to be manually scripted. The rule name is then displayed in the field provided and is applied when the recordset is processed.

Purging

ThePurging tabis used to manage the number of rows present in the repository by removing rows that are no longer required to be stored. It is carried out after the processing has been completed and is achieved through a number of selectable option check boxes.

Do not purge – No rows in the repository are deleted.

Purge rows no longer present – All rows in the recordset are compared against those in the repository. Rows that appear in the repository but not in the resordset are removed from the repository.

Purge rows no longer present if processed more than – All rows in the recordset are compared against those in the repository. This option is used in conjunction with two fields below it which enable a time period to be selected. Rows that have been previously processed more than the selected time period are removed from the repository. For example, if a time period of 1 minute is selected then rows that were last processed more than 1 minute previously are removed.

Purge ‘row no longer present’ rows after being processed once as ‘row no longer present’ – All rows in the recordset are compared against those in the repository. Rows that appear in the repository for a second time with a status of ‘row no longer present’ are removed from the repository.

Purge rows no longer present based on a rule – All rows in the recordset are compared against those in the repository. An ‘Edit’ button is provided to open the script editor to enable a rule to be manually scripted. The rule name is then displayed in the field provided and is applied when the repository is purged.

Creating a Visual Basic filter

Using script, a visual basic filter applies specific expressions to a recordset. It can be used as a decision step to route the task output based on data values returned by a query.

Example: A filter is used to define how a customer receives communication, either by email or printed letter. The filter is looking at a value in a field called Restrict_Email. Where the value is set to 0, the customer will receive communication via email. Where it is set to 1, the letter for the customer will be sent to the printer.

  1. Within the Main tab select Add to create a new filter.
  2. Select Visual Basic Filter followed by OK9
  3. Provide a name and description for the filter
    10

Script Tab

Here script can manually be entered or by using drag& drop to select columns of the recordset from the Task Browser and placing them into the script field. The Check Syntax button is used to validate script.

10

To read about tasks which make use of the Filter Data tool see:

Tasks which write key data to a repository – Internal memory