Skip to main content

AggregateFields

AggregateFields are ADITO models for aggregating EntityField values, for example by

  • counting datasets, for example via an ID column,
  • summing values, or
  • returning minimum, maximum, or average values.

In most cases, aggregations are used together with grouping.

One straightforward example is the Offer context in the ADITO xRM project. The following sections use this context to explain both the client-side appearance and the Designer-side configuration of AggregateFields.

Appearance​

In the client, open the Offer context from menu group Sales. In the FilterView of this context (OfferFilter_view), use the view selection button in the upper-right corner. Among the available ViewTemplates, three variants of type DynamicMultiDataChart are provided. All three are column charts based on a predefined grouping by EntityField STATUS of Offer_entity in module offer, which contains values such as Checked, Open, Sent, or Won.

  • Count Chart shows how many Offer datasets exist for each STATUS value.
  • Sum Chart shows the sum of all NET values for each STATUS value.
  • Probability Chart shows the average PROBABILITY value for each STATUS value.
note

DynamicMultiDataChart is only one possible target. AggregateFields can also be used in other ViewTemplate types and in Consumers. See Properties allowing AggregateFields.

Configuration​

When using DbRecordContainer​

To configure aggregation with a DbRecordContainer, proceed as follows:

  1. Create an EntityField and assign a title. This field acts as the parent field of the AggregateField. In OfferFilter_view, the aggregate-related parent fields are COUNT, NET, and PROBABILITY of Offer_entity.
  2. Add an AggregateField to that EntityField. Right-click the EntityField and choose New AggregateField. The dialog asks for a name and a parent field.
    • The default naming pattern is <EntityField name>_aggregate, for example COUNT_aggregate, NET_aggregate, or PROBABILITY_aggregate.
    • By default, the parent field is the EntityField to which the AggregateField is added. In most cases this setting can remain unchanged.
  3. Open the RecordContainer node, for example db, and initialize the corresponding xxx.value RecordFieldMapping such as COUNT_aggregate.value.
  4. In the "Properties" window, you can now configure the properties
    • recordField: Select the EntityField whose values should be aggregated. Alternatively, you can enter SQL code in expression. If both are set, ADITO uses expression exclusively.
    • aggregateType: Select the aggregation type, for example COUNT, SUM, AVG, MIN, or MAX.
  5. Create a ViewTemplate that supports AggregateFields. See Properties allowing AggregateFields. In the example above, this is done with DynamicMultiDataChart ViewTemplates assigned to OfferFilter_view.
  6. Set the new ViewTemplate properties:
    • title, for example Sum chart
    • chartType, in this case COLUMN
    • defaultGroupFields: Because the x-axis is determined by grouping, set the grouping field here. In the example, this is STATUS.
    • columns: The y-axis values come from an EntityField. In the three example charts these are COUNT, NET, and PROBABILITY. In addition, set the corresponding AggregateField in columns.aggregateEntityField, for example COUNT_aggregate.
    • yAxisLabel: Optionally define a label for the y-axis.

When using JDitoRecordContainer​

AggregateFields can also be used with a JDitoRecordContainer. The first configuration steps are similar to the DbRecordContainer setup.

The ADITO xRM context Turnover is a useful reference for learning this setup:

  1. Open an ADITO xRM project that contains demo data.
  2. In menu group Sales, open Sales forecast. This opens the context that appears as Turnover in the Designer.
  3. Review the different ViewTemplates in the GroupLayout of the FilterView TurnoverDynamicMultiDataChart_view.
  4. In the Designer, inspect the Turnover context and the ViewTemplates assigned to TurnoverDynamicMultiDataChart_view.
  5. Read the documentation property of Turnover_entity in module revenue.
  6. Review the Entity configuration, including EntityFields, RecordFieldMappings, and AggregateFields.
  7. Examine the involved JDitoRecordContainers in detail. Their contentProcess implementations use a specific grouping and result format.
    • The contentProcess of RecordContainer jdito contains detailed inline documentation.
    • It is also useful to inspect jditoDynamicMultiDataChart and its contentProcess.

displayValueProcess of an AggregateField​

To format an AggregateField, you need to use the displayValueProcess.

important

AggregateFields currently can only be formatted by using the displayValueProcess. In the RecordFieldMappings, a displayValue.expression property exists, but as the system builds the aggregate from the return of the expression, it is not possible to use it for extra formatting or adding further elements to the result of the aggregate function.

Example of a displayValueProcess of an AggregateField
import { result, vars  } from "@aditosoftware/jdito-types";

result.string("#: " + vars.get("$this.value"))

Usage in filter​

If you want to use an AggregateField in a filter, set isFilterable of the parent field's RecordFieldMapping to true. The field then appears in the filter component on the right side of the FilterView using the title of the parent field, not the title of the AggregateField itself.

AggregateFields can also be used in filterConditionProcess. In that context, the following variables are relevant:

  • $local.isAggregateCondition: true if the current condition is based on an AggregateField
  • $local.conditionHaving: The condition that must be inserted into the subselect when a FilterExtension is triggered by an aggregation field
  • $local.columnPlaceholder: The placeholder value used when filtering by attributes and resolving the correct column name

Usage in Consumer​

AggregateFields can also be used in a Consumer via the properties

  • lookupIdField
  • targetContextField
  • targetIdField
  • sortingField
note

If you need to display two aggregations in the same Entity, use a second Consumer.

Properties allowing AggregateFields​

AggregateFields can be specified in various properties of the following ADITO models:

  1. ViewTemplates
    • properties entityField and columns/fields:
      • Actions
      • CardTable
      • DynamicMultiDataChart
      • DynamicSingleDataChart
      • Gantt
      • Generic
      • GenericMultiple
      • ScoreCard
      • Table
      • TitledList
      • TreeTable
    • ActionList: properties titleField, descriptionField, iconField
  2. Consumer: properties lookupIdField, targetContextField, targetIdField, sortingField