parentlookup function

Function Name
parentlookup

Description
Returns the value of a specified field from a parent record.

This function can also be used to return a value from a grandparent table, a great-grand parent table, etc.

Format
Note: LTP is used below as an abbreviation for link to parent field.

• parentlookup("LTP in this table", "desired Field Name in parent table")
• parentlookup("LTP in this table", "LTP in parent table", "desired Field Name in Grandparent")

Notes
The first field name entered in the function should refer to a link to parent field in the current table. To look to grandparent tables (or beyond) you should successively enter the names of link to parent fields in the function and TrackVia will follow those links from one table to the next. The last field name entered in the function specifies the value (from the desired field in the desired table) that will be returned.

Problems with your formula?  Please see our formula troubleshooting article.

Examples

  Video Demo (1 minute)

•  In a business example you would have a parent table named Customer Companies and a child table named Customer Contacts, where these tables are linked by a field named Company.  Each Company can have 1 or many Contacts.
• For each contact, you'd like to display his or her company's main street address.
  • parentlookup("Company Name", "Complete Address")

•  In an org chart example you would have a table named Employees and a self-joining link to parent field in this table named Reports To.  This arrangement allows you to track all employees in a single table, but also to reflect the hierarchy of who reports to whom.
• For each employee you'd like to display her boss' boss' phone extension (in other words, the phone extension of her boss' boss, considered a grandparent record in TrackVia terminology).
  • parentlookup("Reports To", "Reports To", "Phone Extension")

•  In a sports example you would have a child table name Players, a parent table named Teams, and a grandparent table named Divisions.  Each player belongs to a team, and each team belongs to a division.
• For each player, you'd like to display the name of his league in the Players table.
  • parentlookup("Team Name", "Division Name")

Troubleshooting

•  If you receive a syntax error
  • Check that you've properly opened and closed all parentheses used in your formula.
    • Tip: Count the number of opening "(" parentheses and make sure there's the same number of closing ")" parentheses.
• If you receive an error that includes "...parentlookup must have 2 or more field names in quotation marks..."
  • Ensure that each field name is inside quotes.
• If you receive an error that includes: "..."[field name] is not a field in this table"
  • Ensure that your function uses the name of the link to parent field in this table, not the name of that field in the parent table.
  • For example, the link to parent field may be named "Team" in the child table, and it may reference a field named "Team Name" in the parent table.
• If you receive an error that includes "...[field name] is not a field in table [parent table name]..."
  • Open a separate browser window, navigate to the parent table, and copy the name of the desired field.  Then return to the browser window where you're inputting your formula and paste the field name you copied from the parent table.