Aggregate Expressions

Aggregate Expression

The aggregate expressions allows some of the same type of aggregation as the child record expressions, but for an App which is not necessarily a child app and for a set of records independent of any record hierarchy. the same base expression can be used with different aggregate types and uses different parameters for each aggregate type.

An example being that you might want to pull into an account plan record the average invoice value of all invoices for all customers so that you can compare against this customer

Note: if the data of the records used in the aggregation changes such that the value will be different, this would not be reflected in the record with the expression until that record is saved

USES

Get the smallest value from a set of records (min)

aggregate('min','false','52753880dd5f49093c37dbf8','Workplan','Cost','{ "Status": "Red" }')

• Parameter 1 : the aggregate type : 'min' - we are looking for the minimal (smallest) value of all the records found
• Parameter 2 : Child records only : 'false' - we want to look in all records, not just child records of the record the expression is run from
• Parameter 3 : Team ID : '52753880dd5f49093c37dbf8' - What team will be used as the security when looking at all of the records matching the filter - this needs to be a valid team id. The records looked at when the expression runs will need to be visible to this team
• Parameter 4 : App ID : 'Workplan' - Which App should the aggregation come from - needs to be the ID of the app, not the name
• Parameter 5 : Field ID : 'Cost' - Which Field is being aggregated - needs to be the ID of the field, not the name
• Parameter 6: Filter of the records to be included in the aggregation : '{ "Status": "Red" }' - this can contain nothing (two single quotes) or a single value comaprison against a fixed value such as status is Red above. Some other examples are :
  • '{ "Status": "Red" }'  - only include records where the value of the field ID Status is 'Red'
  • '{ "ProjectCost": { $gt: 10000} }'  - only include records where the value of the field ID ProjectCost is greater than 10000
  • '{ "Project": "' + [Project] + '" }'  - only include records where the value of the field ID Project is the same as the value of the field [Project] in the record where the expression is being run from
  • '{ "Status": "Red", "Project": "' + [Project] + '" }' - you can use more than one filter criteria in the filter by combining the filters with a comma separating them - in this case using the two above filters

Note: All sums, avgs, maxs, etc will be for all records under the team selected in parameter 3 so please make sure you are happy that everyone who will see the aggregate should see these values

Get the largest value (max)

aggregate('max','false','52753880dd5f49093c37dbf8','Workplan','Cost','{ "Status": "Red" }')

Use the same parameters as above

Get the average value (avg)

aggregate('avg','false','52753880dd5f49093c37dbf8','Workplan','Cost','{ "Status": "Red" }')

Use the same parameters as above

Get the sum value of all the records added up (sum)

aggregate('sum','false','52753880dd5f49093c37dbf8','Workplan','Cost','{ "Status": "Red" }')

Use the same parameters as above

Aggregation of text from a text field in all of the matching records  (last)

aggregate('last','false','52753880dd5f49093c37dbf8','Workplan','Cost','{ "Status": "Red" }')

 Use the same parameters as above

Get the first value of the set of records (first)

aggregate('first','false','52753880dd5f49093c37dbf8','Workplan','Cost','{ "Status": "Red" }')

Use the same parameters as above, but with the addition of a 7th parameter to specify a sort order so that we bring back the first record once the records are ordered correctly

• Parameter 7: Specify the field and the order required
  • '{ "ProjectCost": 1}' - order the records by the field ID ProjectCost, and use Ascending (note the 1)
  • '{ "ProjectStage": -1}' - order the records by the field ID ProjectStage, and use Descending (note the -1)

Get the last value of the set of records (last)

aggregate('last','false','52753880dd5f49093c37dbf8','Workplan','Cost','{ "Status": "Red" }'

Use the same parameters as above, including parameter 7

A count of the number of matching records (count)

aggregate('count','false','52753880dd5f49093c37dbf8','Workplan','{ "Status": "Red" }')

Note that Parameter 5 is now the filter as there is no field parameter required

A sum records matching multiple filters (sum)

aggregate('sum','false','52753880dd5f49093c37dbf8','ComponentsDB','Demand','{"Scenario":true, "Month": "' + [Month] + '", "ComponentName": "' + [ComponentName] + '"}')

above is the sum of records that match a set value of [Scenario] = true and the variable value of [month] and [ComponentName] set in the record

Make sure to click the 'Save' button when making any changes in order for them to be added to the next app version. Once you have made all the changes you need to an application you are then ready to publish it to workspace.

Note on Field Dependencies - Read more here

Field dependencies are by definition the other field(s) that are reliant on a given field, usually dependent fields are interlinked to one another by expressions and are indicated by an atom symbol. They are a crucial tool when looking to delete Fields within an App or, when it comes to understanding the structure and data flows of an App.