Skip to content

Knowledge Base

White Paper Libary

Filter Data Tool

What is the Filter Data Tool?

The Filter Data tool extracts a subset of data from a recordset produced by a Database Query (ODBC), Database Query (OLEDB), or Call Stored Procedure (OLEDB) step and provides the data for use in further steps.

Filter Data Tool Features

  • Single or multiple filters
  • Multiple data purging options

Repository Filters

When a task containing an Output step which uses Memory is run, a Repository is automatically created for the task containing the memorised recordset. It contains no data until the task has been run, unless data is then manually entered, but may still be selected for use in the Repository Filter.

When a Filter Data step is added to the task, the Repository can be selected and a Repository Filter created within the step. The Repository Filter is used to compare the new recordset created when the task is run again against the existing memorised recordset in the Repository, and then set rules to filter the new subset of data produced.

Visual Basic Filters

By adding a Filter Data step to a task, a Visual Basic Filter may be created that applies a filter expression against the selected data source to filter the rows.

The script specified in the filter is executed once for each row in the record source. If the expression returns “true”, the row is transferred to the output recordset.

Filter Data Tool: Working with other Tools

The Filter Data tool can interact directly with the following tools, whether by exposing information to it or consuming information from it:

Consuming from Other Tools

The Filter Data tool can directly consume output from the following tools:

IconTool NameTool Category
Database Query ODBC ToolDatabase Query (ODBC)Input and Data Connectors
Database Query OLEDB ToolDatabase Query (OLEDB)Input and Data Connectors
Import Flat File ToolImport Flat FileInput
Convert XML to Recordset ToolConvert XML to RecordsetFormat

Objects Consumed

The following objects, outputted by other task steps, can be consumed by the Filter Data step:

  • Recordset — Tabular data from any BPA Platform tool capable of exposing recordsets
  • Variables (Text) — Textual data is extracted from selected variables

Exposing to Other Tools

IconTool NameTool Category
Call Stored Procedure (OLEDB) ToolCall Stored Procedure (OLEDB)Input, Data Connectors, Output, and Execute
Filter Data ToolConvert Recordset to XMLFormat
Filter Data ToolCreate Workflow JobFormat
Filter Data ToolFormat as Flat FileFormat
Filter Data ToolFormat as HTMLFormat
Filter Data ToolFormat as HTML ProFormat
Filter Data ToolFormat as TextFormat
Run Crystal Report Tool - Crystal Reports AutomationRun Crystal ReportFormat
Run Microsoft Reporting Services ToolRun Microsoft Reporting ServicesFormat
Run Microsoft Word (Merge) ToolRun Microsoft Word (Merge)Format
Filter Data ToolTransform DataFormat
Filter Data ToolSave FileOutput
Filter Data ToolTransfer File (FTP)Output
Call COM Object ToolCall COM ObjectExecute
Run External Program ToolRun External ProgramExecute

Objects Exposed

Filter Data exposes the following objects; these can be consumed by the above tools:

  • RecordSource — Displays the columns included in the recordset being used as the data source
  • Recordset — A subset of the consumed recordset information structured into columns and rows
  • FilterDefinitions — Displays the Filters assigned to the step and the data being used.
  • Step Properties — A number of properties for the step are exposed in the Environment tab of the Task Browser

Step Configuration

To add a new Filter Data step to an existing task, you either:

  • Click and drag the Filter Data icon from the Task Browser to the task Design area.
  • From the task’s Design tab, right-click on empty space and select Add > General > Filter Data.

For a detailed description of how to create new tasks, refer to the product help.

About the General Tab

Filter Data Tool

The General tab is used to enter the following details for the step:

  • Name — Enter a meaningful name for the step
  • Description — If required, enter a description of this step
  • Input Recordset — Displays a list of available recordsets from other task steps that the Filter Data step can use

About the Main Tab

Filter Data Tool

The Main tab allows the creation and application of filters for filtering the recordset received from a Database Query (ODBC), Database Query (OLEDB), or Call Stored Procedure (OLEDB) Step.

The dialog box contains the items listed below.

  • Filter — Displays a list of filters created for the Step
  • Add — Initially opens the Choose Filter Provider dialog box which provides a list of Filter Providers for
    selection:

    • Repository Filter — Opens the Repository Filter dialog box to enable a new Repository Filter to be created
    • Visual Basic Filter — Opens the Visual Basic Filter dialog box to enable a new Visual Basic Filter to be created
  • Remove — Removes the selected filter from the table
  • Edit — Opens the applicable filter dialog box to enable details for the filter selected in the table to be edited

About the Repository Filter Dialog

The Repository Filter dialog box is displayed when the Repository Filter option is selected in the Choose Filter Provider dialog box. It is used to create new filters or edit details for existing ones.

When a task containing an Output step which uses Memory is run, a Repository is automatically created for the task containing the memorised recordset. It contains no data until the task has been run, unless data is then manually entered, but may still be selected for use in the Repository Filter.

