All Collections
SmartSuite Formulas
Using Formulas
Reference Data from other Tables
Reference Data from other Tables

Use data stored in other Tables within your formulas

Peter Novosel avatar
Written by Peter Novosel
Updated over a week ago

The Linked Record field type allows you to connect your Tables together, and SmartSuite formulas allow you to access the data stored across those links.

We recommend that you have a good understanding of SmartSuite formula basics before creating formulas that reference Linked Records. Take a look at our Create and Edit Formulas article for more information.


Formatting References to Linked Records

You can include a linked record in your formula by referencing it just like other fields, with the field name in square brackets. You can use the Linked Record name by itself for functions that are not field-specific, such as COUNT:

COUNT( [Linked Record Field] )

The result of the above example is a number representing the total count of records referenced in the Linked Record field.

What if you want to add up the values of a field in those Linked Records? That's going to require specifying the target field. SmartSuite supports this with the following format, combining the Linked Record field and the target field with a period separator:

[Linked Record Field].[Target Field]

To help you navigate through the related record's fields, SmartSuite's formula editor updates the list of available fields when you type a period after the linked record's name, like this:

[Linked Record Field].

The "Linked Fields" display in the screenshot below have been updated to show the fields in the linked Table:

Handling Single or Multiple Values

Depending on how your Linked Record field is configured, referencing a field in the linked Table will return either a single value or a list of values:

  • Linked Record set to Single entry: returns a single value

  • Linked Record set to Multiple entry: returns a list of values

If the Linked Record returns a list you have to use a function that can accept that list, such as:

  • SUM

  • SUMIF

  • COUNT

  • COUNTIF

  • MIN

  • MAX

  • MEDIAN

  • AVG

  • AVGIF

  • STDEV


Examples

Using SUM

Imagine you have an Invoices Table with a Linked Record to the Line Items Table. If you want to add up the Total field for all of the attached line items, you just point your SUM function at that field, like this:

SUM( [Line Items].[Total] )

Single Values

A single select Linked Record field will return the value of the linked record (if one exists!), allowing you to reference the linked record's field - just like it was part of the same record.

Here's an example, comparing a date in a Linked Record to a due date in the main Table:

DATEDIFF( [Link to Tasks].[Target Date], [Due Date], "days" )

Did this answer your question?