All Collections
SmartSuite Formulas
Formula Reference Guide
Formula Functions & Operators
Formula Functions & Operators

All Functions and Operators, with syntax and examples, for our Advanced Mode of the Formula field.

Brian Dollen avatar
Written by Brian Dollen
Updated over a week ago

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

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

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

Usage: 

Number1 * Number2

Example:

6 * 2 = 12

[Numeric Field] * 2 → if [Numeric Field] is 4, returns 8

/
(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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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.

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.

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] > NOW()) → Returns a count of items that are due after today. 

CHECKLIST_COUNT([Checklist], [Checklist].[Due Date] < NOW() 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

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

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

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

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.

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.

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

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

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

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

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

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

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

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

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.

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.

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.

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).

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.

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.

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.

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] < NOW(), [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

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”

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

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

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

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

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

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

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

Usage:

LENGTH( Text )

Example:

LENGTH( "Testing" ) → returns 7 

LENGTH( "This is " + "a test" ) → returns 14

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.

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

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

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

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.

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

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

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.

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

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

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 as a date value

Usage:

NOW()

Example:

NOW() → returns today’s date and the current time as a datetime value

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.

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.

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

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.

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

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

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.

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.

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

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.

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.

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

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

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.

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.

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.

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

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

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

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

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

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

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

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

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

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.

Usage:

WEEKDAY(Date, StartDayOfWeek)

Example:

WEEKDAY( NOW() ) → returns 3, assuming today is Wednesday. 

WEEKDAY( NOW(), "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

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)

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

Usage:

YEAR( Date )

Example:

YEAR( "01-01-2020 01:10:11" ) → returns 2020 

YEAR( "11-21-2019 02:11:12" ) → returns 2019

Did this answer your question?