Note: Nintex Apps data centers are located in West US and Australia (AUS). In-region processing of Nintex Apps data is only available in these regions.

Aggregate Models

When creating a model in Nintex Apps, there are two choices for model behavior: basic and aggregate. While the basic model is most frequently used, the aggregate model offers ways to collect, group, and summarize data, and they are particularly useful for creating custom reports and dashboards.

Note:  Aggregate models are currently only available for Salesforce and SQL connectors.

What is Aggregation?

Aggregation is combining separate sets of data into a single end result: a total sum; an average a count, a maximum or minimum. As opposed to updating individual pieces of information, aggregate models surface trends in data that are used for reporting and to support strategic analysis.

Aggregate models have two exclusive elements:

  • Aggregations are a function (count, sum, average, minimum or maximum) performed server-side—before any data reaches a Nintex Apps model—on a specific field for the large number of records that are accessed by the aggregate model.
  • Groupings cluster or split out aggregations into separate rows within the model by using another field. Content is grouped so that the aggregations provide valuable insights.

Additionally, aggregate models also use:

  • Conditions: As with all models, model conditions limit what data comes into the model, and allow for filtering that data on the fly.
  • Actions: Model actions function the same way in aggregate models as they do in basic models.

Why aggregate?

Imagine a Sales manager who wants to know the value of all the opportunities in their team's pipeline, broken down by stage—to get a better idea of the overall health of their pipeline. If the team is large, that could be a lot of records. Sure, the manager could view them, stage by stage, getting a total value for each stage, but this would take a lot of time, and they still wouldn't be seeing the data all in one place.

What if there was a way to make a page that displays the total value of opportunities, grouped by stage—the total for all the prospecting leads, qualified leads, proposals submitted, etc.—plus a grand total for all stages?

And, hey, maybe an average for each stage as well (go ahead, dream a little!)

What if this page could be filtered by owner, to see how each member of the sales team was doing? Or by industry? Or by closing date? A page that might look like this:

image0

Note:  Already familiar with aggregate model concepts? Want to see them in action? Feel free to check out the An Aggregation Case Study to build the Opportunity page discussed throughout this topic.

Create an Aggregate Model

Creating an aggregate model consists of three steps—plus any desired conditions.

  1. In a Nintex Apps page, click Models.

  2. Click add Add Model and edit the model properties:

    • Model ID: Give the model a unique name.
    • Connector: Salesforce or other SQL connection.

    Note:  Aggregate models can only be used with the Salesforce and SQL connections.

  • Connection: Your specific Salesforce or SQL connection

  • External Object name: The object that contains the fields to be aggregated.

    Note:  This property is called Salesforce Object Name when the Salesforce connector is selected.

  • Model behavior: Aggregate

  • Max # of records (limit): Typically left blank, this field limits the number of groups —determined by a model's groupings—displayed in components. Aggregations always retrieve all the records for a field, but if you only wish to retrieve the first X number of groups, use this field.

When the model behavior changes to Aggregate, the model elements (listed below the model name in the Elements pane) shift from Fields, Conditions, and Actions to Aggregations, Conditions, Groupings, Actions.

If desired, display records in ascending or descending order:

  1. Click the aggregate model.

  2. In the properties pane, edit Fields to order records:

    Note:  It's only possible to order by fields that are being Grouped or Aggregated.

    • If ordering by a grouping: Enter the grouping's API name ( StageName, not Stage).
    • If ordering by an aggregation: Enter the aggregation by the formatted name of the aggregation, for example SUM(Amount).
    • Add a space and then the ordering function: ASC
  1. Click Save.

The records display in the order selected.

What criteria are used to choose what fields to aggregate? Essentially, aggregations ask the user to determine what information they need collected or compiled across a large number of records.

  1. Click the aggregate model.

  2. Click Aggregations.

  3. Click add Add to the left the field to be used for the aggregation.

    • If using a SUM, AVG, MAX, or MIN aggregation function (see below), be sure to select a field that returns a numeric value.

    Important:  It's not possible to aggregate on a field that is also used as the grouping field.

  4. Click the new aggregation and indicate the type of aggregation:

    • Function:
      • COUNT: Returns the total number of values in a field, for example, a COUNT of ID fields return the total number of records in the model.
      • COUNT DISTINCT: Returns the total number of different values in a field, for example, a COUNT DISTINCT on the Owner ID field returns the total number of different owners (without double-counting the same owners across records).
      • SUM: Returns the sum of all values for the selected field, for all the records in this model; only available for numeric fields.
      • AVG: Returns the average value for the selected field, for all the records in this model; only available for numeric fields.
      • MAX: Returns the highest value in the selected field, for all the records in this model; only available for numeric fields.
      • MIN: Returns the lowest value in the selected field, for all records in this model; only available for numeric fields.

  5. Alias name: Every aggregation (and grouping) must have an alias. This field will autopopulate with a unique name; this means it's possible to aggregate the same field multiple times with different functions. It's possible to edit this name, but the Alias Name must be unique and have 3-25 alphanumeric characters.

    Note:  Use this Alias Name— not the API name —when referencing the aggregated field in components and merge syntax.

    Important:  If updating an Alias Name in a model, the components that refer to the aggregation will not update. When changing an Alias Name, ensure to update it across all areas where it is referenced.

