Creatio development guide
PDF
bpm’online development guide
This documentation is valid for Creatio version 7.13.0. We recommend using the newest version of Creatio documentation.

Adding columns to a query

Glossary Item Box

Terrasoft.EntitySchemaQuery query column is Terrassoft.EntityQueryColumn class instance. You can specify main characteristics of column instance in its properties: title, display value, checkboxes, sorting order and direction etc.

addColumn() method that returns instance of column, added to query, is designed for adding columns to queries. The column name relative to root schema is formed in addColumn() methods in accordance with rules, described in Building of paths to columns relative to root schema. This method has several variants that allow for adding columns with different parameters to a query (table 1).

Table 1. — Method of adding columns to query

Method
addColumn(column,[columnAlias])

Creates and adds Terrasoft.Entity.QueryColumn column instance to query column collection.

column String/Terrasoft.BaseQueryColumn Is a column adding path (is specified relative torootSchema) or query column instance Terrasoft.BaseQueryColumn.
columnAlias String (optional) Column alias.
addAggregationSchemaColumn(columnPath, aggregationType, [columnAlias], aggregationEvalType)

Creates and adds Terrasoft.FunctionQueryColumn functional column instance with set aggregation type (Terrasoft.FunctionType.AGGREGATION) to query column collection.

columnPath String Is a column adding path (it is specified relative torootSchema).
aggregationType Terrasoft.AggregationType Is a type of used aggregation function.
columnAlias String (optional) Is a column alias.
aggregationEvalType Terrasoft.AggregationEvalType Is an application field of aggregation function.

Aggregation types (Terrasoft.AggregationType)

AVG Is an average value of all times.
COUNT Is a number of all items.
MAX Is a maximum value among all items.
MIN Is a minimum value among all items.
NONE Means that the type of aggregation function is not determined.
SUM Is a sum of the values of all items.

Application field of aggregation function (Terrasoft.AggregationEvalType)

NONE Means that application field of aggregation function is not determined.
ALL Means that this function is applied to all items.
DISTINCT Means that this function is applied to unique values.
addParameterColumn(paramValue, paramDataType, [columnAlias])

It creates and adds Terrasoft.ParemeterQueryColumn parameter column instance to column correlation.

paramValue Mixed Is a parameter value. The value should correspond to data type.
paramDataType Terrasoft.DataValueType Is a parameter data type.
columnAlias String (optional) Is a column alias.
addFunctionColumn(columnPath, functionType, [columnAlias])

It creates and adds Terrasoft.FunctionQueryColumn function column instance to column collection.

columnPath String Is a column adding path (it is specified relative torootSchema).
functionType Terrasoft.FunctionType Is a function type.
columnAlias String (optional) Is a column alias.

Function type (Terrasoft.FunctionType)

NONE Means that functional expression type is not determined.
MACROS Is a macro substitution.
AGGREGATION Is an aggregation function.
DATE_PART Is a date part.
LENGTH Is a length of byte value.
addDatePartFunctionColumn(columnPath, datePartType, [columnAlias])

It creates and adds Terrasoft.FunctionQueryColumn function column instance with [Date Part] type (Terrasoft.FucntionType.DATE_PART) to query column collection.

columnPath String Is a column adding path (it is specified relative torootSchema).
datePartType Terrasoft.DatePartType Is a data part, used as a value.
columnAlias String (optional) Column alias.

Data part(Terrasoft.DatePartType)

NONE Is a blank field.
DAY Is a day.
WEEK Is a week.
MONTH Is a month.
YEAR Is a year.
WEEK_DAY Is a week day.
HOUR Is an hour.
HOUR_MINUTE Is a minute.
addMacrosColumn(macrosType, [columnAlias])

It creates and adds Terrasoft.FunctionQueryColumn function column instance with [Macros] type (Terrasoft.FunctionType.MACROS) that doesn't require parameterization (for example, current month, current user, primary column etc.) to column collection.

macrosType Terrasoft.QueryMacrosType Is a column macros type.
columnAlias String (optional) Is a column alias.

Macros column types (Terrasoft.QueryMacrosType)

