Overview
An important part of using the native Excel connector is to read filtered information from the Excel file. When you want tu use the native Excel filtering options use this action.
Action
The Filter Range action lets you read a range of cells, filter it, and store it as a collection in your Data Model. You can identify this action by its purple border on the left panel.
Give this action a meaningful name and select the sheet where the action will take place.
With a Filter range action you can read multiple columns in a single range, and filter each of them. You have to define an output for each column of the range. Each column on the range is associated with an attribute of your collection. To set up this action you must configure the range and the column definition.
In the Range definition panel you can configure your range either by using coordinates or using a named range.
To identify a range with its coordinates select the option Coordinate range and provide the coordinate of the top left cell of the range and of the bottom right cell.
To identify a range with its name select the option Range name from the dropdown list and select the name of your cell from the available options. The name must already exist in your Excel file to be shown in the options.
The panel automatically calculates the number of columns you must configure in both cases.
After configuring a range the Headers? option automatically appears.
If you check Yes, the Row field appears. Type the row with the headers, and Bizagi will automatically obtain the name of the column.
If the header feature is selected your column name will have the following format [ColumnLetter]:[Header] other wise the column name will be simply the column letter.
Afterward configure the columns in the Filter definition panel. Bizagi automatically creates a definition for each column in your range.
For each column definition you must provide the expected type. Its possibles values are: String, Boolean, Byte, Date, Decimal, Double and Integer.
Additionally you can set a single filter for each of the columns.
The available filters are.
•No filter: no filter is applied
•Is: this filter matches exactly an already existing value in your excel file.
•Like: this filter looks for the cells that contain a designated text.
•Not like: this filter looks for the cells that don't contain a designated text.
•Begins with: this filter looks for the cells that start with a designated text.
•Ends with: this filter looks for the cells that end with a designated text.
•>=: this filter looks for cells that have a greater or equal value to a reference number
•<=: this filter looks for cells that have a lower or equal value to a reference number
•>: this filter looks for cells that have a greater value than a reference number
•<: this filter looks for cells that have a lower value than a reference number
•Blanks: this filter looks for cells that have blank values.
•Non Blanks: this filter looks for cells that don't have blank values.
For each filter provide a default value. Filters of type Like, Not like, Begins with and Ends with can be overwritten when mapping the connector in Advance mapping mode.
|
If your range has empty rows the connector creates empty records in your collection in Bizagi. |
Advance Mapping
When you map the inputs of the connector using the advance view you can map the following properties for the Read range action:
•Sheetname: with this field you can select your sheet where the action is executed dynamically. Use the name of the Sheet as it is shown in your Excel file.
•Range: with this field, you can select the range from where you want to read the information. If the cell is configured to receive a coordinate, you must provide a coordinate. In the same way, if you configure it to receive a name, you must provide an available range name.
•list-Filters: a list of all the filters that you can overwrite their value. You will see the column's name associated with the filter.
Configuration example
In this example we will use the following Excel file:
In this simple Excel file, we have a list of names in the A column, their respective ID in column B, their subscription type in the C column, and the date in which they acquired the subscription in column D. We want to obtain only Gold subscribers whose name starts with an A.
On this example, the connector will Read information from the range A2:D13 when executed.
In the Filter definition add the following and filters:
•A: Client: begins with and default value A.
•C: Subscription: is and select the Gold option.
With this your connector is configured.
Last Updated 2/25/2024 10:41:25 PM