Child Lookups

Child Lookups

In many applications with relational data, it's useful to aggregate data from child records into parent records. In other words, you might want to include in the parent record a count of child records, or the sum of a certain field across all children. TrackVia's child lookup functions allow you to do this.

• childcount
• childsum
• childmin
• childmax
• childcountdistinct
• childcountnonblank
• childconcatenate
• childconcatenatedistinct

Note: This video demo accompanied by these tips & examples provide an in-depth review of child lookups.

Example

Suppose you have an order tracking system that consists of two tables. The parent table is called Orders, and contains general information about the order: customer name, ship-to address, etc. The child table is called Order Line Items, and contains the individual components of the order.

Each line item has a product number, quantity, price, etc. In parent order records, you'd like an automatically-calculated number that tells you how many line items the order has. You'd also like a calculated total order amount, which is the sum of all the Price fields across the child records for any given order.

Counting child records

To calculate the number of child records, use the childcount function. First, create a calculated number field in your parent table. Give it a name like Number of line items. The formula would be:

Here, "Order Line Items" is the name of the child table you want to count records in. "Order Number" is the name of the field in that child table which links back to the parent table. (You have to provide these two inputs because TrackVia allows multiple child tables to link to a single parent table, and multiple distinct links from a child table to the same parent table.) Note that the field names are in quotation marks, which is different than the way fields are normally referenced in a formula.

When you add or remove child records for a single parent, this formula will automatically update with the count of children (though you might have to refresh your browser to see the latest number in a view or a parent record detail screen).

Summing child values

To calculate the sum of the Price field across child records, use the childsum function. First, create a calculated currency field in your parent table. Give it a name like Order Total. The formula would be:

As before, "Order Line Items" is the name of the child table you want to sum records from, and "Order Number" is the name of the field in that child table that links back to the parent table. The third input, "Total Cost", is the name of the field that you want summed across all children. This Order Total field will now be automatically updated whenever a child record is added, deleted, or changed.

Getting fancy

Child lookup functions can be combined with parent lookup functions and other formulas to create powerful applications. As an example of how these might be used together, suppose the Order Line Items table in the above example included a link-to-parent field that linked to a Products table. The price of the product would be pulled into each line item record using a calculated currency field, with a formula of parentlookup("Product","Price") (see the Parent Lookups article). Then a calculated field called Total Cost would create a subtotal for each line item, with a formula of Quantity * Price. Finally, this Total Cost (not the product's price field) is what would be summed into the parent Order record.

Child lookup functions can be used with numeric fields (numbers, currencies, or percentages), date fields, text fields, drop-down or checkbox fields, or any other TrackVia field type. Furthermore, in addition to counting and summing child records, these functions can find minimum or maximum values among children, and combine text values. Here are the other child lookup functions available:

Childmin

This returns the smallest value of a field among all child records. For example, to find the earliest ship date among the line items in our order, we'd use