Articles in this section

See more

Child & Parent Expressions

Avatar
Kyle Theobald
  • Updated
Follow

Overview

Child and Parent Expressions allow values to be calculated or referenced across a parent-child App relationship.

  • Child Expressions are configured in the parent App and calculate or retrieve values from its linked child Records.
  • Parent Expressions are configured in the child App and retrieve a value from its linked parent Record.

These expressions can be used to summarise child data, retrieve the first or last matching value, inherit values from a parent, support calculations and reduce duplicate data entry.

 

Expression Recalculation Direction

Expressions recalculate upwards through a parent-child hierarchy.

When a child Record is created, updated, linked or unlinked:

  1. Expressions in the child Record are calculated, including any Parent Expressions.
  2. Expressions in its parent Record are then recalculated.
  3. In a multilevel hierarchy, the recalculation continues upwards through each parent.

Updating a parent Record does not automatically update values in its existing child Records. If a changed parent value must be passed down to existing children, an Update Child Field Value Workflow is required.

 

 

Child Record Aggregation Expressions

Child Expressions calculate or retrieve values from Records linked as children of the current parent Record.

The general expression structure is:

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

The parameters required depend on the selected function. The App Builder must provide the correct Child App ID, Field identifier, filter syntax and, where applicable, sort order.

Archived child Records are excluded from these expressions. All active matching child Records are included regardless of the viewing User’s access to the individual Records.

 

Numeric Aggregation Functions

The following functions calculate numeric results from linked child Records:

  • childSum: Returns the sum of the values in a numeric Field.
  • childCount: Returns the number of matching child Records. This is the only child function that does not require a Field identifier.
  • childAvg: Returns the average of the values in a numeric Field.
  • childMin: Returns the minimum value found in a numeric Field.
  • childMax: Returns the maximum value found in a numeric Field.

If a filter is included, only child Records meeting its conditions are included in the calculation.

 

First and Last Value Functions

The following functions retrieve a value from the first or last matching child Record:

  • childFirst: Returns the specified Field value from the first matching child Record.
  • childLast: Returns the specified Field value from the last matching child Record.

The Field used by childFirst or childLast does not need to be numeric.

A sort order should normally be provided so that Softools can determine which matching Record should be treated as the first or last. A sort order is not necessary when the filter can only ever match one child Record.

For sort order values:

  • -1 sorts values in descending order.
  • 1 sorts values in ascending order.

 

Results When No Records Match

When no child Records match the expression:

  • Numeric aggregation functions, including childSum, childCount, childAvg, childMin and childMax, return 0.
  • childFirst and childLast return a blank value.

 

Filter Syntax

The optional filter determines which child Records are included in the expression.

Fields referenced on the left-hand side of a filter use double quotation marks. Whether the value on the right-hand side uses quotation marks depends on the data type.

Boolean values are not enclosed in quotation marks:

'{"FilterBitField1": true}'

Numeric values are not enclosed in quotation marks:

'{"FilterNumericField1": 12}'

String values, including Text and RAG values, are enclosed in quotation marks:

'{"FilterStringField1": "Text"}'

Multiple conditions can be combined. The following example includes Records where FilterField1 is true and FilterField2 is either Done or In Progress:

'{"FilterField1": true, "$or": [{"FilterField2":"Done"},{"FilterField2":"In Progress"}]}'

 

Numeric Aggregation Examples

Sum all values in FieldToSum where the filter conditions are met:

childSum('ChildAppID', 'FieldToSum', '{"FilterField1": "Value", "FilterField2": "Value"}')

Dynamically filter child Records using a value from the current parent Record:

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

This sums FieldToSum for child Records whose ComponentName matches the value in the current parent Record.

Count child Records where Score is greater than zero:

childCount('ChildAppID','{"Score" : { $gt : 0 } }')

Count child Records where Status is Green, Amber or Red:

childCount('ChildAppID', '{$or: [{"Status" : "Green"}, {"Status" : "Amber"}, {"Status" : "Red"}]}')

Return the average Score for child Records where Score is greater than zero, rounded to two decimal places:

ROUND(childAvg('ChildAppID','Score','{"Score" : { $gt : 0 } }'),2)

Count all active child Records linked to the current parent:

childCount('ChildAppID')

Count child Records where a Bit Field is true:

childCount('ChildAppID','{"TickBoxField" : true}')

Return the minimum Score from child Records where ProjectValue is greater than 1,000,000:

childMin('ChildAppID','Score', '{"ProjectValue" : {$gt :1000000} }')

Return the maximum Score from child Records where ProjectValue is greater than 1,000,000:

childMax('ChildAppID','Score', '{"ProjectValue" : {$gt :1000000} }')

 

First and Last Value Examples

Return the Score from the first matching child Record after sorting by ProfilePeriod in descending order:

childFirst('ChildAppID','Score', '{"ProjectValue" : {$gt :1000000} }', '{"ProfilePeriod":-1}')

Return the Score from the last matching child Record after sorting by DateField in ascending order:

childLast('ChildAppID','Score', '', '{"DateField":1}')

The retrieved Field does not need to be numeric. For example, the following expression returns the Status from the most recent child Record based on UpdatedOn:

childFirst('ChildAppID','Status', '', '{"UpdatedOn":-1}')

 

 

Child Record Text Aggregation Expressions

The childConcat function combines text values from multiple child Records into a single Text or Long Text Field in the parent Record.

The expression structure is:

childConcat('Separator', 'App', 'Field', 'Filter', 'SortOrder')