Both aggregations and groupings can be dragged and dropped into tables, similar to fields. They are also displayed when Add fields* is clicked from a table.

Having an aggregation of a particular set of data records can be useful, but how do to "compare and contrast" aggregated fields?

Groupings are a way to delineate data based on a specific field, in effect, to split a record's rows and group them by that field. In the Opportunities example, the total amount for all Opportunities is nice to have but doesn't really convey the desired information: what's the value of our opportunities broken up by stage? Stage, therefore, is the grouping used to cluster the Opportunity data.

Think about the data being aggregated: how do should it be compared? What field will allow the greatest visibility into that comparison?

  1. Click an aggregate model.

  2. Click Groupings.

  3. Select the grouping method. There are two options:

    Note:  You can only choose one grouping method for the model, even if there is more than one grouping created. If changing this method after adding additional groupings, it may be necessary to re-add any groupings using the other option.

    • Simple: Use with aggregations that will product basic totals.
    • Rollup: This grouping method calculates a SUM total of all rows—which are created by groupings—in the model, and appends that sum as an extra row to the model.

    Note:  Since the rollup field is appended as a row to the end of a model's data, setting a Max # of records (limit) can prevent the rollup from displaying if the limit is reached before the end of the model's groups.

  1. Select the field to group by clicking the box.

    • If grouping by a related object, click the arrow next to that object's reference field.

  2. Alias name: Every aggregation (and grouping) must have an alias. This field will autopopulate with a unique name; this means it's possible to aggregate the same field multiple times with different functions. It's possible to edit this name, but the Alias Name must be unique and have 3-25 alphanumeric characters.

    Important:  Use this Alias Name— not the API name —when referencing the aggregated field in components and merge syntax.

    Note:  When applying groupings to an aggregate model: for every grouping applied, the model will be grouped that way in all components —even if the grouping field is not represented in the component.

If groupings provide a way to "slice" up aggregations into meaningful buckets, multiple groupings provide a way to similarly split a group for increased granularity.

Imagine a page that aggregates the quarterly closed sales for reps by region. That's a region field used as a grouping. But to break each region down to the individual rep, add a second grouping on the Owner ID field.

Now there are two grouping columns: the region itself, and a breakdown of individual reps within the region.

image1

Simple rule: the more groupings used, the more delineated the data.

Note:  If an additional grouping is added to the aggregate model, but the grouping field is not added to the table, the table will nonetheless display the data with that grouping applied.

Reference fields can be used for groupings, with a few caveats: when grouping with reference fields, Nintex Apps creates groupings for both the id field and the plain language name field. Make sure to have both groupings (and do not delete either).

The name field is used in the page display, but the id field is critical to using the reference field for splitting the data.

Just as in basic models, aggregate models use conditions to limit the queried data coming into the model.

Per Salesforce documentation, HAVING is an "optional clause that can be used in a SOQL query to filter results that aggregate functions return." This clause can be employed as a model condition to filter data, but cannot be added declaratively. , it must be added by editing the XML for the Nintex Apps page, and adding the clause under the model's conditions, using the same format as other conditions.

Working with the HAVING clause is only recommended for advanced Nintex Apps users who are comfortable editing XML. To learn more about this process, see the Filter on Count community post.

Troubleshooting

Because aggregate models include a number of moving parts, basic troubleshooting starts by checking all the elements needed to make the models work:

  • Make sure there is not a number in the Max # of records (limit) field for the model.
  • Is there a Grouping field on the aggregate model?
  • Are all the fields used on the table included in the aggregate Model?
  • Are they set to the right summaries?
  • Ordering fields: Are the fields used for ordering identified correctly?
    • If ordering by a grouping, use the grouping's API name (StageName, not Stage).
    • If ordering by an aggregation, use the aggregation's formatted name, for example SUM(Amount).

Non-grouped query that uses overall aggregate functions cannot also use LIMIT ( Nintex Apps for Salesforce )

Normally, if there is no grouping field, having a limit on an aggregate model will cause this failure. However, if there is a grouping field and a limit on an aggregate model, this error may mean there is a permissions issue in Salesforce and the running user does not have access to the fields selected for grouping.

There are a few ways to resolve this error:

  1. Make sure there is a grouping.
  2. Remove the limit and see if the query works.
  3. Try adding a standard model and table with the fields you are grouping on to see if you can access the fields.