Formulas
Table of content:
Concepts
A formula is an expression that is combined between column/numbers/string values and operations/functions.
Value can be a number or a string, the string value must be surrounded by double quotes “Hello World“ or column value.
Operation is a math operation such as +, -, *, /, %, ^
Function is a custom function, example: Sum, Len, Concat, If, …
For example, if you wanted a formula that calculated the total revenue based on the revenue of the January column and February column, the formula would look like:
January + February
or
Sum(January, February)
Available functions (alphabetical)
Press Ctrl+F to find a function by typing the first few letters or a descriptive word.
| Function | Description | Examples |
|---|---|---|
|
And(logical1, [logical2, ...]) |
Return true if all the arguments are true, returns false otherwise. |
And(Logical 1, Logical 2) => True (if both logical values is truth) |
|
Average(value1, [value2, ...]) |
Return the average of the numbers. |
Average(1, 2, 3) => 2 |
|
Ceil(value) |
Round a number up to the nearest integer. |
Ceil(-7.1) => -7 Ceil(5.6) => 6 |
|
Concat(text1, [text2, ...]) |
Join several text items into a single text value. |
Concat("Hello", "-", "world") => Hello-world |
|
Contains(MultipleSelectionColumn, "Item 1") |
Check for the existence of a value in a list. |
Contains(["Hello", "World"], "Hello") => true |
|
Count(value1, [value2, ....]) |
Count the number of items without null items. |
Count(1,2,3,"","four") => 4 |
|
Color (value) |
Return color based on the color names at HTML Color Names Note that this function is meant to use with |
If(len(column1) > 10, ResultWithColor(Color("DarkTurquoise"), "Too long"), "Ok") |
| DateTimeAdd(datetime column, valueToAdd, unit) | Add specified "count" units to a datetime. Possible unit value are: years, months, weeks, days, hours, minutes, seconds |
DateTimeAdd("2021-04-15T09:51:00Z", 1, "days") => 2021-04-16T09:51:00Z |
| DateTimeDiff(datetime1, datetime2, unit) |
Return the difference between datetimes in specified units. The difference between datetimes is determined by subtracting [datetime1] from [datetime2]. This means that if [datetime1] is later than [datetime2], the resulting value will be negative. Possible unit value are: years, months, weeks, days, hours, minutes, seconds |
DateTimeDiff("2023-04-11T04:11:18Z", “2023-04-11T04:11:17Z”, “days”) => -16 |
|
DateTimeFormat(value, format, timezone) |
Format a datetime into a specified string. |
DateTimeFormat("2021-07-08T00:00:00Z[UTC]", "yyyy-MM-dd", “Asia/Tokyo” => 2021-07-08 08:00 |
|
DateTimeTruncate(value, unit) |
Get a datetime value based on the unit. |
DateTimeTruncate("2021-07-08T09:10:11Z[UTC]", "DayOfMonth") => 8 |
|
Day(value) |
Returns the day of the month of a datetime in the form of a number between 1-31. |
Day("2021-07-08T09:10:11Z[UTC]") => 8 |
| EditDistance (value1, value2) |
Find the Levenshtein distance between two strings Levenshtein distance: the minimum number of single-character edits (insertions, deletions or substitutions) required to change one word into the other. |
editDistance("Hallo", "Hello") → 1
|
|
EncodeAsBase64(value) |
Encode string as base64. |
EncodeAsBase64("Hello world") => SGVsbG8gd29ybGQ= |
|
EndsWith(value, suffix) |
Checks if a string ends with a specified characters |
EndsWith("Hello world", "world") => True |
|
ExtractDigits(value) |
Extract digits from string | ExtractDigits("V12") → 12 |
| ExtractJsonPath(value, jsonPath) | Extract content from JSON string |
value = {“data“: “Hello World“} Extract JsonPath(value, “$.data”) => Hello world |
|
False() |
Return false value |
False () => False |
|
Floor(value) |
Rounds a number down to the nearest integer. |
Floor(-7.1) => -8 Floor(5.6) => 5 |
|
Format(value, arguments) |
Returns formatted string by given arguments. |
Format("Hello $s", "world") => Hello world |
|
FuzzyMatching(value1, value2)
|
Return match scores that can range from 0 through 100% based on how close the value1 and value2 match | FuzzyMatching("Hello", "Hallo") → 88 |
|
Hour([datetime]) |
Returns the hour of a datetime as a number between 0 (12:00am) and 23 (11:00pm). |
Hour("2021-07-08T09:10:11Z[UTC]") => 9 |
|
If(logical, value1, value2) |
Returns value1 if the logical argument is true, otherwise it returns value2. |
If(Amount > 50, "True", "False") => True (if the logical value over 50) |
|
IfBlank(value, fallbackValue) |
Returns fallbackValue if the value is blank, otherwise return the value. |
IfBlank("", "Blank") => Blank IfBlank("Not blank", "Blank") => Not blank |
|
IsAfter(value1, value2) |
Checks if a datetime is after another datetime. |
IsAfter(Now(), "2021-07-08T09:10:11Z[UTC]") => False |
|
IsBefore(value1, value2) |
Checks if a datetime is before another datetime. |
IsBefore(Now(), "2021-07-08T09:10:11Z[UTC]") => True |
|
IsBlank(value) |
Checks if a string value is blank. |
IsBlank("Not blank") => False |
|
IsNotBlank (value) |
Check if a string value is not blank |
IsNotBlank("Not blank") => True |
|
IsChecked (value) |
Check if the value on the boolean column is checked |
IsChecked(column1) => True (given column1’s value is checked) |
|
IsUnchecked (value) |
Check if the value on the boolean column is unchecked |
IsUnchecked(column1) => False (given column1’s value is checked) |
|
IsEqual(value1, value2) |
Check if two values are equal. |
IsEqual("Hello", "Hello") => True |
|
IsNotEqual(value1, value2) |
Check if two values are not equal |
IsNotEqual("Hello", "Hello") => False |
|
IsEven(value) |
Check if a value is even. |
IsEven(6) => True |
|
IsOdd(value) |
Check if a value is odd. |
IsOdd(9) => True |
|
IsStatus(column, status) |
Check if the dependency status of a column field equals to a specified value (Up-to-date, Out-of-date, Unset) | IsStatus(English, “Out-of-date”) → true (supposed dependency status is Out-of-date) |
|
IsStatusOutOfDate
|
Check if the dependency status of a column is "Out-of-date" | IsStatusOutOfDate(English) → true (supposed dependency status is Out-of-date) |
|
IsStatusUnset |
Check if the dependency status of a column is "Unset" | IsStatusUnset(English) → false (supposed dependency status is Out-of-date) |
|
IsStatusUpToDate |
Check if the dependency status of a column is "Up-to-date" | IsStatusUpToDate(English) → false |
|
Join(delimiter, value1, [value2, ...]) |
Combine multiple string values with a delimiter. |
Join("-", "Hello", "world", "!") => Hello-world-! |
|
LastModifiedBy(column) |
Get email of the member who last modified a record or a specified column fields. |
LastModifiedBy() => a@gridly.com LastModifiedBy(column1) => a@gridly.com (given column1’s modified person is “a@gridly.com“) |
|
LastModifiedTime(column)
|
Get last modified time of record or a specified column field |
LastModifiedTime() → 2021-07-08T09:10:11Z[UTC] LastModifiedTime(column1) => 2021-07-08T09:10:11Z[UTC] (given column1’s data modified time is “2021-07-08T09:10:11Z[UTC]“) |
|
Left(value, howMany) |
Extract howMany characters from the beginning of the string. |
Left("Hello world", 5) => Hello |
|
Len(value) |
Returns the length of a string. |
Len("Hello") => 5 |
| LenB(value) | Returns the length of a string in bytes |
LenB("Hello") => 5 LenB(“你好”) => 6. |
|
List(value1, [value2, ...]) |
Makes a list of values. |
List("Hello", "world") => ["Hello", "world"] |
|
Log(number, [base]) |
Computes the logarithm of the value in provided base. The base defaults to 10 if not specified. |
Log(1024, 2) => 10 |
|
Longest(list) |
Gets longest text from the list |
Longest(split(column1,"\n") => “World!“ (given column1’s value is “Hello\nWorld!“) |
|
Lower(value) |
Makes a string lowercase. |
Lower("Hello world") => hello world |
|
Max(value1, [value2, ...]) |
Return the largest of the given numbers. |
Max(10, 100) => 100 |
|
Mid(string, whereToStart, count) |
Extract a substring of count characters starting at whereToStart. |
Mid("Hello world", 0, 5) => Hello |
|
Min(value1, [value2, ...]) |
Return the smallest of the given numbers. |
Min(10, 100) => 10 |
|
Minute([datetime]) |
Return the minute of a datetime as an integer between 0 and 59. |
Minute("2021-07-08T09:10:11Z[UTC]") => 10 |
|
Month([datetime]) |
Return the month of a datetime as a number between 1 (January) and 12 (December). |
Month("2021-07-08T09:10:11Z[UTC]") => 7 |
|
Now() |
Return the current date AND time. |
Now() => 2021-08-08T09:10:11Z[UTC] |
|
Or(logical1, [logical2, ...]) |
Return true if any one of the arguments is true. |
Or({Field1}, {Field2}) => 1 (if one field value is truthy) |
|
PathAsList() |
Convert Path to strings |
PathAsList() => Apple,Iphone,13 (given record’s path is Apple/Iphone/13) |
|
PathStartsWith (column) |
Check if path starts with a tag or sub path |
PathStartsWith("Apple/Iphone") => True (given record’s path is Apple/Iphone/13) |
|
PathEndsWith (column) |
Check if path ends with a tag or sub path |
PathEndsWith("13") => True (given record’s path is Apple/Iphone/13) |
|
PathContains (column) |
Check if path contains a tag |
PathContains("Iphone") => True (given record’s path is Apple/Iphone/13) |
| PathLastModifiedTime() | Return the last modified time when updating the path for a record |
PathLastModifiedTime() => 2021-07-08T09:10:11Z[UTC]
|
|
PathLen () |
Return the length of a path | PathLen() → 3 (given record’s path is Apple/Iphone/13) |
|
Power(base, power) |
Compute the specified base to the specified power. |
Power(2, 3) => 8 |
|
RegexMatch(value, regex) |
Return whether the input text matches a regular expression. |
RegexMatch("Hello", "[0-9\\s+\\+]+") => False |
|
RegexReplace(value, regex, replacementText) |
Substitute all matching substrings with a replacement string value. |
RegexReplace("To9", "0-9s+++", "night") => Tonight |
|
Replace(value, start_character, number_of_characters, replacement) |
Replace the number of characters beginning with the start character with the replacement text. |
Replace("Hello", 1, 5, "i") => Hi |
|
ResultWithColor (color, value) |
Return value and fills color based on the value |
If(len(column1) > 10, ResultWithColor(Color("Red"), "Too long"), "Ok") => Too long (given column1's value is "Hello world") |
|
Right(value, howMany) |
Extract howMany characters from the end of the string. |
Right("Hello world", 5) => world |
|
Round(value, precision) |
Round the value to the number of decimal places given by "precision". |
Round(2.3456, 2) => 2.35 |
|
RoundDown(value, precision) |
Round the value down to the number of decimal places given by "precision". |
RoundDown(2.3456, 2) => 2.34 |
|
RoundUp(value, precision) |
Round the value up to the number of decimal places given by "precision". |
RoundUp(2.3456, 2) => 2.35 |
|
Second([datetime]) |
Return the second of a datetime as an integer between 0 and 59. |
Second("2021-07-08T09:10:11Z[UTC]") => 11 |
|
Shortest(list) |
Get shortest text from the list |
Shortest(split(column1,"\n") => “Hello“ (given column1’s value is “Hello\nWorld!“) |
|
Split(value, delimiter) |
Split string with delimiter (default is " " if not specified) |
Split("Hello world", “ ”) => ["Hello", "world"] |
|
Sqrt(value) |
Return the square root of a nonnegative number. |
Sqrt(100) => 10 |
|
StartsWith(value, prefix) |
Check if a string starts with a specified character. |
StartsWith("Hello world", "Hello") => True |
|
Status(column) |
Get the dependency status of a column | Status(English) → Out-of-date (supposed dependency status is Out-of-date) |
|
Substitute(string, old_text, new_text) |
Replace occurrences of old_text with new_text. |
Substitute("Hello world", "Hello", "Hi") => Hi world |
|
Sum(number1, [number2, ...]) |
Sum together the numbers. Equivalent to number1 + number2 + ... |
Sum(1, 2, 3) => 6 |
|
ToDateTime(value, format) |
Convert a string to datetime format |
ToDateTime("2021-07-08", "yyyy-MM-dd") => 2021-07-08T00:00:00Z[UTC] |
|
Today() |
Return the current date with zero time |
Today() => 2021-08-08T00:00:00Z[UTC] |
|
Trim(value) |
Remove whitespace at the beginning and end of the string. |
Trim(" Hello ") => Hello |
|
True() |
Return true value |
True () => True |
|
Upper(value) |
Make string uppercase. |
Upper("Hello") => HELLO |
|
ValueAt(list, position) |
Get the value at position of the list |
ValueAt(column1, 1) => "Hello" (given column1’s value is [“Hello“, “World“]) ValueAt(split(column1,"-"), 2) => “World“ (given column1’s value is “Hello-World“) |
|
ValueFirst(list) |
Get the first value of the list |
ValueFirst(column1) => "Hello" (given column1’s value is [“Hello“, “World“]) ValueFirst(split(column1,"-")) => “Hello“ (given column1’s value is “Hello-World“) |
|
ValueLast(list) |
Get the last value of the list |
ValueLast(column1) => "World" (given column1’s value is [“Hello“, “World“]) ValueLast(split(column1,"-")) => “World“ (given column1’s value is “Hello-World“) |
|
WordCount(value) |
Count the number of words in the string (available to language column) |
WordCount("Hello world") => 2 |
|
Year([date]) |
Return the four-digit year of a datetime. |
Year("2021-07-08T09:10:11Z[UTC]") => 2021 |