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:
|
|||||||||||||||||||||
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. | |||||||||||||||||||||
TrimDateTime |
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 | EntitySchemaQuery |
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 EntitySchemaQuery
|
||||||||||||||||||||||||
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:
|
||||||||||||||||||||||||
MacrosType | EntitySchemaQuery |
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:
|
||||||||||||||||||||||||
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.