Formula fields provide SmartSuite Solution Owners with a lot of power and flexibility, but there are a few things that you should keep in mind when planning to use them in your solutions.
Note: Formula fields can reference other Formula fields.
Field Limit Note:
The limit for complex field types in Tables vary based on plan type & include:
Free / Team Plan
30 Linked Records
30 Lookups
30 Total Calculated fields, including: Formulas, Rollups, Counts
Professional Plan
50 Linked Records
50 Lookups
50 Total Calculated fields, including: Formulas, Rollups, Counts
Enterprise / Signature Plan
100 Linked Records
100 Lookups
100 Total Calculated fields, including: Formulas, Rollups, Counts
Limitations to conditional logic (IF statements and similar)
Any conditional logic statement such as IF or SUMIF must return the same type for every possible response. In other words, your IF statement cannot return a date for its TRUE response and a number for its FALSE response.
This rule applies to nested conditional functions (nested IF statements for example). If one response returns a date, every possible response must return a date or the function will generate an error.
Lookup Fields Always Return Lists
Lookup type fields are always returned as arrays or lists of values. If you get an error that mentioned "list of . . ." you will need to use an aggregate.
Common aggregates for text results are CONCAT or ARRAYUNIQUE
Common aggregates for dates are MIN or MAX
Common aggregates for numbers are AVG, SUM, MIN, MAXIf your Linked Record field can only link to a single record, you can reference them through the appropriate Linked Record field without using an aggregate.
โ
[Linked Record].[Field that is your Lookup target]
Dot Notation Table Depth Limit
SmartSuite's "Dot Notation" shown above is a very unique feature that allows you to look through linked records and pull fields through into the calculation. To maintain simplicity and avoid over-complication, we limit formula references to a maximum depth of 4 tables. For example, the formula below retrieves a list of account names associated with a subtask, demonstrating this limit:
ARRAYUNIQUE( [Link to Tasks].[Link to Projects].[Link to Contacts].[Link to Account].[Account Name], "; " )
TODAY and NOW Functions
The TODAY and NOW formula functions are restricted to our paid and trial plan types.
Other unsupported fields
There are a few additional fields that aren't supported in formulas. They include the following types:
Files and Images
Signature
SmartDoc