Parent Lookups

Parent Lookups

Note: You should read the main article Using Relational Fields before reading this one.

TrackVia's parentlookup() function lets you create formulas that pull additional data from the parent record into a child record, beyond the one field being linked to.

For example, suppose you have a Contacts child table that links to a Companies parent table. You might want your Contacts records to display the website of the company from the Companies table.

Here "Company" is the name of the link-to-parent field in the Contacts table, and "website" is the name of the parent field you want to retrieve. Note that the field names are in quotation marks, which is different than the way fields are normally referenced in a formula.

When you do this, each contact record that's linked to a company will automatically contain the company's website. TrackVia will keep this information up to date if the parent record is edited, or if the child is linked to a new parent.

This technique is only necessary if you want the company website to appear in the contact record's detail page, or if you want to search on the company website in the Contacts table. If you simply want the company website to appear in a view of orders, you can do that without using parentlookup (see the main article Using Relational Fields, in particular the section about parent/child views). You can also use parent fields in mail merge documents and email campaigns without using a parentlookup formula.

The parentlookup function can also pull information from a "grandparent" table. For example, if the Company table is linked to a States table, and you wanted to include the state capital of each company in the Contact record, the formula would be

You can continue the list of linked fields as long as you like.

When you update any parent (or grandparent) records, TrackVia will reapply the formulas in the descendent records to keep all data consistent at all times.

Technical details.

The technical explanation of the parentlookup() function is as follows. The syntax is:

The function returns the value of a field in a record linked to by one or more link to parent record fields. That is, it pulls a parent record's value, or a grandparent record's value, and so on, into the child record. The inputs to this function are fields names enclosed in quotation marks (note this differs from how fields are referred to by all other functions). The first field name must refer to a link-to-parent-record field in the current table. If additional link-to-parent-record fields are provided, TrackVia will follow those links from one table so the next. The last field name specifies the individual value that will be returned.

multiple parentlookup functions can be used in a single formula, or combined with if/then logic, concatenation, and so on. The inputs to each instance of parentlookup() must be static values however, you cannot use other functions or formula logic to dynamically determine the field name inputs to parentlookup().