When a Filter Data step is added to the task, the Repository can be selected and a Repository Filter created within the step. The Repository Filter is used to compare the new recordset created when the task is run again against the existing memorised recordset in the Repository, and then set rules to filter the new subset of data produced.

About the General Tab

Filter Data Tool

The General tab is used to enter the following details for a Repository Filter:

  • Name — Enter meaningful name for this filter
  • Description — If required, enter a description of this filter
  • Repository — Displays the Repository for this filter. Use Browse to select a new Repository.
    You create new Repositories in the Output step of this task.

Selecting a Repository

The Select Repository dialog is used to select a repository for either the Repository Filter in the Filter Data tool or for use in Internal Memory.

When a task containing an Output step which uses Memory is run, a Repository is automatically created for the task containing the memorised recordset. It contains no data until the task has been run, unless data is then manually entered, but may still be selected for use in the Repository Filter.

When a Filter Data step is added to the task, the Repository can be selected and a Repository Filter created within the step. The Repository Filter is used to compare the new recordset created when the task is run again against the existing memorised recordset in the Repository, and then set rules to filter the new subset of data produced.

Summary View

The Summary View option lists the Repositories directly under each task in a hierarchical tree structure.

Filter Data Tool

Detailed View

In addition to the above, the Detailed View option also allows you to see the task / step / Memory Provider / Repository in the hierarchy.

Filter Data Tool

The tree behaves in a similar manner to a Windows explorer to displays or hide items nested below it as explained below.

  • Folder — Contain tasks and sub-folders
  • This Task — Contain steps. These are the Output steps through which a Repository has been created. Double-clicking on the task displays the items below it.
    • Step (Example) — Contains Memories created for the step. Double-clicking on the step displays the items below it.
  • Memory — Contains Repositories. Double-clicking on the Memory displays the Repositories below it.
  • Repository — Displays the Repository name. Double clicking on the name then selects it for inclusion in the Repository Filter.

About the Mapping Tab

The Mapping tab is used to select the recordset columns used by the filter which are then mapped to the Repository columns.

Filter Data Tool

It contains a table which is automatically populated when a Repository is selected.

  • Repository Column — Displays the Repository column name.
  • Recordset Column — Displays the recordset column name which the Repository column is mapped to. This is initially selected automatically if the column names are the same or displays None if it is not. However clicking on the name displays a drop down list of all available columns in the recordset for selection. Each row for the mapped column value created at task run-time is then processed by the filter and stored in the Repository against the selected Repository column name.
  • Parameter — Displays the parameter for the selected column. The parameter is resolved for each row in the record source and is used to look up existing entries in the Repository when the task is run. This is the mechanism used to detect new rows, if rows have changed, and so on.

About the Processing Tab

Filter Data Tool

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

  • Process all rows — All rows in the recordset are included in the subset of data provided by the step.
  • Process new rows — Only new rows are included in the subset of data provided by the step.
  • Process changed rows — Only changed rows are included in the subset of data provided by the step.
  • Process unchanged rows — Only unchanged rows are included in the subset of data provided by the step.
  • Process rows last processed more than — This option is used in conjunction with two fields below it which enable a time period to be selected. Any rows that have been previously processed more than the selected time period are included in the subset of data provided by the step.
  • Process rows no longer present — Any rows that are no longer present are included in the subset of data provided by the step.
  • 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 a Rule Script dialog box to enable a rule to be manually scripted. The actual rule logical expression itself is then displayed in the field provided and is applied when the recordset is processed.

About the Purging Tab

The Purging tab is used to manage the number of rows contained in the Repository and is carried out after all processing has been completed.

Filter Data Tool

This is achieved through a number of selectable option check boxes as follows:

  • 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 recordset 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. Rows that are no longer present which were processed more than the selected time period ago are removed from the Repository. For example, if a time period of 1 Minute is selected then rows that are no longer present that were last processed more than 1 minute previously are removed.
  • Purge row no longer present rows after being processed 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. Any matching the selected rule are removed.

About the Visual Basic Filter

By adding a Filter Data step to a task, a Visual Basic Filter may be created that applies a filter expression against the selected data source to filter the rows.

The script specified in the filter is executed once for each row in the record source. If the expression returns “true”, the row is transferred to the output recordset.

About the General Tab

Filter Data Tool

The General tab is used to enter the following details for a Visual Basic Filter:

  • Name — Enter a meaningful name for this filter
  • Description — If required, enter a description of the filter
About the Script Tab

Filter Data Tool

The Script tab is used to enter the Script for the filter through the following:

  • Script — Variables, properties, columns, formulas, functions and scripting elements may be dragged and dropped into the created script from the Task Browser dialog
  • Check Syntax — Checks the script syntax and displays the result in a pop-up dialog

About the Options Tab

The Options tab allows you to define how errors in this step are handled at task runtime.

Filter Data Tool

If the step is aborted, you can choose to Continue processing onto the next step in the task, or terminate the whole task immediately (Abort Task).

White Paper - Filter Data

White Paper - Filter Data

The Filter Data white paper introduces the tool.