This article serves as a reference to all available functions and operators within SmartSuite's Formula field type.
Visit our Formula Operators & Functions solution template for those that are hands-on learners. It has basic formulas already set up so you can see how the information changes as you modify the example data, or even use the examples to create your own more complex formulas.
To learn more about the Formula field, check out the Formula Field article where you'll find everything you need to know about the field including videos on how to write complex formulas.
Operators
-
(SUBTRACT) Return the difference between two numbers
-
(SUBTRACT) Return the difference between two numbers
Usage:
Number1 - Number2
Example:
6 - 2 = 4[Numeric Field] - 2 → if [Numeric Field] is 4, returns 2
!=
(NOT EQUAL) Returns TRUE if left (first) value is not equal to the right (second) value and FALSE otherwise
!=
(NOT EQUAL) Returns TRUE if left (first) value is not equal to the right (second) value and FALSE otherwise
Usage:
Value 1 != Value 2
Example:
1 != 2 → returns TRUE
”Test" != "Test"→ returns FALSE
[Rating] != "Poor" → returns TRUE if the value of [Rating] field is not exactly the text "Poor”
*
(MULTIPLY) Return the the product of two numbers
*
(MULTIPLY) Return the the product of two numbers
Usage:
Number1 * Number2
Example:
6 * 2 = 12
[Numeric Field] * 2 → if [Numeric Field] is 4, returns 8
/
(DIVIDE) Divides two numbers, returning their quotient
/
(DIVIDE) Divides two numbers, returning their quotient
Usage:
Number1 / Number2
Example:
6 / 2 = 3
[Numeric Field] / 2 → if [Numeric Field] is 8, returns 4
+
(ADD) Add together two numbers or concatenate text values
+
(ADD) Add together two numbers or concatenate text values
Usage:
Number1 + Number2
Text1 + Text2
Example:
[First Name] + [Last Name] → "First Name Last Name"
"One" + "Two" + "Three" → "OneTwoThree”
<
(LESS THAN) Returns TRUE if left (first) value is less than right (second) value and FALSE otherwise
<
(LESS THAN) Returns TRUE if left (first) value is less than right (second) value and FALSE otherwise
Usage:
Value 1 < Value 2
Example:
1 < 2 → returns TRUE
[Score] < 10 → returns TRUE if the value of [Score] field is below 10
<=
(LESS THAN OR EQUAL TO) Returns TRUE if left (first) value is less than or exactly equal to right (second) value and FALSE otherwise
<=
(LESS THAN OR EQUAL TO) Returns TRUE if left (first) value is less than or exactly equal to right (second) value and FALSE otherwise
Usage:
Value 1 <= Value 2
Example:
1 <= 2 → returns TRUE
2 <= 2 → returns TRUE
3 <= 2 → returns FALSE
[Score] <= 10 → returns TRUE if the value of [Score] field is 10 or less
==
(EQUALS) Returns TRUE if compared values are equal and FALSE otherwise
==
(EQUALS) Returns TRUE if compared values are equal and FALSE otherwise
Usage:
Number1 == Number2
Text1 == Text2
Date1 == Date2
Example:
1 == 2 → returns FALSE
[Status] == "Closed" → returns TRUE if status is exactly "Closed”
>
(GREATER THAN) Returns TRUE if left (first) value is greater than right (second) value and FALSE otherwise
>
(GREATER THAN) Returns TRUE if left (first) value is greater than right (second) value and FALSE otherwise
Usage:
Value 1 > Value 2
Example:
1 > 2 → returns FALSE
[Score] > 10 → returns TRUE if the value of [Score] field is over 10
>=
(GREATER THAN OR EQUAL TO) Returns TRUE if left (first) value is greater than or exactly equal to right (second) value and FALSE otherwise
>=
(GREATER THAN OR EQUAL TO) Returns TRUE if left (first) value is greater than or exactly equal to right (second) value and FALSE otherwise
Usage:
Value 1 >= Value 2
Example:
1 >= 2 → returns FALSE
2 >= 2 → returns TRUE
3 >= 2 → returns TRUE [Score] >= 10 → returns TRUE if the value of [Score] field is 10 or more
Functions
ABS
Returns the absolute value of a number
ABS
Returns the absolute value of a number
Usage:
ABS( Number )
Example:
ABS(-2) = 2
ABS([Field1]) → if [Field1] is -25, result is 25
AND
Returns TRUE if all provided arguments are logically true, otherwise returns FALSE
AND
Returns TRUE if all provided arguments are logically true, otherwise returns FALSE
Usage:
AND( [Score] > 0, [Score] <= 10 )
AND( SUM([Invoice].[Total]) > 100, [Customer Tier] = "Premium" )
Example:
IF( AND( [Has Support Contract] = "Yes", [Customer Tier] = "Premium"), "Premium Support", "Standard Support" ) → if [Has Support Contract] is "Yes" and [Customer Tier] is "Premium" then function displays "Premium Support" otherwise displays "Standard Support"
AND( [Status] = "Final", [Invoice].[Total] > 0 ) → if [Status] is "Final" and [Invoice].[Total] is 0 returns FALSE
ARRAYJOIN
Joins an array of values into a string with a separator
ARRAYJOIN
Joins an array of values into a string with a separator
Usage:
ARRAYJOIN( [Linked Record].[Assigned To] , Separator, Number of items to return )
Example:
ARRAYJOIN( [Linked Record], “;“ ) → returns a string of Linked Record Titles, separated by “;“ ARRAYJOIN( [Linked Record].[Assigned To], “;“, 2 ) → returns a string of first two Assigned To values from a specified Linked Record, separated by “;“
ARRAYUNIQUE
Joins an array of values into a string with a separator, showing only unique values
ARRAYUNIQUE
Joins an array of values into a string with a separator, showing only unique values
Usage:
ARRAYUNIQUE( [Linked Record].[Assigned To] , Separator, Number of items to return )
Example:
ARRAYUNIQUE( [Linked Record].[Assigned To], “;“, 2 ) → returns a string of first two unique Assigned To values from a specified Linked Record, separated by “;“
AVG
Returns the average (arithmetic mean) of a series of numbers
AVG
Returns the average (arithmetic mean) of a series of numbers
Usage:
AVG( Number1, Number2, Number3, … )
Example:
AVG(2, 5, 7) = 4.67
AVG([Field1], [Field2]) → if [Field1] is 2 and [Field2] is 5 returns 3.5
AVGIF
Returns the average (arithmetic mean) of a series of numbers from a referenced field in a linked record that meet specified criteria
AVGIF
Returns the average (arithmetic mean) of a series of numbers from a referenced field in a linked record that meet specified criteria
Usage:
AVGIF( Criteria, [Linked Record Field], [Target Field] )
Example:
AVGIF( [Invoices].[Tax] > 0, [Invoices], [Tax] ) → if [Invoices] has 6 linked records and 3 of those have tax values greater than 0, will return the average of those 3 [Tax] values
BLANK
Returns a blank value. Does not require any arguments.
BLANK
Returns a blank value. Does not require any arguments.
Usage:
BLANK()
Example:
IF([Price], [Price] + 10, BLANK()) → If the cell containing the Price is not empty, it adds 10 to the Price. If the cell is empty, the BLANK function ensures that the result is a blank value.
CASE
Returns a specified value when the first condition is met
CASE
Returns a specified value when the first condition is met
Usage:
CASE( Condition, Match1, Result1, Match2, Result2, ..., Default )
Example:
CASE( [Customer Tier], "Premium", "Premium Support", "Enhanced", "Enhanced Support", "Standard Support" ) → If [Customer Tier] is Premium function displays "Premium Support", if [Customer Tier] is Enhanced displays "Enhanced Support", otherwise displays the default of "Standard Support
CEILING
Returns the rounded-up value of the specified number to the nearest multiple of the given significance.
CEILING
Returns the rounded-up value of the specified number to the nearest multiple of the given significance.
Usage:
CEILING(Number, Significance)
Example:
CEILING(7.8, 2) → returns 8. This example rounds up 7.8 to the nearest multiple of 2, resulting in 8.
CEILING(15.5, 5) → returns 20. This example rounds up 15.5 to the nearest multiple of 5, resulting in 20.
CEILING(-10, -3) → returns -9. This example rounds up -10 to the nearest multiple of -3, resulting in -9.
CHECKLIST_COUNT
Returns a count of checklist items that match a specified criteria.
CHECKLIST_COUNT
Returns a count of checklist items that match a specified criteria.
Usage:
CHECKLIST_COUNT( [Checklist Field], Criteria )
Example:
CHECKLIST_COUNT( [Checklist], [Checklist].[Completed] = True ) → Returns a count of completed Items in a given Checklist field. Note that True/False are boolean operators, not strings.
CHECKLIST_COUNT([Checklist], [Checklist].[Assignee] = “John Doe“) → Returns a count of items assigned to John Doe.
CHECKLIST_COUNT([Checklist], [Checklist].[Due Date] > TODAY()) → Returns a count of items that are due after today.
CHECKLIST_COUNT([Checklist], [Checklist].[Due Date] < TODAY() AND [Checklist].[Completed] = False) → Returns a count of items that are due or before today and have not been completed.
CONCAT
Combines multiple text values into one
CONCAT
Combines multiple text values into one
Usage:
CONCAT( Text1, Text2, ... )
Example:
CONCAT( "This", "is", "a", "test" ) → returns "Thisisatest" as text
CONCAT( RECORD_ID(), "-", [Name] ) → if [Name] is Test returns "00000-Test" (where 00000 is the record id)
CONTAINS
Returns TRUE if expression contains the defined text and FALSE otherwise
CONTAINS
Returns TRUE if expression contains the defined text and FALSE otherwise
Usage:
CONTAINS( Expression, Text )
Example:
CONTAINS( [Owner], "Jon" ) → if [Owner] field contains the text "Jon" then function returns TRUE
OR( CONTAINS( [Owner], "Jon" ), CONTAINS( [Owner], "Peter" ) ) → if [Owner] field contains either the text "Jon" OR the text "Peter" then function returns TRUE
COUNT
Returns the count of items in a referenced field, or the count of records in a linked record field
COUNT
Returns the count of items in a referenced field, or the count of records in a linked record field
Usage:
COUNT( [Linked Record Field] )
COUNT( [Linked Record Field].[Field] )
COUNT( [Multiple Select Field] )
Example:
COUNT( [Invoices] ) → if [Invoices] has 6 linked records, returns 6
COUNT( [Invoices].[SKU] ) → if there are a total of 3 unique [SKU] appearing 12 times across all linked [Invoices], returns 12
COUNT( [Impacted Areas] ) → if [Impacted Areas] multiple select list has 2 selections, returns 2
COUNT_DISTINCT
Returns the count of distinct (unique) items in a referenced field, or the count of distinct records in a linked record field
COUNT_DISTINCT
Returns the count of distinct (unique) items in a referenced field, or the count of distinct records in a linked record field
Usage:
COUNT_DISTINCT( [Linked Record Field] )
COUNT_DISTINCT( [Linked Record Field].[Field] )
COUNT_DISTINCT( [Multiple Select Field] )
Example:
COUNT_DISTINCT( [Contacts] ) → if [Contacts] has 3 linked records, returns 3
COUNT_DISTINCT( [Invoices].[SKU] ) → if there are a total of 3 unique [SKU] appearing 12 times across all linked [Invoices], returns 3
COUNT_DISTINCT( [Impacted Areas] ) → if [Impacted Areas] multiple select list has 2 selections, returns 2
COUNTA
Count the number of non-empty values. This function counts both numeric and text values.
COUNTA
Count the number of non-empty values. This function counts both numeric and text values.
Usage:
COUNTA( TextOrNumber1, TextOrNumber2, ... )
Example:
COUNTA([Linked Record].[Number], [Linked Record].[Text]) → Counts the non-empty values in specified fields of a Linked Record. In case if Linked Record field has multiple records linked, each non-empty [Linked Record].[Number] and [Linked Record].[Text] will be counted separately.
COUNTALL
Count the number of all elements including text and blanks. This function counts both numeric and text values.
COUNTALL
Count the number of all elements including text and blanks. This function counts both numeric and text values.
Usage:
COUNTALL( TextOrNumber1, TextOrNumber2, ... )
Example:
COUNTALL(1, 2, 3, "", "four") → returns 5 Counts the number of all elements including text and blanks. COUNTALL([Linked Record].[Number], [Linked Record].[Text]) → Counts the number of blank and non-blank values in specified fields of a Linked Record. In case if Linked Record field has multiple records linked, each non-empty [Linked Record].[Number] and [Linked Record].[Text] will be counted separately.
COUNTIF
Returns a count of items in a referenced field, or the count of records in a linked record field that match specified criteria
COUNTIF
Returns a count of items in a referenced field, or the count of records in a linked record field that match specified criteria
Usage:
COUNTIF( Criteria, [Linked Record Field] )
COUNTIF( Criteria, [Multiple Select Field] )
Example:
COUNTIF( [Invoices].[Status] == "Open", [Invoices] ) → if [Invoices] has 6 linked record and 4 have a [Status] value of "Open", returns 4
COUNTIF( [Has Impact] == "Yes", [Impacted Areas] ) → if [Impacted Areas] multiple select list has 2 selections and [Has Impact] is "Yes", returns 2
COUNTIF_DISTINCT
Returns a count of distinct (unique) items in a referenced field, or the count of distinct records in a linked record field that match specified criteria
COUNTIF_DISTINCT
Returns a count of distinct (unique) items in a referenced field, or the count of distinct records in a linked record field that match specified criteria
Usage:
COUNTIF_DISTINCT( Criteria, [Linked Record Field] )
COUNTIF_DISTINCT( Criteria, [Linked Record Field].[Field] )
COUNTIF_DISTINCT( Criteria, [Multiple Select Field] )
Example:
COUNTIF_DISTINCT( [Trouble Tickets].[Asset Type] == "Laptop", [Trouble Tickets] ) → if 12 [Trouble Tickets] out of 20 have an [Asset Type] of "Laptop" returns 12
COUNTIF_DISTINCT( [Trouble Tickets].[Asset Type] == "Laptop", [Trouble Tickets].[Asset] ) → if 12 [Trouble Tickets] out of 20 have an [Asset Type] of "Laptop" and there are 5 unique assets, returns 5
COUNTIF_DISTINCT( [Has Impact] == "Yes", [Impacted Areas] ) → if [Impacted Areas] multiple select list has 2 selections and [Has Impact] is "Yes", returns 2 - if [Impacted Areas] multiple select list has 2 selections and [Has Impact] is "No", returns 0
DATE
Converts a string to a date value
DATE
Converts a string to a date value
Usage:
DATE( String )
Example:
DATE( "1 Jan 2021" ) → returns 1/1/2021 as a date value
IF( IS_NULL( [Target Date] ), DATEADD( [Start Date], 30, "days" ), DATE( [Target Date] ) ) → if [Target Date] text field is empty returns a date 30 days after [Start Date], otherwise returns contents of [Target Date] as a date value
DATE_FORMAT
Returns a date formatted in the specified way
DATE_FORMAT
Returns a date formatted in the specified way
Usage:
DATE_FORMAT( Date, Format )
Format is one of the following:
∙ compact - 4/14/2021
∙ standard - Apr 14, 2021
∙ long - Sun, Apr 14, 2021
Example:
DATE_FORMAT( "01-01-2020", "compact" ) → returns 1/1/2020
DATE_FORMAT( "01-01-2020", "standard" ) → returns Jan 1, 2020
DATE_FORMAT( "01-01-2020", "long" ) → returns Weds, Jan 1, 2020
DATEADD
Adds a specified number of hours, days, weeks, months or years to a date value
DATEADD
Adds a specified number of hours, days, weeks, months or years to a date value
Usage:
DATEADD( Date, Count, Unit )
Unit is one of the following:
∙ minutes
∙ hours
∙ days
∙ weeks
∙ months
∙ years
Example:
DATEADD( [Target Date], 10, "days" ) → if [Target Date] is 1/1/2021 returns 1/11/2021 as a date value
IF( IS_NULL( [Due Date] ), DATEADD( [Start Date], 10, "days" ), DATE( [Due Date] ) ) → if [Due Date] text field is empty returns a date 10 days after [Start Date], otherwise returns contents of [Due Date] as a date value
DATEDIFF
Returns the difference between two dates as a number representing specified date / time units
DATEDIFF
Returns the difference between two dates as a number representing specified date / time units
Usage:
DATEDIFF( Date1, Date2, Unit )
Unit is one of the following:
∙ minutes
∙ hours
∙ days
∙ weeks
∙ months (uses 30 days for calculation)
∙ years
Example:
DATEDIFF( "01-01-2020", "01-02-2020", "days" ) → returns the number 1
DATEDIFF( "01-01-2020", "01-02-2020", "weeks" ) → returns the number 0.14
DATETIME_FORMAT
Returns a date and time formatted in the specified way
DATETIME_FORMAT
Returns a date and time formatted in the specified way
Usage:
DATETIME_FORMAT( Date, Format )
Format is one of the following:
∙ compact - 4/14/2021
∙ standard - Apr 14, 2021
∙ long - Sun, Apr 14, 2021
Example:
DATETIME_FORMAT( "01-01-2020", "compact" ) → returns 1/1/2020
DATETIME_FORMAT( "01-01-2020", "standard" ) → returns Jan 1, 2020
DATETIME_FORMAT( "01-01-2020", "long" ) → returns Weds, Jan 1, 2020
DAY
Returns the numeric day of the month from a date expression
DAY
Returns the numeric day of the month from a date expression
Usage:
DAY( Date )
Example:
DAY( "01-01-2020" ) → returns 1
DAY( "01-21-2020" ) → returns 21
ENCODE_URL_COMPONENT
Returns the input string with certain characters replaced by their encoded equivalents, making it suitable for constructing URLs or URIs. The characters - _ . ~ are not encoded.
ENCODE_URL_COMPONENT
Returns the input string with certain characters replaced by their encoded equivalents, making it suitable for constructing URLs or URIs. The characters - _ . ~ are not encoded.
Usage:
ENCODE_URL_COMPONENT(component_string)
Example:
ENCODE_URL_COMPONENT("chicken & waffles") → returns "chicken%20%26%20waffles" This example encodes the string "chicken & waffles" for use in a URL, replacing the space with %20 and the ampersand with %26.
ENCODE_URL_COMPONENT("hello_world") → returns "hello_world" The function leaves the string "hello_world" unchanged as the characters - _ . ~ are not encoded.
EVEN
Rounds a number up to the nearest even integer.
EVEN
Rounds a number up to the nearest even integer.
Usage:
EVEN( Number )
Example:
EVEN(3) → returns 4. This example rounds 3 up to the nearest even integer, resulting in 4.
EVEN(5.7) → returns 6. This example rounds 5.7 up to the nearest even integer, resulting in 6.
EVEN(-3) → returns -2. This example rounds -2 up to the nearest even integer, resulting in -2.
EXP
Calculates the exponential value of a given number. Precision is optional and defaults to 2.
EXP
Calculates the exponential value of a given number. Precision is optional and defaults to 2.
Usage:
EXP(Number, Precision)
Example:
EXP(1) → returns 2.72 This example calculates 'e' raised to the power of 1, resulting in the approximate value of 2.72.
EXP(0) → returns 1 This example calculates 'e' raised to the power of 0, resulting in 1.00
EXP(-2,3) → returns 0.135 This example calculates 'e' raised to the power of -2, resulting in the approximate value of 0.135 with a precision of three decimal places
FIND
Returns a number that is the offset of the matched string’s first instance. If no match is found, returns -1Start Position defines where to start the search (defaults to 0 if not specified). End Position defines where to end the search (defaults to the end of the string if not specified).
FIND
Returns a number that is the offset of the matched string’s first instance. If no match is found, returns -1Start Position defines where to start the search (defaults to 0 if not specified). End Position defines where to end the search (defaults to the end of the string if not specified).
Usage:
FIND(String to Find, Where to Search, Start Position, End Position)
Example:
FIND(“sweet“, “Home, sweet home“) → returns a number of 6
FIND(“home“, “Home, sweet home“, 5) → returns a number of 12
FIND(“e“, “Home, sweet home“, 6, 12) → returns a number of 8
FIND(“x“, “Home, sweet home“) → returns a number of -1
FIRST
Returns the first value from a list.
FIRST
Returns the first value from a list.
Usage:
FIRST( Expression )
Example:
FIRST( [Categories] ) → if [Categories] is a multi-select list with selected values Value1, Value2 and Value3, returns "Value1" as text
FLOOR
Returns the rounded-down value of the specified number to the nearest multiple of the given significance.
FLOOR
Returns the rounded-down value of the specified number to the nearest multiple of the given significance.
Usage:
FLOOR(Number, Significance)
Example:
FLOOR(7.8, 2) → returns 6. This example rounds down 7.8 to the nearest multiple of 2, resulting in 6.
FLOOR(15.5, 5) → returns 15. This example rounds down 15.5 to the nearest multiple of 5, resulting in 15.
FLOOR(-10, -3) → returns -12. This example rounds down -10 to the nearest multiple of -3, resulting in -12.
GET_LIST
Sorts and filters related records and returns a list of values of a specified field from those related records.
GET_LIST
Sorts and filters related records and returns a list of values of a specified field from those related records.
Usage:
GET_LIST( [Related Record].[Filter Condition], [Related Record].[Field to Return], [Related Record].[Field to Sort By], [Sorting Order])
Example:
ARRAYJOIN(GET_LIST([LR].[Status] = “Complete“, [LR].[Title], [LR].[Number], “ASC“), “; “) → Creates a list of Titles values from records that are of “Complete” status, sorted by the Number field. Values of the list are separated with a “;“.
SUM(TOP(GET_LIST([Sub-Items].[Number] > 100, [Sub-items].[Number], [Sub-Items].[Date]), 3)) → Returns the sum of Numbers the three most recent Sub-Items that have Number greater than 100.
ARRAYJOIN(TOP(GET_LIST([Checklist].[Due Date] < TODAY(), [Checklist].[Assignee], [Checklist].[Due Date], “DESC“), 3)) → Returns a list of names of members assigned to items with Due Date in the past. The list is limited to three most recent items.
HOUR
Returns the numeric hour of the day from a datetime expression
HOUR
Returns the numeric hour of the day from a datetime expression
Usage:
HOUR( Date )
Example:
HOUR( "01-01-2020 01:10:11" ) → returns 1
HOUR( "01-21-2020 02:11:12" ) → returns 2
HOUR( "01-21-2020 13:11:12" ) → returns 13
IF
Returns Value 1 if a logical expression is "TRUE", and Value 2 if it is "FALSE”
IF
Returns Value 1 if a logical expression is "TRUE", and Value 2 if it is "FALSE”
Usage:
IF( Expression, Value 1, Value 2 )
Example:
IF( [Percent Complete] == 100, "Completed", "In Progress" ) → displays "Completed" if [Percent Complete] equals 100 and otherwise displays "In Progress"
IF( [Status] == "Approved", "Closed", IF( [Status] == "Pending Approval", "Review", "In Progress" ) ) → displays "Closed" if [Status] is "Approved", "Review" if it is "Pending Approval", and "In Progress" otherwise
IFERROR
Returns a specified value if the calculation generates an error
IFERROR
Returns a specified value if the calculation generates an error
Usage:
IFERROR( Expression, Error Value )
Example:
IFERROR( 1 / 0, "Divide by zero!" ) → returns the text "Divide by zero!"
IFERROR( [Price] * [Tax], 0 ) → returns 0 if there is an error in the calculation
IFNONE
Returns a specified value if evaluated expression is null or empty
IFNONE
Returns a specified value if evaluated expression is null or empty
Usage:
IFNONE( Expression, Value )
Example:
IFNONE( [Owner], "No Owner" ) → displays "No Owner" if [Owner] field is empty
IS_NOT_NULL
Returns TRUE if expression is not null or empty and FALSE otherwise
IS_NOT_NULL
Returns TRUE if expression is not null or empty and FALSE otherwise
Usage:
IS_NOT_NULL( Expression )
Example:
IF( IS_NOT_NULL( [Owner] ), "Owner is " + [Owner], "No Owner" ) → displays "No Owner" if [Owner] field is empty, and "Owner is" [Owner] otherwise
LAST_MODIFIED_BY
Returns a name of a member who last modified a value of a specified field
LAST_MODIFIED_BY
Returns a name of a member who last modified a value of a specified field
Usage:
LAST_MODIFIED_BY([Field name])
Example:
LAST_MODIFIED_BY([Status]) → returns a name of a member who modified a value of a Status field last
LAST_MODIFIED_BY([Status], [Priority]) → returns a name of a member who was the last to modify values of Status and Priority fields
LAST_MODIFIED_TIME
Returns a date and a time when a value of the specified field was last changed
LAST_MODIFIED_TIME
Returns a date and a time when a value of the specified field was last changed
Usage:
LAST_MODIFIED_TIME([Field name])
Example:
LAST_MODIFIED_TIME([Status]) → returns the date and time when a value of the Status field was last changed
LAST_MODIFIED_TIME([Status], [Priority]) → returns the latest modification date/time value for the Status and Priority fields
LEFT
Returns the first n characters from specified text
LEFT
Returns the first n characters from specified text
Usage:
LEFT( Text, Characters )
Example:
LEFT( "Testing", 4 ) → returns "Test" as text
LEFT( ("This is " + "a test"), 9 ) → returns "This is a" as text
LENGTH
Returns the numeric character count of specified text
LENGTH
Returns the numeric character count of specified text
Usage:
LENGTH( Text )
Example:
LENGTH( "Testing" ) → returns 7
LENGTH( "This is " + "a test" ) → returns 14
LIKE
Returns TRUE if expression contains specified pattern
LIKE
Returns TRUE if expression contains specified pattern
Usage:
LIKE( Expression, Pattern )
Example:
LIKE( [Owner], "J*" ) → returns TRUE if [Owner] field contains a string starting with the letter J
LIKE( [Assigned To], "Ol?g" ) → returns TRUE if [Assigned To] field contains a string starting with "Ol" and ending in "g" with any character in between
The following patterns are supported by the LIKE function:
LOG
Returns the logarithm of the specified number with the specified base.
LOG
Returns the logarithm of the specified number with the specified base.
Usage:
LOG(Number, Base, Precision)
Example:
LOG(8) → returns 2.07 This example calculates the natural logarithm (base 'e') of 8 using default precision (2), resulting in the approximate value of 2.07.
LOG(100, 10) → returns 2 This example calculates the logarithm of 100 with a base of 10, resulting in 2.
LOWER
Returns specified text in lower case
LOWER
Returns specified text in lower case
Usage:
LOWER( Text )
Example:
LOWER( "Testing" ) → returns "testing"
LOWER( "This is " + "a Test" ) → returns "this is a test"
MAX
Returns the maximum numeric value from a series of numbers
MAX
Returns the maximum numeric value from a series of numbers
Usage:
MAX( [Field1], [Field2], ... )
MAX( [Linked Record Field].[Field] )
Example:
MAX( [Invoices].[Total] ) → if [Invoices] has 6 linked records with the highest [Total] value of 10, returns 10
MAX( [Count A], [Count B] ) → if [Count A] is 3 and [Count B] is 4, returns 4
MEDIAN
Returns the median ("midpoint") value of a series of numbers
MEDIAN
Returns the median ("midpoint") value of a series of numbers
Usage:
MEDIAN( [Number1], [Number2], ... )
MEDIAN( [Linked Record Field].[Number1] )
Example:
MEDIAN( [Invoices].[Total] ) → if [Invoices] has 4 linked records with [Total] values of 1, 2, 3 and 4, returns 2.5
MEDIAN( [Val1], [Val2], [Val3] ) → if [Val1] is 1 and [Val2] is 2 and [Val3] is 3, returns 2
MID
Extracts a substring from a text string based on specified start and length parameters.
MID
Extracts a substring from a text string based on specified start and length parameters.
Usage:
MID(Where to Search, Start Position, Length)
Example:
MID(“Home, sweet home“, 6, 5) → returns “sweet“
MIN
Returns the minimum numeric value from a series of numbers
MIN
Returns the minimum numeric value from a series of numbers
Usage:
MIN( [Number1], [Number2], ... )
MIN( [Linked Record Field].[Number] )
Example:
MIN( [Invoices].[Total] ) → if [Invoices] has 6 linked records with the lowest [Total] value of 10, returns 10
MIN( [Count A], [Count B] ) → if [Count A] is 3 and [Count B] is 4, returns 3
MINUTE
Returns the numeric minute from a datetime expression
MINUTE
Returns the numeric minute from a datetime expression
Usage:
MINUTE( Date )
Example:
MINUTE( "01-01-2020 01:10:11" ) → returns 10
MINUTE( "01-21-2020 02:11:12" ) → returns 11
MINUTE( "01-21-2020 13:47:12" ) → returns 47
MOD
Returns the remainder of a division operation.
MOD
Returns the remainder of a division operation.
Usage:
MOD(Dividend, Divisor)
Example:
MOD(10, 3) → returns 1
MOD(15, 7) → returns 1
MOD(8, 2) → returns 0
MONTH
Returns the numeric month from a datetime expression
MONTH
Returns the numeric month from a datetime expression
Usage:
MONTH( Date )
Example:
MONTH( "01-01-2020 01:10:11" ) → returns 1
MONTH( "11-21-2020 02:11:12" ) → returns 11
NOT
Returns TRUE if expression is logically false and FALSE otherwise
NOT
Returns TRUE if expression is logically false and FALSE otherwise
Usage:
NOT( Expression )
Example:
IF( NOT( [Status] == "Closed" ), [Status], "Case is Closed" ) → displays "Case is Closed" if [Status] is closed, otherwise displays the value of [Status]
NOT( 2 == 1 ) → returns TRUE
NOT( 1 == 1 ) → returns FALSE
NOW
Returns the current date and time as a datetime value
If you only need the current date, you can use TODAY()
instead.
NOW
Returns the current date and time as a datetime value
If you only need the current date, you can use TODAY()
instead.
Usage:
NOW()
Example:
NOW() → returns today’s date and the current time as a datetime value
NUMBER
Returns a referenced string as a number.
NUMBER
Returns a referenced string as a number.
Usage:
NUMBER( Expression, Precision )
Example:
NUMBER( "12.351" ) → returns 12.35 as a number
NUMBER( [Text], 4 ) → if [Text] is "12.35143" returns 12.3514 as a number
NUMERICVALUE
Returns the number associated with a single select field or zero if nothing selected. Multi-select fields return the sum of selected values or 0 if none.
NUMERICVALUE
Returns the number associated with a single select field or zero if nothing selected. Multi-select fields return the sum of selected values or 0 if none.
Usage:
NUMERICVALUE( [Single or Multi-Select Field] )
Example:
NUMERICVALUE([Score]) = 3
NUMERICVALUE([Categories]) → if [Categories] has two selections with associated numeric values 2 and 3, returns 5
ODD
Returns the nearest odd integer greater than or equal to the specified number.
ODD
Returns the nearest odd integer greater than or equal to the specified number.
Usage:
ODD( Number )
Example:
ODD(3) → returns 3. This example rounds 3 to the nearest odd integer, resulting in 3.
ODD(5.7) → returns 7. This example rounds 5.7 up to the nearest odd integer, resulting in 7.
ODD(-2) → returns -1. This example rounds -2 up to the nearest odd integer, resulting in -1.
OR
Returns TRUE if any of the specified expressions are logically true and FALSE otherwise
OR
Returns TRUE if any of the specified expressions are logically true and FALSE otherwise
Usage:
OR( Expression1, Expression2, ... )
Example:
OR( 2 == 1, 1 == 1 ) → returns TRUE
OR( [Status] == "Closed", [Status] == "Canceled" ) → returns TRUE if Status is either Closed or Canceled
POWER
Calculates the result of raising a number to a specified power. Precision is optional and defaults to 2.
POWER
Calculates the result of raising a number to a specified power. Precision is optional and defaults to 2.
Usage:
POWER(Base, Exponent, Precision)
Example:
POWER(2, 3) → returns 8 This example calculates 2 raised to the power of 3, resulting in 8.
POWER(4, 0.5) → returns 2 This example calculates the square root of 4 by raising it to the power of 0.5, resulting in 2.
POWER(3, -2, 4) → returns 0.1111 This example calculates 3 raised to the power of -2, which is equivalent to 1 divided by (3^2), resulting in 0.1111
QUARTER
Returns the numeric quarter from a datetime expression
QUARTER
Returns the numeric quarter from a datetime expression
Usage:
QUARTER( Date )
Example:
QUARTER( "01-01-2020 01:10:11" ) → returns 1
QUARTER( "11-21-2020 02:11:12" ) → returns 4
RECORD_ID
Returns the unique record id of the current record as text
RECORD_ID
Returns the unique record id of the current record as text
Usage:
RECORD_ID()
Example:
RECORD_ID() → returns the current record id
RELATED_RECORD_SORT
Sorts related records and returns a value of a specified field of a first record.
RELATED_RECORD_SORT
Sorts related records and returns a value of a specified field of a first record.
Usage:
RELATED_RECORD_SORT( [Related Record].[Field to Return], [Related Record].[Field to Sort By], [Sorting Order])
Example:
RELATED_RECORD_SORT([Linked Record].[Title], [Linked Record].[Number], “ASC“) → sorts Linked Records by the Number field and returns the value of the Title from the record with the lowest Number value.
RELATED_RECORD_SORT([Sub-Items].[Number], [Sub-items].[Date]) → sorts Sub-items by the Date field and returns the value of the Number field from the item with the latest Date
RELATED_RECORD_SORT([Checklist].[Assignee], [Checklist].[Due Date]) → sorts Checklist items by the Due Date field and returns the value of the Assignee field from the item with the latest Due Date
RELATED_RECORD_SORT([Linked Record].[Multiple Select], [Linked Record].[Number]) → sorts Linked Records by the Number field and returns the coma separated string of values of the Multiple Select field from the record with the lowest Number value
RELATED_RECORD_SORT([Lookup], [Lookup], “DESC”) → sorts Lookup values and returns the highest value.
RELATED_RECORDS_SORT
Sorts related records and returns a list of values of a specified field from those linked records.
RELATED_RECORDS_SORT
Sorts related records and returns a list of values of a specified field from those linked records.
Usage:
RELATED_RECORDS_SORT( [Related Record].[Field to Return], [Related Record].[Field to Sort By], [Sorting Order])
Example:
ARRAYJOIN( RELATED_RECORDS_SORT([Linked Record].[Title], [Linked Record].[Number], “ASC“), “; “) → Creates a list of linked Titles, separated with a “; “ and sorted by the linked Number field.
SUM(TOP(RELATED_RECORDS_SORT([Sub-Items].[Number], [Sub-items].[Date]), 3)) → Returns the sum of the three most recent Number fields from the Sub-Items.
TEXT(TOP(RELATED_RECORDS_SORT([Checklist].[Assignee], [Checklist].[Due Date], “DESC“), 1)) → Returns the name of person assigned to an item with the latest Due Date in a Checklist field
REPLACE
Replaces specified characters with another set of characters, returning the modified text
REPLACE
Replaces specified characters with another set of characters, returning the modified text
Usage:
REPLACE( Text, Text to Replace, Replace with Text )
Example:
REPLACE( "Testing", "s", "x" ) → returns "Texting"
REPLACE( "This is a test", "test", "text" ) → returns "This is a text"
REPLACE( "yes yes yes", "yes", "no" ) → returns "no no no"
REPT
Returns a new text string by repeating the specified text string the specified number of times.
REPT
Returns a new text string by repeating the specified text string the specified number of times.
Usage:
REPT(Text, Number)
Example:
REPT("abc ", 3) → returns "abc abc abc ". This example repeats the text string "abc " three times.
REPT("123", 5) → returns "123123123123123". This example repeats the text string "123" five times.
REPT("hello", 0) → returns "". This example, with a repeat count of 0, results in an empty string.
RFIND
Returns a number that is the offset of the matched string’s last instance. If no match is found, returns -1.
RFIND
Returns a number that is the offset of the matched string’s last instance. If no match is found, returns -1.
Usage:
RFIND(String to Find, Where to Search, Start Position, End Position)
Example:
RFIND(“home“, “Home, sweet home“) → returns 12
RFIND(“e“, “Home, sweet home“, 5, 10) → returns 9
RFIND(“x“, “Home, sweet home“) → returns -1
RIGHT
Returns the last n characters from specified text
RIGHT
Returns the last n characters from specified text
Usage:
RIGHT( Text, Characters )
Example:
RIGHT( "Testing", 4 ) → returns "ting" as text
RIGHT( ("This is " + "a test"), 9 ) → returns "is a test" as text
ROUND
Rounds a number to the specified number of decimal places
ROUND
Rounds a number to the specified number of decimal places
Usage:
ROUND( Number, Digits )
Example:
ROUND( [Total], 0 ) → if [Total] is 19.95, function returns 20
ROUND( [Total], 1 ) → if [Total] is 12.45, function returns 12.5
ROUNDDOWN
Returns the rounded-down value of the specified number to the specified number of digits.
ROUNDDOWN
Returns the rounded-down value of the specified number to the specified number of digits.
Usage:
ROUNDDOWN(Number, Digits)
Example:
ROUNDDOWN(5.8, 0) → returns 5. This example rounds down 5.8 to the nearest integer.
ROUNDDOWN(7.35, 1) → returns 7.3. This example rounds down 7.35 to one decimal place, resulting in 7.3.
ROUNDDOWN(9876, -2) → returns 9800. This example rounds down 9876 to the nearest hundred, resulting in 9800.
ROUNDUP
Returns the rounded-up value of the specified number to the specified number of digits.
ROUNDUP
Returns the rounded-up value of the specified number to the specified number of digits.
Usage:
ROUNDUP(Number, Digits)
Example:
ROUNDUP(5.3, 0) → returns 6. This example rounds up 5.3 to the nearest integer.
ROUNDUP(7.85, 1) → returns 7.9. This example rounds up 7.85 to one decimal place, resulting in 7.9.
ROUNDUP(1234, -2) → returns 1300. This example rounds up 1234 to the nearest hundred, resulting in 1300.
SQRT
Calculates the square root of a given number with specified precision.
SQRT
Calculates the square root of a given number with specified precision.
Usage:
SQRT(Number, Precision)
Example:
SQRT(9) → returns 3.00
SQRT(16, 3) → returns 4.000
SQRT(2, 3) → returns 1.414
STDEV
Returns the standard deviation of a series of numbers
STDEV
Returns the standard deviation of a series of numbers
Usage:
STDEV( Number1, Number2, ... )
STDEV( [Linked Record].[Field] )
Example:
STDEV( [Number1], [Number2], [Number3] ) → if [Number1] is 10, [Number2] is 20 and [Number3] is 30, function returns 10
STDEV( [Invoice].[Total] ) → if 3 [Invoices] have [Total] values of 100, 110 and 150, function returns 26.46
SUM
Returns the sum of a series of numbers
SUM
Returns the sum of a series of numbers
Usage:
SUM( Number1, Number2, ... )
SUM( [Linked Record].[Field] )
Example:
SUM( [Number1], [Number2], [Number3] ) → if [Number1] is 10, [Number2] is 20 and [Number3] is 30, function returns 60
SUM( [Invoice].[Total] ) → if 3 [Invoices] have [Total] values of 100, 110 and 150, function returns 360
SUMIF
Returns the sum of a series of numbers that match specified criteria
SUMIF
Returns the sum of a series of numbers that match specified criteria
Usage:
SUMIF( Criteria, [Linked Record].[Number1] )
Example:
SUMIF( [Invoice].[Status] == "Final", [Invoice].[Total] ) → if 3 [Invoices] have [Total] values of 100, 110 and 150, and [Invoice].[Status] set to "Final" function returns 360
T
Returns the argument if it is text and blank otherwise
T
Returns the argument if it is text and blank otherwise
Usage:
T(Value)
Example:
T("hello") → returns "hello". This example returns the text "hello" because it is a text value.
T(42) → returns "". This example returns an empty string because the value 42 is not text.
T(True) → returns "". This example returns an empty string because the boolean value True is not text.
TEXT
Returns the referenced numeric or date expression as text
TEXT
Returns the referenced numeric or date expression as text
Usage:
TEXT( Expression )
Example:
TEXT( [Due Date] ) → if [Due Date] is 12/31/2021 returns "12/31/2021" as text
TEXT( [Score] ) → if [Score] is 10 returns "10" as text
TODAY
Returns the current date as a date value
TODAY
Returns the current date as a date value
Usage:
TODAY()
Example:
TODAY() → returns today’s date as a date value
TOP
Accepts an array and limits it to a specific number of values. Both arguments are required.
TOP
Accepts an array and limits it to a specific number of values. Both arguments are required.
Usage:
TOP([Array of values], [Number of values to return])
Example:
ARRAYJOIN(TOP([Linked Record].[Title], 2), ";" ) → returns a list of the first two Titles from a Linked Record.
SUM(TOP([Sub-Items].[Number], 3)) → sums up the Number fields of the first three Sub-Items.
SUM((TOP(RELATED_RECORDS_SORT([Linked Record].[Number], [Linked Record].[Date]), 3)) → Returns the sum of the three most recent Number fields from the Linked Record.
TRIM
Removes whitespace from the beginning and end of the specified text
TRIM
Removes whitespace from the beginning and end of the specified text
Usage:
TRIM( Text )
Example:
TRIM( " Testing " ) → returns "Testing"
TRIM( "This is " + "a Test " ) → returns "This is a test"
UPPER
Returns specified text in upper case
UPPER
Returns specified text in upper case
Usage:
UPPER( Text )
Example:
UPPER( "Testing" ) → returns "TESTING"
UPPER( "This is " + "a Test" ) → returns "THIS IS A TEST"
WEEK
Returns the numeric week number of the year from a datetime expression
WEEK
Returns the numeric week number of the year from a datetime expression
Usage:
WEEK( Date )
Example:
WEEK( "01-01-2020 01:10:11" ) → returns 1
WEEK( "11-21-2020 02:11:12" ) → returns 47
WEEKDAY
Returns the day of the week as an integer between 1 and 7. StartDayOfWeek is an optional argument specifying the starting day of the week. It can be either "Monday" or "Sunday". If omitted, weeks start on Monday by default.
WEEKDAY
Returns the day of the week as an integer between 1 and 7. StartDayOfWeek is an optional argument specifying the starting day of the week. It can be either "Monday" or "Sunday". If omitted, weeks start on Monday by default.
Usage:
WEEKDAY(Date, StartDayOfWeek)
Example:
WEEKDAY( TODAY() ) → returns 3, assuming today is Wednesday.
WEEKDAY( TODAY(), "Sunday") → returns 4, assuming today is Wednesday.
WORKDAYS
Returns the date a specified number of working days (as defined in your SmartSuite profile, including any holidays that have been specified) from the specified start date
WORKDAYS
Returns the date a specified number of working days (as defined in your SmartSuite profile, including any holidays that have been specified) from the specified start date
Usage:
WORKDAYS( Start Date, Days )
Example:
WORKDAYS( "4/2/2020", 5 ) → returns date 4/9/2020
WORKDAYS_DIFF
Returns the number of working days (as defined in your SmartSuite profile, including any holidays that have been specified) between the specified start and end dates (inclusive of the start and end days)
WORKDAYS_DIFF
Returns the number of working days (as defined in your SmartSuite profile, including any holidays that have been specified) between the specified start and end dates (inclusive of the start and end days)
Usage:
WORKDAYS_DIFF( Start Date, End Date )
Example:
WORKDAYS_DIFF( "4/2/2020", "4/7/2020" ) → returns 4 as a number
YEAR
Returns the numeric year from a datetime expression
YEAR
Returns the numeric year from a datetime expression
Usage:
YEAR( Date )
Example:
YEAR( "01-01-2020 01:10:11" ) → returns 2020
YEAR( "11-21-2019 02:11:12" ) → returns 2019