Skip to main content

AggregateFields

AggregateFields are ADITO models designed for aggregating EntityField values, e.g.

  • counting datasets (e.g., via their ID column)
  • summing up values
  • showing minimum, maximum, or average values

In most cases, aggregations are related to a grouping.

A plain example of an implementation of AggregateFields is Context "Offer" in the ADITO xRM project. We will use this example to show the appearance of AggregateFields in the client and to explain how they can be configured in the ADITO Designer.

Appearance​

In the client, navigate to Context "Offer" (menu group "Sales"). In the FilterView of this Context (OfferFilter_view), click on the view selection button (see upper right corner): Amongst other ViewTemplates, you can choose between 3 variants of ViewTemplates of type "DynamicMultiDataChart", which are all column charts, based on a preset grouping according to Offer_entity's EntityField STATUS (holding values like "Checked", "Open", "Sent", "Won"):

  • Count Chart shows how many Offer datasets have which STATUS value.
  • Sum Chart shows, separately for each STATUS value, the sum of all NET values (EntityField NET) of Offer datasets that have the respective STATUS value.
  • Probability Chart shows, separately for each STATUS value, the average probabilty (EntityField PROBABILITY) of all Offer datasets that have a specific STATUS value.
note

Besides "DynamicMultiDataChart", there are further ViewTemplate types suitable for processing AggregateFields. Besides, AggregateFields can also be used in a Consumer. Find further information in chapter Properties allowing AggregateFields.

Configuration​

when using DbRecordContainer​

To configure an aggregate functionality, please proceed as follows:

  1. Create an EntityField (and give it a title) as so-called "parent field" for the AggregateField (see below). As for the aggregate-related ViewTemplates of OfferFilter_view, these are Offer_entity's EntityFields COUNT (Count), NET (Total net), and PROBABILTY (Probability).
  2. Add an AggregateField to the new EntityField (and give it a title): Right-click on the EntityField and choose option "New AggregateField" from the context menu. This will open a dialog requesting name and parent field.
    • The name will be preset in the syntax "<EntityField name>_aggregate", which can be left unchanged ("COUNT_aggregate", "NET_aggregate", and "PROBABILITY_aggregate").
    • The parent field is, by default, the EntityField to which the AggregateField has been added. In most cases, this can remain unchanged. (Exceptions are explained later.)
  3. Open the RecordContainer node (e.g. "db") and double-click on the corresponding "xxx.value" RecordFieldMapping, e.g. COUNT_aggregate.value, in order to initialize it.
  4. In the "Properties" window, you can now configure the properties
    • recordField: Select the EntityField holding the values that are to be aggregated. Alternatively, you can specify an SQL code in property "expression". If both properties are set, the ADITO logic will exclusively use "expression" (same logic as for the RecordFieldMapping of an EntityField).
    • aggregateType: Select the type of aggregation, e.g., COUNT, SUM, AVG (average), MIN(imum), or MAX(imum).
  5. Create a new ViewTemplate that can process AggregateFields (see chapter Properties allowing AggregateFields). In our above example, these are ViewTemplates of type "DynamicMultiDataChart", which are assigned to OfferFilter_view.
  6. Set the new ViewTemplate properties:
    • title (e.g. "Sum chart")
    • chartType (here: COLUMN)
    • defaultGroupFields: As the scale of the x-axis is the result of a data grouping, enter the EntityField that determines the grouping in property defaultGroupFields. In our example, it is the EntityField STATUS.
    • columns: The values of the y-axis (= here: the height of the columns) are provided by an EntityField - in our 3 column chart examples, these are the EntityFields having AggregateFields assigned to: COUNT, NET, and PROBABILITY, respectively. Additionally, you need to set the corresponding AggregateField in columns aggregateEntityField, i.e., COUNT_aggregate, NET_aggregate, and PROBABILITY_aggregate, respectively.
    • yAxisLabel: Optionally, you can set a label for the y-axis here.

when using JDitoRecordContainer​

AggregateFields can also be applied when using a JDitoRecordContainer. The first steps are similar to when using a DbRecordContainer (see above).

A good pattern for learning the configuration of AggregateFields in a JDitoRecordContainer is the ADITO xRM project's Context "Turnover". Proceed as follows:

  1. Login to an ADITO xRM project that includes demo data.
  2. In the global menu, click on "Sales forecast" (menu group "Sales"). This will open the Context that appears named "Turnover" in the ADITO Designer.
  3. Scan through the various ViewTemplates in the GroupLayout of this Context's FilterView (internal name: "TurnoverDynamicMultiDataChart_view") and get familiar with the provided functionalities.
  4. In the Designer, navigate to Context "Turnover" and scan through its elements, e.g., inspect the configuration of the ViewTemplates assigned to TurnoverDynamicMultiDataChart_view.
  5. Read property "documentation" of Entity "Turnover_entity".
  6. Study this Entity's configuration (EntityFields, RecordFieldMappings, AggregateFields etc.)
  7. Study the JDitoRecordContainers very carefully: Their "contentProcess" properties include a special grouping and result format.
    • The contentProcess of RecordContainer "jdito" has a comprehensive inline code documentation, which will help you to understand the technique.
    • Also study the configuration of JDitoRecordContainer "jditoDynamicMultiDataChart", as well as the code of 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 property isFilterable of the corresponding parentField's RecordFieldMapping to true. It will then appear in the filter component (to the right of the FilterView) with the title of the AggregateField's parent field (not of the AggregateField itself), so you can manually set a filter condition.

Furthermore, you can use AggregateFields also in the filterConditionProcess. Then, the following functions may be helpful:

  • $local.isAggregateCondition: true, if the condition, for which the filterConditionProcess is executed, is based on an AggregateField; else false
  • $local.conditionHaving: If a FilterExtension is executed due to an aggregation field, this variable returns the condition to be included in the subselect
  • $local.columnPlaceholder: The value of the place holder, if you filter according to attributes und thus neet to retrieve the correct column name.

Usage in Consumer​

AggregateFields can also be used in a Consumer, via its properties

  • lookupIdField
  • targetContextField
  • targetIdField
  • sortingField
note

If you want to show 2 aggregations in the same Entity, you need to 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