Documentation

Creatio development guide
PDF
This documentation is valid for Creatio version 7.11.0. We recommend using the newest version of Creatio documentation.

DataService. Data filtering

Glossary Item Box

General information

During the execution of DataService operations, it is often necessary to filter data. For example, when reading section records, you need to fetch only those records that meet certain criteria. Bpm'online provides the Filters class to form these criteria .

The Filters class

The Filters class is defined in the Terrasoft.Nui.ServiceModel.DataContract namespace of the Terrasoft.Nui.ServiceModel.dll class library. For simplicity, the hierarchical structure of the Filters data filter is conveniently presented as a JSON format object:

"Filters":{
    "RootSchemaName":["Root schema name"],
    "FilterType":[Filter type],
    "ComparisonType":[Comparison type],
    "LogicalOperation":[Logical operation],
    "IsNull":[Completeness checkbox],
    "IsEnabled":[Activation checkbox],
    "IsNot":[Negation operator checkbox],
    "SubFilters":[Subquery filters],
    "Items":[Filter group collection],
    "LeftExpression":[Expression to be checked],
    "RightExpression":[Filtration expression],
    "RightExpressions":[Filtration expressions array],
    "RightLessExpression":[Initial filtration range expression],
    "RightGreaterExpression":[Final filtration range expression],
    "TrimDateTimeParameterToDate":[Cutting time for date/time parameters checkbox],
    "Key":["Filter key in the filter collection"],
    "IsAggregative":[Aggregating filter checkbox],
    "LeftExpressionCaption":["Expression title to be checked"],
    "ReferenceSchemaName":["Reference schema name"]
}

The basic properties of the Filters class and their possible values are presented in table 1.

Table 1. Filters class properties.

Property Type Description
RootSchemaName string A string containing the name of the root object schema of the added record.
FilterType FilterType

Filter type. Set by the FilterType enumeration value of the Terrasoft.Nui.ServiceModel.DataContract namespace.

FilterType enumeration values:

None 0 Filter type not defined.
CompareFilter 1 Comparison filter. Used to compare expression results.
IsNullFilter 2 The filter that defines whether an expression is empty.
Between 3 The filter that defines whether an expression is one of the expressions.
InFilter 4 The filter that defines whether an expression equals one of the expressions.
Exists 5 Existence filter.
FilterGroup 6 Filter group. Filter groups can be nested in one another, i.e., the collection itself can be an element of another collection.
ComparisonType FilterComparisonType

Comparison operation type. Set by the FilterComparisonType enumeration value of the Terrasoft.Core.Entities namespace.
LogicalOperation LogicalOperationStrict Logical operation. This type does not allow the None value specified in the LogicalOperationStrict enumeration of the Terrasoft.Common namespace.
IsNull bool Expression completion checkbox.
IsEnabled bool Checkbox that defines whether the filter is active and will be taken into account when building a request.
IsNot bool Specifies whether to use the negation logical operator.
SubFilters Filters Subrequest filters. Cannot contain filters with other subrequests.
Items Dictionary<string, Filter> Collection containing a filter group.
LeftExpression BaseExpression The expression in the left part of the comparison, i.e. the expression to be tested. The BaseExpression class is defined in the Terrasoft.Nui.ServiceModel.DataContract namespace.
RightExpression BaseExpression The filter expression that will be compared to the expression contained in the LeftExpression property.
RightExpressions BaseExpression[ ] The expression array that will be compared to the expression contained in the LeftExpression property.
RightLessExpression BaseExpression Initial filtration range expression.
RightGreaterExpression BaseExpression Final filtration range expression.
TrimDateTimeParameterToDate bool Checkbox indicating whether to cut time from the date-time parameters.
Key string Filter key in the collection of Items filters.
IsAggregative bool Aggregating filter checkbox.
LeftExpressionCaption string Left comparison part title.
ReferenceSchemaName string The object schema name referenced by the left part of the filter if the column type is lookup.

 

