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.

 

Child expressions will be written in the format of:

Function('ChildAppID', 'FieldID', 'Filter(Optional)', 'SortOrder(IfRequired)')

 

Different Functions include:

 - childSum: This sums up all values in a number field where, if a filter is added, all filter conditions are met. Otherwise, it will sum up all values in all records

 - childCount: This counts all records where, if a filter is added, all filter conditions are met. This is the only function that doesn't require a FieldID as it counts record

 - childAvg: This averages all values in a number field where, if a filter is added, all filter conditions are met. Otherwise, it will average all values in all records

 - childMin: This will bring back the minimum value found in a number field across all records where, if a filter is added, all filter conditions are met. Otherwise, it will pull back the minimum value found across all records

 - childMax: This will bring back the maximum value found in a number field across all records where, if a filter is added, all filter conditions are met. Otherwise, it will pull back the maximum value found across all records

 - childFirst: This will bring back the first value found in any field across all records where, if a filter is added, all filter conditions are met. Otherwise, it will pull back the first value found across all records. There is also the option to add a sort order at the end where you can choose to pick from the top or bottom of a sorted list.

 - childLast: This will bring back the last value found in any field across all records where, if a filter is added, all filter conditions are met. Otherwise, it will pull back the last value found across all records. There is also the option to add a sort order at the end where you can choose to pick from the top or bottom of a sorted list.

Note: Fields referenced on the left-hand-side (LHS) of the colon always need to be in double quotes ". Whereas values on the RHS right-hand-side don't depending on the data type, cases are:
 - Boolean Like Bit fields (true/false) these aren't double-quoted '{"FilterBitField1": true}'

 - Numeric Like Number fields aren't double-quoted '{"FilterNumericField1": 12}'

 - String Like Text, RAG are double-quotes '{"FilterStringField1": "Text"}'

Some examples are:

childSum('ChildAppID', 'FieldToSum', '{"FilterField1": "Value", "FilterField2": "Value"}') 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

 

childSum('ChildAppID', 'FieldToSum', '{"ComponentName": "' + [ComponentName] + '"}')

Above is an example of dynamic filtering and sums the FieldToSum of Child records that have a Component Name matching the [ComponentName] in the Parent Record.

 

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 red

 

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', '', '{"DateField":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 Date 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)

 

Note: Child expression should only contain a single child function and should not be combined with any other expression functions

 

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 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.

 

Example 1: All Actions with a Red Status

The expression for this Field would then be:

childConcat('\n', 'Actions', 'ActionTitle', '{"Status" : "Red"}')

The first parameter is the Unicode separators, the second Parameter is the Child App that is being aggregated from, the third parameter is the Field that is being aggregated.

The fourth parameter is the filter that should be applied. In this example we 

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

 

Example 2: All Actions that are Status Red with an Importance > 2

This example shows that we can build up multiple conditions to filter the child records to be aggregated.

childConcat('\n', 'Actions', 'ActionTitle', '{$and: [{"Stauts" : "Red"}, {"Importance" : { $gt : 2 } }]}')

 

Example 3: All Actions that relate to the current Project Phase

This example shows how we can dynamically reference a value in the parent Project to filter by. It builds up the filter as a string expression with the Parent Field value referenced in square brackets. It also shows how we can use different separators.

childConcat('\u00A6 \u0020', 'Actions', 'ActionTitle', '{"Phase" : "' + [CurrentPhase] + '"}')

 

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.

 

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 Security - 

Child expressions and parent expressions will not take into account the security of the record its pulling the data from when comparing it to the security of the user viewing the expression. For example, if you have a sum of actions for a project, even if the user does not have visibility of the actions, if they can see the child expression field in the parent they will see the sum for all records

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.