NONE Means that macros type is not determined.
CURRENT_USER Means current user.
CURRENT_USER_CONTACT Means current user contact.
YESTERDAY Means yesterday.
TODAY Means today.
TOMORROW Means tomorrow.
PREVIOUS_WEEK Means previous week.
CURRENT_WEEK Means current week.
NEXT_WEEK Means next week.
PREVIOUS_MONTH Means previous month.
CURRENT_MONTH Means current month.
NEXT_MONTH Means next month.
PREVIOUS_QUARTER Means previous quarter.
CURRENT_QUARTER Means current quarter.
NEXT_QUARTER Means next quarter.
PREVIOUS_HALF_YEAR Means previous half year.
CURRENT_HALF_YEAR Means current half year.
NEXT_HALF_YEAR Means next half year.
PREVIOUS_YEAR Means previous year.
CURRENT_YEAR Means current year.
PREVIOUS_HOUR Means previous hour.
CURRENT_HOUR Means current hour.
NEXT_HOUR Means next hour.
NEXT_YEAR Means next year.
NEXT_N_DAYS Means next N days. It requires parameterization.
PREVIOUS_N_DAYS Means previous N days. It requires parameterization.
NEXT_N_HOURS Means next N hours. It requires parameterization.
PREVIOUS_N_HOURS Means previous N hours. It requires parameterization.
PRIMARY_COLUMN Means primary column.
PRIMARY_DISPLAY_COLUMN Means primary display column.
PRIMARY_IMAGE_COLUMN Means primary image column.
addDatePeriodMacrosColumn(macrosType, [macrosValue], [columnAlias])

It creates and adds Terrasoft.FunctionQueryColumn function column instance with [Macros] type (Terrasoft.FucntionType.MACROS) to query column columns. The function adds column with macros type that requires parameterization. For example, next N days, the 3d quarter of the year etc.

macrosType Terrasoft.QueryMacrosType Is a macros column type.
macrosValue Number/Date (optional) Is an auxiliary variable for macros.
columnAlias String (optional) Is a column alias.

Examples of addition of columns to query

Example 1. — Adding query column from root schema to query column collection

var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
    rootSchemaName: "Activity"
});
esq.addColumn("DurationInMinutes", "ActivityDuration");

Example 2. — Adding aggregation column query with SUM aggregation type, applied to all table records, to query column collection

var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
    rootSchemaName: "Activity"
});
esq.addAggregationSchemaColumn("DurationInMinutes", Terrasoft.AggregationType.SUM, "ActivitiesDuration", Terrasoft.AggregationEvalType.ALL);

Example 3. — Adding aggregation column query with COUNT aggregation type, applied to table unique records, to query column collection

var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
    rootSchemaName: "Activity"
});
esq.addAggregationSchemaColumn("DurationInMinutes", Terrasoft.AggregationType.COUNT, "UniqueActivitiesCount", Terrasoft.AggregationEvalType.DISTINCT);

Example 4. — Adding parameter column with TEXT data type to query column type

var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
    rootSchemaName: "Activity"
});
esq.addParameterColumn("DurationInMinutes", Terrasoft.DataValueType.TEXT, "DurationColumnName");

Example 5. — Adding function column with LENGTH function type (value size in bytes) to query column collection

var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
    rootSchemaName: "Activity"
});
esq.addFunctionColumn("Photo.Data", Terrasoft.FunctionType.LENGTH, "PhotoLength");

Example 6. — Adding function column with Date-Part function type (date part) to query column collection. Week day is used as a value

var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
    rootSchemaName: "Activity"
});
esq.addDatePartFunctionColumn("StartDate", Terrasoft.DatePartType.WEEK_DAY, "StartDay");

Example 7. — Adding function column with MACROS type that don't require parameterization, i.e. PRIMARY_DISPLY-COLUMN (Primary Display Column), to query column collection

var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, {
    rootSchemaName: "Activity"
});
esq.addMacrosColumn(Terrasoft.QueryMacrosType.PRIMARY_DISPLAY_COLUMN, "PrimaryDisplayColumnValue");

See Also

© bpm'online 2002-2019.

Did you find this information useful?

How can we improve it?