The BaseExpression class is the base expression class. It is defined in the Terrasoft.Nui.ServiceModel.DataContract namespace of the Terrasoft.Nui.ServiceModel library. The properties of this class instance are populated depending on the ExpressionType property that specifies the expression type. A complete list of the BaseExpression class properties is given in table. 2.

Table 2. BaseExpression class main properties

Property Type Description
ExpressionType EntitySchemaQueryExpressionType

The expression type that defines the value that will be contained in the added column. Set by the EntitySchemaQueryExpressionType enumeration of the Terrasoft.Core.Entities namespace defined in the Terrasoft.Core class library. For the InsertQuery the EntitySchemaQueryExpressionType.Parameter value is set.

The EntitySchemaQueryExpressionType enumeration values:

SchemaColumn 0 Schema column.
Function 1 Function
Parameter 2 Parameter
SubQuery 3 Subquery
ArithmeticOperation 4 Arithmetic operation
ColumnPath string The path to a column relative to the root schema. The rules for building the paths can be found in the "The use of EntitySchemaQuery for creation of queries in database" article.
Parameter Parameter

Defines the value that will be contained in the added column. Its Parameter type is defined in the Terrasoft.Nui.ServiceModel.DataContract namespace.
FunctionType FunctionType

Function type. Set by the value from the FuctionType enumeration defined in the Terrasoft.Nui.ServiceModel.DataContract namespace.

FunctionType enumeration values:

None 0 Not defined
Macros 1 Macro
Aggregation 2 Aggregating function
DatePart 3 Date part
Length 4 Length
MacrosType EntitySchemaQueryMacrosType

Macro type. Set by the value from the EntitySchemaQueryMacrosType enumeration defined in the Terrasoft.Core.Entities namespace.
FunctionArgument BaseExpression Function argument. Takes the value if the function is defined with a parameter. The BaseExpression class is defined in the Terrasoft.Nui.ServiceModel.DataContract namespace and is the ancestor of the ColumnExpresion class and has the same set of properties.
DatePartType DatePart

Date part. Set by the value from the DatePart enumeration defined in the Terrasoft.Nui.ServiceModel.DataContract namespace.

DatePart enumeration values:

None 0 Not defined
Day 1 Day
Week 2 Week
Month 3 Month
Year 4 Year
Weekday 5 Day of the week
Hour 6 Hour
HourMinute 7 Minute

AggregationType

 AggregationType Aggregating function type. Sets the value of AggregationType enumeration defined in the namespace Terrasoft.Common defined in the class library Terrasoft.Common
AggregationEvalType AggregationEvalType Aggregating function Set by the value from the AggregationEvalType enumeration defined in the Terrasoft.Core.DB namespace defined in the Terrasoft.Core class library.
SubFilters Filters Subquery filter collection. Its Filter type is defined in the Terrasoft.Nui.ServiceModel.DataContract namespace.

Learn more about filters in the "EntitySchemaQuery filters handling" article. Next, there is an example of using filters in requests to the DataService service from a third-party application.

Using filters in a third-party application example

Case description

You need to create a console application that will read the following data from the [Contact] section using the DataService service:

  • Id
  • Full name
  • Number of activities is an aggregating column that shows the number of activities of this contact.

It is necessary to filter the data so that only those contacts whose number of activities is in the range of 1 to 3, and the [Full name] column value starting with "H" are read.

Case realization

You can download the full source code of this example here.

Case implementation algorithm

1. Сreate and configure a C# console application project that reads records

To perform this step, you must perform the example of reading records in a third-party application, described in the "DataService. Reading records" article.

The result of the query class implementation instance to read the records with the columns in an abbreviated form:

