In some situations you might need to define complex filters that cannot be set using the default XPath filters. For those purposes, Bizagi allows defining Advanced filters through scripting expressions.
Advanced filters can be defined for the following controls:
•Combo
•Radio
•List
•Search
•Suggest
•Tables
Expressions used to define these filters use database sentences, for this reason, these should always return a string type value.
We strongly recommend returning a variable holding the string with the filter. However you can also return a sentence between double quotes like:
"(FrequentSupplier=true AND Rating > 0,85)"
When comparing Boolean values recall to use true or false in lowercase and without quotes: i.e BooleanAttribute = true; String values have to be set using simple quotes, avoid simple quotes for other values. In the following examples we will use SQL sentences (AND, OR, LIKE, <>, IN, etc).
|
•Filters within filters are not supported. •When using an expression, its name will be displayed on the left. If the name is too long to fit in the box, hover with the mouse, a tooltip with the complete name will display..
|
Expression filters examples
Defining filters combining AND/OR
When you need to define filters whose conditions are linked by a single operator (AND/OR) you can make use of the Boolean expression editor. However, when you need these conditions to be linked by combining AND/OR operators, you should use Expression filters.
Let us consider a situation where you need to evaluate conditions linked by AND/OR operators simultaneously in order to define which values will be displayed in a drop-down list.
In a Purchase Request Process, the purchase manager has to choose a supplier for the requested products from a combo control. The suppliers that can be chosen (the values displayed within the combo) must comply with the following requirements:
- If supplier is a frequent supplier then its rating must be higher than 85%.
- If the supplier is a not a frequent supplier then its rating must be higher than 95%.
Note these requirements can be expressed as two different conditions linked by an OR operator. As well, each condition is composed by two sub-conditions linked by an AND operator.
To identify if a supplier is frequent as well as its rating the Supplier Master entity has, among others, the following attributes:
In order to define the proposed filter over the Selected Supplier combo, follow the next steps:
1. Open the Forms Designer from the Process Wizard, where the control to be filtered is found.
Locate the Supplier control and access the Advanced tab. Click the expression icon (
) within the Filter field.
Click New to create a new expression.
2. A new window will display with the scripting expressions editor. Create an Expression module.
3. Declare the following variables:
•Condition1: stores the string related to the first condition (frequent supplier and rating > 0,85).
•Condition2: stores the string related to the second condition (not frequent supplier and rating > 0,95).
•Filterstring: is the string of the filter to be set.
4. Define the string for each condition. Note we are using AND sentences to link the sub-conditions of each one.
Each condition must be written within double quotes.
Condition1="(FrequentSupplier=true AND Rating > 0,85)"
Condition2="(FrequentSupplier=false AND Rating > 0,95)"
5. Build the Filter string. Note it links the two conditions by using an OR operator.
Operators must be written within double quotes.
Filterstring= Condition1 + " OR " + Condition2;
6. Finally return the filter as shown.
Return the filter string
Filterstring;
Click OK to save the changes.
Filtering a WFUSER related combo by Roles
Advanced filters are also useful to filter values related to WFUser attributes, according to user properties as Roles, Positions or Skills.
Suppose in a Help Desk Process the support person who receives a Ticket must assign it to one of the qualified technicians. To do so, he/she selects a technician from a drop-down list (combo control) called Assign to. This control should only display the users who hold a Role of Support Technician.
To filter the values displayed in this control, we need to establish an Advanced filter that displays the qualified technicians from the WFUser Entity, based on their Role.
This filter has to follow the next structure:
idUser in (idTech1,idTech2,idTech3....idTechN)
Where idUser is the foreign key of the WFUser entity in the database, and in is a SQL sentence.
Therefore, the expression used to define this filter has to obtain a string containing the basic sentence (idUser in) followed by the identifiers of the users that meet the criteria, separated by comas and within parenthesis (idTech1,idTech2,idTech3....idTechN).
To do so, follow the next steps:
1. Open the Forms Designer from the Process Wizard, where the control to be filtered is found.
In the Advanced tab click the expression icon () within the Filter field.
Click New to create a new expression.
2. A new window will display with the scripting expressions editor. Create Expression modules and one For module as shown below.
•In the first Expression module declare the variables and obtain the list of users with the specified Role.
•Use the For module to iterate over each record of the list.
•In the second Expression module, obtain the string of the identifiers of the users that meet the criteria.
•In the final Expression module, build the filter string and return it.
3. In the first Expression module declare the following variables.
•UsersArray: stores the list of the users that belong to the specified Role.
•IterationIndex: is the counter for the FOR cycle.
•CurrentUserId: temporally stores a specific record of the users list.
•UsersIds: is the string that contains the identifiers of the users belonging the specific role, in the required format.
•Filterstring: is the string of the filter to be set.
4. Obtain the list of users with the desired Role (In this case, the Support Technician.) using the Get users in Role Function found in the Users category.
//Obtain the list of users with the specified Role and store the collection in the variable
UsersArray=CHelper.getUsersForRole("SupportTechnician");
5. Iterate the list using the For module. The cycle starts at zero (0) and ends at the last index of the list.
The sentence UsersArray.Count will return the total number of users found.
Then, the loop will be executed as long as the Index of the collection is less than the total number of users in the list.
6. Include the following code in the second Expression module to obtain the string of the user's identifiers, in the specific format.
Note this is only a way to do it.
//Obtain the user identifier of the current iterated record in the array
CurrentUserid=UsersArray(IterationIndex);
//Build the string used to filter the combo
//If the current user is not the first user
if (UsersIds!= null )
{
//If the current user is not the last user
if (IterationIndex!=(UsersArray.Count-1))
{
UsersIds=UsersIds+CurrentUserid+",";
}
//If the current user is the last user
else
{
UsersIds=UsersIds+CurrentUserid+")";
}
}
//If the current user is the first user
if (UsersIds== null )
{
UsersIds="("+CurrentUserid+",";
}
7. In the last expression module build the Filter string and return it as shown
// Concatenate the filter sentence with the identifiers of users that meet the criteria
Filterstring="idUser in "+UsersIds;
// Return the filter string
Filterstring;
Click OK to save the changes.
In a Purchase Request Process the Supervisor of the Requester must approve a purchase of a product when one of the following conditions is met:
•A product has a Unit cost greater than $1.000 and a Quantity greater than 100 units
OR
•The Total price of a product is greater than $5.000 and its Quantity is less than 100 units.
The Supervisor must only see the products he must approve, thus a filtered table is necessary.
A Table is configured on the Form and an expression filter is necessary to fulfill the business requirement.
1. In the Work Portal, the Work Portal displays the Table with the complete list of products, with no filtering.
We will now create a filter to display only the relevant information.
2. In the Forms designer, we define an expression filter for the Table control.
.
3. Click on New to create a new filter based on the result of an expression. Add a new expression.
4. Once the Expression element is opened. Add the following code in order to filter the table.
"(UnitPrice > 1000 AND Quantity > 100) OR (TotalPrice > 5000 AND Quantity < 100)"
Note that the last thing on the expression is what it returns, and it must be written within double quotes.
5. Save the expression and the Form.
6. When we update the Work Portal we can see that the Table now displays the two relevant products, that meet the conditions of the filter.
Last Updated 2/7/2022 4:44:47 PM