The parameters are:

  • Separator: The character or spacing placed between each returned value.
  • App: The identifier of the child App.
  • Field: The identifier of the child Field containing the text to return.
  • Filter: An optional filter that determines which child Records are included.
  • Sort Order: An optional value that controls the order in which the results are returned.

For example, an Actions App could contain an ActionTitle Field that combines important information about each Action:

[Title] + ' - ' + [Category] + ' - ' + [Owner] + ' - ' + [Status]

A Long Text Field in the parent Projects App could then use childConcat to list the relevant Actions.

 

Example 1: List All Actions with a Red Status

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

This returns the ActionTitle from each linked Action with a Red status. The \n separator places each Action on a new line, so the expression should be used with a Long Text Field.

 

Example 2: List Red Actions with an Importance Greater Than Two

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

This applies multiple conditions so that only Records meeting both conditions are included.

 

Example 3: Filter Using a Value from the Parent Record

The filter can be built dynamically using a Field value from the current parent Record:

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

This returns Actions whose Phase matches the CurrentPhase of the parent Project and sorts them by DueDate in descending order.

Common separator characters include:

  • \u0020 for a space
  • \u00A6 for a broken bar
  • \n for a line break in a Long Text Field

 

 

Parent Record Expressions

Parent Expressions are configured in a child App and retrieve a Field value from the Record to which the child is linked.

The parent Field is referenced using:

[Parent.ParentFieldName]

The first part, [Parent., tells Softools to access the linked parent Record. ParentFieldName must be replaced with the identifier of the Field in the parent App.

For example:

[Parent.ProjectName]

A Parent Expression runs each time the child Record is updated. This means that if the parent value has changed, the latest value will be retrieved when the child is next updated.

Linking an existing child Record to a parent also calculates its Parent Expressions using the new parent. Reparenting the Record recalculates them using the newly linked parent.

 

Supporting Records Without a Parent

If Users can create or edit Records without a parent, use isnull() to provide a fallback value. This prevents the Parent Expression from producing an error when no parent is linked.

For a parent Field containing a string value:

isnull([Parent.TextField],'')

For a parent Field containing a numeric value:

isnull([Parent.NumberField],0)

For a parent Field containing a Boolean value:

isnull([Parent.Bit],false)

If an existing child Record is unlinked from its parent, the Parent Expression recalculates using its isnull() fallback value.

The fallback should match the Field Type and represent an appropriate empty or default value.

Best practice: For reliable dependency tracking, use the Parent Field reference on its own or within an isnull() expression. Avoid combining it with other Field references or more complex functions unless the resulting dependencies have been tested.

 

 

Using Parent Expressions as Default Values

Parent references can be entered in either:

  • the Expression property; or
  • the Default Value property when Process the Default Value as an Expression is selected.

A Parent Expression configured in the Expression property recalculates whenever the child Record is updated.

A parent-based default value expression is processed when the child Record is created. It provides an initial value that can then be edited and retained independently in the child Record.

For example, the following Default Value copies the parent Project Name into a new child Record:

isnull([Parent.ProjectName],'')

Updating the parent later does not automatically reapply the default value to existing child Records.

 

 

Using Parent Defaults During CSV Import

Parent-based default value expressions can populate when new child Records are created through a CSV import.

Importing Directly into the Child App

When importing directly into the child App:

  1. Leave [ID] blank so that Softools creates a new Record.
  2. Enter a valid parent reference in [Hierarchy].
  3. Use the following hierarchy format:
ParentAppID|ParentRecordID

For example:

Project|6aaaa3352443411f50aa4567

Softools establishes the parent relationship while creating the new child Record. Parent-based default value expressions can then retrieve values from the specified parent during creation.

If [Hierarchy] is blank or does not identify a valid parent relationship, the new Record has no parent from which the default expression can retrieve a value.

 

Importing from Within a Parent Record

When importing from a child Report within a parent Record, the [Hierarchy] value in the CSV file is ignored.

All imported Records are linked to the parent Record from which the import was started. Parent-based default value expressions can therefore use that parent when new Records are created.

For full details about [ID], [Hierarchy] and the differences between the two import routes, see Importing Records via .CSV.

 

 

Keeping Child Values Synchronised with the Parent

Changing a parent Record does not automatically update values in its existing child Records.

For example, a Project App and its child Actions App may both contain a Location Field. The child Location Field could initially use the following default value expression:

isnull([Parent.Location],'')

This copies the Project’s Location when a new Action is created.

To pass later changes to existing Actions:

  1. Configure a Workflow in the parent Project App.
  2. Trigger the Workflow when the parent Location changes.
  3. Use Update Child Field Value to update the Location Field in the linked Action Records with the new parent value.

This provides an initial value when the child is created and then keeps existing child Records aligned when the parent changes.

Note: Workflow updates are processed asynchronously. If the parent is updated again before the previous child updates finish, the overlapping operations may cause a concurrency issue.

 

 

Security Considerations

Child and Parent Expressions do not calculate different results based on the viewing User’s access to the referenced Records.

For example, a User may not have permission to view individual Actions linked to a Project. However, if the User can view a child aggregation Field in the Project, they will see the result calculated from all active linked Actions.

Consider whether an aggregated or inherited value could reveal information from Records that the User cannot access directly.

 

 

Field Dependencies

Fields referenced by Child or Parent Expressions become dependencies of the Field containing the expression. Dependencies are indicated by an atom symbol in App Studio.

Review Field dependencies before changing or deleting Fields so that existing expressions and data flows are not unintentionally affected.

Make sure to select Save after making changes. Once all required changes have been completed, publish the App to the Workspace.

Related articles

Comments

0 comments

Article is closed for comments.