Filter Condition snippet
Description
The Filter Condition snippet is a useful tool to organize and maintain the required Filter Conditions field.
The Filter Condition snippet would be used in some AlchemyJ Extended Functions such as
To insert a Filter Condition snippet, select the Cell B3 and ensure that there are 4 extra empty rows below Cell B3 to prevent the snippet from overwriting the values below.
Go to AlchemyJ ribbon and click Insert Snippet. Under the dropdown, select Filter Condition (Filter Type 1) or Filter Condition (Filter Type 2) accordingly.
Column definition
Filter Type 1
The range defines the filter condition in the Kendo grid style. Click Insert Snippet\Filter Condition (Filter Type 1) to add the preset required format. The first row defines the header. They are Data Name, Operator 1, Search Criteria 1, And / Or, Operator 2, Search Criteria 2.
| Column Name | Description |
|---|---|
| Data Name (mandatory) | This column is for the data field name. |
| Operator 1 (mandatory) | The following operators are supported:
|
| Search Criteria (mandatory) | This is to state the search criteria value. |
| And / Or (optional) | If the input is AND, it will need to pass Search Criteria 1 and Search Criteria 2. If the input is OR, it will pass if it passes either Search Criteria 1 or Search Criteria 2. |
| Operator 2 (optional) | The following operators are supported:
|
| Search Criteria (optional) | This is to state the search criteria value. |
| Or Group (optional) | This is to group the list of data name filter conditions together. If Or is keyed, the filter conditions will be constructed by Or operation. |
Notes: An error will occur if the range value is not in the format of the corresponding filter type.
For filter_type = 1, if the operator is available but operand is empty, it will be treated as '' (i.e. empty string).
Filter Type 2
The range defines a filter condition similar to the format used in MS Query. Click Insert Snippet\Filter Condition (Filter Type 2) to add the preset required format. Two columns are used to define one filter condition. So the number of columns should always be a multiple of 2. e.g. 2, 4, 6. The first column defines the operator and the second consider defines the value. For example, < 10.
| Column Name | Description |
|---|---|
| Column Name 1 (mandatory) | This column is for the operator. |
| Value 1 (mandatory) | This column is for the value. |
| Column Name 2 (optional) | This column is for the operator. |
| Value 2 (optional) | This column is for the value. |
| Column Name 3 (optional) | This column is for the operator. |
| Value 3 (optional) | This column is for the value. |
Notes: An error will occur if the range value is not in the format of the corresponding filter type.
For filter_type = 2, if the operator is available but operand is empty, it will be treated as '' (i.e. empty string). It will also validate the correctness of Column Header and DataType against database schema.
Example
These examples use table "tb_customer" in database "aj_sample_mysql57". The following records exist in the table.
Click Add Component button in AlchemyJ ribbon, select DB Schema to add the DBSchema worksheet. It will prompt a window to input the table name, input 'tb_customer', and click OK. Columns information will be loaded to DBSchema worksheet. The value defined in the Data Name column could be used in the filter condition as well. Refer to example 3 as an example, Customer Level is used instead of the column name customer_lvl.
Example 1 - Filter Type 1 without Or Group
Below is the example of the ajDBCountRecord function with Filter Type 1.
The snippet will be populated as shown below with no values.
Fill in the Filter Condition details accordingly. It is to filter CUSTOMER_LVL equals to 1 or CUSTOMER_LVL equals to 2. Total 2 records match the filter condition in table tb_customer.
When referencing the Filter Condition table, do remember to include the column headers as part of the cell range as shown in B14 for the formula to work.
Example 2 - Filter Type 1 with Or Group Condition
This example is similar with example 1, one more condition row is defined and Or Group column is specified.
When Or Group is specified, all conditions before Or Group will be treated as Or condition 1. And all conditions after Or Group will be treated as Or condition 2. In this example, It is to filter (CUSTOMER_LVL equals to 1 or CUSTOMER_LVL equals to 2) or (CUSTOMER_NAME starts with 'M' And CUSTOMER_NAME does not contain 'C'). Total 3 records match the filter condition in table tb_customer.
Example 3 - Filter Type 2
Below is the example of the ajDBCountRecord function with Filter Type 2.
The snippet will be populated as shown below with no values.
Fill in the Filter Condition details accordingly. It is to filter the Gender equals to F and Customer Level is greater than 2. Total 2 records match the filter condition in table tb_customer.
When referencing the Filter Condition table, do remember to include the column headers as part of the cell range as shown in Cell B31 for the formula to work.