Documentation

Creatio development guide
PDF
This documentation is valid for Creatio version 7.16.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. Creatio 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 "The EntitySchemaQuery class. Filters handling" article. Next, there is an example of using filters in requests to the DataService service from a third-party application.

See Also

© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?