Child Functions

Child Functions


Click here to download a complete list of formulas.

TrackVia has built-in functions to retrieve data from linked child records.  You will see these functions listed when clicking in the Result Formula box once a calculated field is added to your table.  After selecting a child function, you can click the blue highlighted "child_field" text to display a list of available child fields to include in the formula (see image below). *Note: The two child "concatenate" functions also require a delimiter to be entered within quotation marks.


Function Description
childconcatenate()


childconcatenate({child field reference}, "delimiter")
The childconcatenate() function combines multiple values from the linked records field in a child table, separated by the delimiter.  After selecting the childconcatenate() function in the list of options presented in the Result Formula box, you will see two entries are needed within the function: the reference to the child name (which you can also find in the list) and the delimiter (the character you would like to use to separate each child record's value) which needs to be entered within quotation marks.  

If each child field needs to be added to the parent with a line break separating the entries, then a line break can be used as the delimiter.  The delimiter would be: "\n".  Multiple line breaks can be expressed as: "\n\n".

Another example would be if you would like to see a comma-separated list of products that one of your clients has purchased where the Clients table is a parent of the Orders table.  You can use the following formula to display this information in a calculated text field in the Clients table:

childconcatenate({Orders}.{Customer Link}.{Product}, ", ")

childconcatenatedistinct()

childconcatenatedistinct({child field reference}, "delimiter")
This function works the same as the function above, except it will only return the unique values from the child table's field.  For example, if the results from the formula above return "Dell, Mac, Samsung, Dell, HP", the childconcatenatedistinct() function will only return "Dell, Mac, Samsung, HP".
childconcatenateorderby()

childconcatenateorderby({child field reference}, "delimiter", "Sort")
This function works the same as the childconcatenate() function above, except it will sort all records in a specified order. There are two options for sort: ASC (ascending) and DESC (descending). 

For example, if childconcatenateorderby({Item Type}.{Link to Item Type}.{Make},”, ")
returns:
"Samsung, HP, Dell, Mac",
use
childconcatenateorderby({Item Type}.{Link to Item Type}.{Make},”, ","ASC")
to return the same values sorted from A-Z:
"Dell, HP, Mac, Samsung".

count()

count({child field reference})
The count() function requires choosing a reference to a field in the child table.  For each record in the parent table, it will return the number of records linked where the field chosen is not blank.  In order to return a count for every child record linked to a parent record, you can utilize the field that is linking the two tables together.  Here is an example:

count({Orders}.{Customer Link}.{Customer Link})

countdistinct()

countdistinct({child field reference})
This function works the same as the count() function above, except it will only return the unique values from the child table's field.
sum()

sum({child field reference})
The sum() function allows you to add values from linked child records where each value in the field referenced is a number, currency or percent.  For example, if you would like to display the total amount a customer has spent from multiple orders, you can use the following formula:

sum({Orders}.{Customer Link}.{Total Amount})
average()

average({child field reference})
Similar to the sum() function, the average() function returns the average value from linked child records where each value in the field referenced is a number, currency or percent.  It sums the values and divides it by the number of linked child records.  The following formula can be used to return the average amount a customer spends per order:

average({Orders}.{Customer Link}.{Total Amount})
max()

max({child field reference})
Returns the maximum value from a child table's field.

max({Orders}.{Customer Link}.{Total Amount})
min()

min({child field reference})
Returns the minimum value from a child table's field.

min({Orders}.{Customer Link}.{Total Amount})
stdev()

stdev({child field reference})
Returns the sample standard deviation from a child table's field.

stdev({Orders}.{Customer Link}.{Total Amount})
stdevp()

stdevp({child field reference})
Returns the population standard deviation from a child table's field.

stdevp({Orders}.{Customer Link}.{Total Amount})

Feedback and Knowledge Base