Skip to main content
All CollectionsSmartSuite FormulasUsing 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

SmartSuite allows you to connect tables through Linked Record fields, and with formulas, you can access and manipulate data stored across those links. This capability lets you create powerful, dynamic workflows and calculations that span multiple tables.

Important: You can link tables across solutions with a Linked Record.

Plan Availability

All plan types

Permissions

Editable by Solution Managers and Administrators.

Related Reading


Understanding Linked Records and Formulas

The Linked Record field connects data between tables, enabling formulas to reference fields from related records.

Example Use Case: Retrieve a list of account names associated with subtasks:

ARRAYUNIQUE([Link to Tasks].[Link to Projects].[Link to Account].[Account Name], "; ")

Important Note:

  • To maintain simplicity, formulas are limited to referencing a maximum depth of 3 tables.


Formatting References to Linked Records

Basic Syntax

To reference a Linked Record field, use the field name in square brackets:

COUNT([Linked Record Field])

The above example returns the count of linked records in the specified field.

Referencing Specific Fields in Linked Records

To target a specific field in a linked record, combine the Linked Record field and target field with a period separator:

[Linked Record Field].[Target Field]

Example: Add up values in the "Total" field of linked records:

SUM([Line Items].[Total])

SmartSuite Formula Editor Assistance

When typing a formula, SmartSuite’s formula editor updates to show available fields after typing a period (.) following the Linked Record name:

[Linked Record Field].

This feature ensures you can easily navigate and select fields from related tables.


Handling Single vs. Multiple Values

Single Entry Linked Records

  • If a Linked Record field is configured for single entries, referencing its fields returns a single value.

Multiple Entry Linked Records

  • If a Linked Record field is configured for multiple entries, referencing its fields returns a list of values.

  • Use functions that handle lists, such as:

    • SUM: Adds all values.

    • COUNT: Counts all values.

    • MIN/MAX: Finds the smallest or largest value.

    • AVG: Calculates the average.

    • SUMIF/COUNTIF/AVGIF: Applies conditions to calculations.


Examples

1. Using SUM with Linked Records

In an Invoices Table linked to a Line Items Table, calculate the total for all attached line items:

SUM([Line Items].[Total])

2. Comparing Single Values

With a single-entry Linked Record field, compare a date in a linked task to a due date in the main table:

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


Tips for Success

  • Start Small: Experiment with shallow references before building complex formulas across multiple tables.

  • Leverage Functions: Use list-handling functions like SUM or COUNT to work with multiple linked records.

  • Use the Formula Editor: Let SmartSuite guide you in selecting fields with its autocomplete and dropdown suggestions.

Did this answer your question?