Child & Parent Expressions

Child Record Numeric Aggregation Expressions 

Child Record Expressions perform an aggregation on all the child records of the current record. There are various options, but the onus is on the person writing the expression to get the parameters such as the Child App ID and filter syntax correct.

Some examples are

ChildSum('ChildAppID','ChildFieldName') : Returns a sum (addition) of all the values in ChildFieldName in all the child records
Use the same third parameter above if you want to filter results to only certain child records

childCount('ChildAppID','{"Score" : { $gt : 0 } }') : Gets the Count of all child records where the field [Score] in the child record is greater than 0

childCount('ChildAppID', '{$or: [{"Status" : "Green"}, {"Status" : "Amber"}, {"Status" : "Red"}]}') : Get a count of all child records where the [Status] field is green, amber or green

ROUND(childAvg('ChildAppID','Score','{"Score" : { $gt : 0 } }'),2) : Gets the Average of all child records for the field [Score] where the value of [Score] is greater than 0 , and rounds the result to 2 decimal places

childCount('ChildAppID') :Gets a count of all child records (Linked to that parent record)

childCount('ChildAppID','{"TickBoxField" : true}') : Get a a count of all child records where the tick box field [TickBoxField] is ticked (set to true)

childMin('ChildAppID','Score', '{"ProjectValue" : {$gt :1000000} }') : Return the minimum value of the Score field, for all child records where the Project Value field [ProjectValue] is greater than 1M

childMax('ChildAppID','Score', '{"ProjectValue" : {$gt :1000000} }') : Return the maximum value of the Score field, for all child records where the Project Value field [ProjectValue] is greater than 1M

childFirst('ChildAppID','Score', '{"ProjectValue" : {$gt :1000000} }', '{"ProfilePeriod":-1}') : Return the FIRST value found for the Score field, for all child records where the Project Value field [ProjectValue] is greater than 1M, where the records are sorted by ProfilePeriod Descending (note the "1" at the end of the last parameter which represents the sort order, where a value of -1 gives Descending, and 1 gives Ascending)

childLast('ChildAppID','Score', '', '{"ProfilePeriod":1}') : Return the LAST value found for the Score field, for all child records where there is no filter and the records are sorted by ProfilePeriod Ascending (note the "1" at the end of the last parameter which represents the sort order, where a value of -1 gives Descending, and 1 gives Ascending)

Child Record Text Aggregation Expression

This expression will aggregate text data up from Child records to a Text or LongText Field. It does this by pulling back a value for each record from a Text Field and then separates these values with the Unicode character defined in the expression.

An example would be to pull back all Actions for a Project.

First choose the Field to identify the Actions by. This could be the Title for the Action or it could be a new Field that combines key information about the Child using text aggregation such as ActionTitle with expression [Title] + ' - ' + [Category] + ' - ' + [Owner] + ' - ' + [Status]

Then in the Parent Project App create a LongText Field to display the aggregated data. The expression for this Field would then be:

childTextAggregate('ActionsApp', 'ActionTitle', @"'\u0020 \u00A6 \n'")

The first parameter is the ID of the Child App, the second Parameter is the Field ID for the Field in the Child App that the values being aggregated come from, The third parameter is to determine the separator in the form @'" choice of separators "'.

This would list all of the Actions key content on a line each in the LongText Field in the Projects App.

Example Unicode characters and separators are:

\u0020 - White Space, \u00A6 - Broken Bar , \n - line break (requires LongText Field)

Parent Record Expressions

NOTE : Parent expressions need to be the only expression for a field, they can't be used as part of an expression.

isnull([Parent.ParentFieldName], 'Value if no Parent')

The first word '[Parent.' is fixed and refers to the fact that the parent record needs to be accessed.

The second part 'ParentFieldName' should be the ID of the parent field to be accessed.

NOTE: If you need to be able to add and edit records outside of then you will need to add an isnull() check so that if there is no parent record, the expression will not error. Often these would be the equivalent value of no user input for the Field:

isnull([Parent.TextField],'') for Parent Fields with String values.

isnull([Parent.NumberField],0) for Parent Fields with Numeric values.

isnull([Parent.Bit], false) for Parent Fields with Boolean values.

These references can be made in the Expression area, or in the default Value area, if the 'Process the Default Value as an Expression' is ticked.