// Query class instance.
var selectQuery = new SelectQuery ()
{
    // Root schema name.
    RootSchemaName = "Contact",
    // Adding columns to query.
    Columns = new SelectQueryColumns ()
    {
        // Column collection.
        Items = new Dictionary <string, SelectQueryColumn> ()
        {
            // Column [Full name].
            {
                // Key.
                "Name",
                // Value.
                new SelectQueryColumn ()
                {
                    // An expression that specifies the column type.
                    Expression = new ColumnExpression ()
                    {
                        // Expression type - schema column.
                        ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                        // Path to the column.
                        ColumnPath = "Name"
                    }
                }
            }
            // Column [Number of activities].
            {
                "ActivitiesCount",
                new SelectQueryColumn ()
                {
                    Expression = new ColumnExpression ()
                    {
                        // Expression - subquery.
                        ExpressionType = EntitySchemaQueryExpressionType.SubQuery,
                        // Path to the column relative to the root schema.
                        ColumnPath = "[Activity: Contact] .Id",
                        // Function type - aggregating.
                        FunctionType = FunctionType.Aggregation,
                        // Aggregation type - number.
                        AggregationType = AggregationType.Count
                    }
                }
            }
        }
    }
};

2.  Add filter implementation

In order to filter data, you must create an instance of the Filters collection class instance, fill in the necessary properties, and then pass the link to this instance to the Filters property of the query class instance that you created in the previous step.

Filter collection class implementation example:

// Query filters.
var selectFilters = new Filters ()
{
    // Filter Type - group.
    FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,
    // Filter collection.
    Items = new Dictionary <string, Filter>
    {
// Filter Implementation.
    }
};
// Adding filter to query.
selectQuery.Filters = selectFilters;
// Query class instance serialization to read data from the JSON string.
var json = new JavaScriptSerializer () Serialize (selectQuery).;

The Items property must contain the key-value type collection. The key is a string containing the filter name, and the value is an instance of the Filter class that contains a direct implementation of the filter.

To implement a filter that selects only those contacts that have a number of activities within a range of 1 to 3, you must add the following instance to the collection of filters:

// Filtration by activity.
{
    // Key.
    "FilterActivities",
    // Value.
    new Filter
    {
        // Filter type - range filter.
        FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.Between,
        // Comparison type - range.
        ComparisonType = FilterComparisonType.Between,
        // An expression to be tested.
        LeftExpression = new BaseExpression ()
        {
            // Expression type - subquery.
            ExpressionType = EntitySchemaQueryExpressionType.SubQuery,
            // Path to the column relative to the root schema.
            ColumnPath = "[Activity: Contact] .Id",
            // Function type - aggregating.
            FunctionType = FunctionType.Aggregation,
            // Aggregation type - number.
            AggregationType = AggregationType.Count
        }
        // Filter range final expression.
        RightGreaterExpression = new BaseExpression ()
        {
            // Expression type - parameter.
            ExpressionType = EntitySchemaQueryExpressionType.Parameter,
            // Expression parameter.
            Parameter = new Parameter ()
            {
                // Parameter data type - integer.
                DataValueType = DataValueType.Integer,
                // Parameter value.
                Value = 3
            }
        }
        // Filter range initial expression.
        RightLessExpression = new BaseExpression ()
        {
            ExpressionType = EntitySchemaQueryExpressionType.Parameter,
            Parameter = new Parameter ()
            {
                DataValueType = DataValueType.Integer,
                Value = 1
            }
        }
    }
}

Add the following instance to the filter collection to filter contact records where the [Full name] column value begins with "H":

// Filtering by name.
{
    // Key.
    "FilterName",
    // Value.
    new Filter
    {
        // Filter type - comparison filter.
        FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
        // Comparison type - starts with an expression.
        ComparisonType = FilterComparisonType.StartWith,
        // Expression to be tested.
        LeftExpression = new BaseExpression ()
        {
            // Expression type - schema column.
            ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
            // Path to the column.
            ColumnPath = "Name"
        }
        // Filtration expression.
        RightExpression = new BaseExpression ()
        {
            // Expression type - parameter.
            ExpressionType = EntitySchemaQueryExpressionType.Parameter,
             // Expression parameter.
            Parameter = new Parameter ()
            {
                // Parameter data type - text.
                DataValueType = DataValueType.Text,
                // Parameter value.
                Value = "CH"
            }
        }
    }
}

You can download the full source code of this example here.

See Also

© bpm'online 2002-2018.

Did you find this information useful?

How can we improve it?