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

DataService. Using macros

Glossary Item Box

General provisions

During execution of DataService operations data often needs to be filtered for a certain period of time. Macros simplify such tasks and help to avoid creating unnecessary custom methods. The macros are implemented in a form of special classes that are designed for calculating typical values in query expressions, such as calculating the start and end date of the current quarter. Macros can be used only if the query expression type is a function. For more information about macro expression types, please see the DataService. Data filtering article.

Types of macros

When creating queries to DataService, both parameterized (ie requiring an argument) and non-parameterized macros can be used. Macro types that must be used in the macro expressions are defined in the EntitySchemaQueryMacrosType enumeration in the Terrasoft.Core.Entities name space. Enumeration values of macro types and their descriptions are available in table 1.

Table 1. Values of the EntitySchemaQueryMacrosType enumeration and their descriptions

Macro Value Description
CurrentHalfYear 16 Current half-year (January-June or July-December).
CurrentHour 21 Current hour.
CurrentMonth 10 Current month.
CurrentQuarter 13 Current quarter.
CurrentUser 1 Current user.
CurrentUserContact 2 Contact record of the current user.
CurrentWeek 7 Current week.
CurrentYear 19 Current year.
DayOfMonth 28 Day of month. Requires parameterization.
DayOfWeek 29 Week day. Requires parameterization.
Hour 30 Hour. Requires parameterization.
HourMinute 31 Time. Requires parameterization.
Month 32 Month. Requires parameterization.
NextHalfYear 17 Next half-year (January-June or July-December).
NextHour 22 Next hour.
NextMonth 11 Next month.
NextNDays 24 Next N days. Requires parameterization.
NextNHours 26 Next N hours. Requires parameterization.
NextQuarter 14 Next quarter.
NextWeek 8 Next week.
NextYear 23 Next year.
None 0 Type of macro not defined.
PreviousHalfYear 15 Previous half-year (January-June or July-December).
PreviousHour 20 Previous hour.
PreviousMonth 9 Previous month.
PreviousNDays 25 Previous N days. Requires parameterization.
PreviousNHours 27 Previous N hours. Requires parameterization.
PreviousQuarter 12 Previous quarter.
PreviousWeek 6 Previous week.
PreviousYear 18 Previous year.
Today 4 Today.
Tomorrow 5 Tomorrow.
Year 33 Year. Requires parameterization.
Yesterday 3 Yesterday.

Example of using macros

Case description

Create a console application that uses DataService to read records from the [Contacts] section with the following columns:

  • Id
  • Full name
  • Birth date

The data must be filtered, so that only contacts who were born in 1992 are shown.

Case realization

The complete source code for implementation of this case is available here.

Case implementation algorithm

1. Create and set up a C# application project that reads records

To execute this step, execute the record reading case covered in the "DataService. Reading records" article.

The result of implementing an instance of query class for reading records:

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

2.  Add a filter implementation with macros

To filter the data, create an instance of the Filters collection class, fill out the properties with corresponding values, and then pass the instance link to the Filters property of the query class created on the previous step.

An example of filter collection class implementation:

// Query filters.
var selectFilters = new Filters()
{
    // Filter type — group.
    FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,
    // Filter collection.
    Items = new Dictionary<string, Filter>
    {
        
        // Filter by year of birth.
        {
            // Key.
            "FilterYear",
            // Value.
            new Filter
            {
                // Filter type — comparison.
                FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
                // Comparison type — equal.
                ComparisonType = FilterComparisonType.Equal,
                // Expression to check.
                LeftExpression = new BaseExpression()
                {
                    // Expression type — schema column.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to schema.
                    ColumnPath = "BirthDate"
                },
                // Expression with which the checked value is compared.
                RightExpression = new BaseExpression
                {
                    // Expression type — function.
                    ExpressionType = EntitySchemaQueryExpressionType.Function,
                    // Function type — macro.
                    FunctionType = FunctionType.Macros,
                    // Macro type — year.
                    MacrosType = EntitySchemaQueryMacrosType.Year,
                    // Function argument.
                    FunctionArgument = new BaseExpression
                    {
                        // Type of expression that determines the argument — parameter.
                        ExpressionType = EntitySchemaQueryExpressionType.Parameter,
                        // Parameter initialization.
                        Parameter = new Parameter
                        {
                            // Parameter type — integer.
                            DataValueType = DataValueType.Integer,
                            // Parameter value.
                            Value = "1992"
                        }
                    }
                }
            }
        }
    }
};
// Adding filters to query.
selectQuery.Filters = selectFilters;
// Serialization of select query class instance in a JSON string.
var json = new JavaScriptSerializer().Serialize(selectQuery);

The collection contains a single filter with the "FilterYear" key. Because only those records that have their year of birth equal to 1992 must be selected from the collection, the type of filter is set as a comparison filter. The type of comparison is set as an equality of values. As a verified expression, set the [Date of birth] column. Specify the macro function as the expression to compare with.

In this case using a macro is optimal because the birth date is stored in the database in "YYYY-MM-DD" format. The macro automatically determines the year value, so the developer does not need to write additional program code.

Because the EntitySchemaQueryMacrosType.Year macro is parametric, the FunctionArgument property must be initialized and assigned a link to an instance of the BaseExpression class. In it, the integer parameter with value "1992" is defined.

The complete source code for implementation of this case is available here.

See Also

© bpm'online 2002-2017.

Did you find this information useful?

How can we improve it?