Skip to main content

Arrays and Other Functions

The array function is a function type in a formula that can operate on data of the array type.

Fields such as Multi-select, One-way Link, Two-way Link, Member, Attachment, and Magic lookup can all output array-type data.


COUNT()​

Counts the number of numeric types in the parameter.

COUNT(number1, [number2, ...])

Parameter Description

number: parameter of any type. This function supports multiple arguments and counts how many of them are value type (Number, Currency, Percent, and Rating are values).


Example

// Count the number of values included in the input parameter.
COUNT(1, 3, 5, "", "7 ");
Result: 3;



COUNTA()​

Counts the number of non-null values in a parameter.

COUNTA(textOrNumber1, [textOrNumber2, ….])

Parameter description

textOrNumber: Any type of Parameter. This function supports multiple parameters and calculates how many parameters there are in total for non-null values.


Example

// Count how many non-null values are included in the input parameter.
COUNTA(1, 3, 5, "", "seven ");
Result: 4;



COUNTALL()​

Counts the number of all values in the parameter, including null values.

COUNTALL(textOrNumber1, [textOrNumber2, ….])

Parameter Description

textOrNumber: Any type of parameter. This function calculates how many values are contained within the input parameters, including null values.


Example

// Count how many values the input parameter contains, including null values.
Formula: COUNTALL(1, 3, 5, "", "seven ");
Result: 5;



COUNTIF()​

Count the keyword in values.

COUNTIF(values, keyword, [operator]);

Parameter Description

values: specifies where to look for the data.Support for data of array type or text type. keyword: A keyword to look up and count. operator: comparator, not required.Support ">", "<", "=", "! =", or "=" or "contain".<"， "="， "!="， δΈε‘«εˆ™δ»£θ‘¨ "=" ζˆ– β€œ εŒ…ε«β€γ€‚


Example

// Count the number of occurrences of the character "A" in a string of text arrays [A, B, C, D, A].Where {rating} is a field of the "magic lookup" type.
COUNTIF({rating}, "A");
Result: 2;

// You can count the number of occurrences of numbers greater than 3 in an array of numbers [1, 2, 3, 4, 5].Where {score} is a field of type "magic lookup".
COUNTIF({score}, 3, ">");
Result: 2;

// You can count the number of times "grape" appears in a string of text "eat grapes, not spit grape skin", where {rhymes} is a field of type "text".
COUNTIF({rhymes}, "grape ");
Result: 2;



ARRAYCOMPACT()​

Removes empty strings and null values from the array.

ARRAYCOMPACT([item1, item2, item3]);

Parameter Description

item: array type values, such as values within fields such as Multiple select, Attachment, Magical link, and magical lookup. This function retains the "false" value and the blank string.


Example

Clear the empty string and empty value in the parameter, the output will still be the array value.
ARRAYCOMPACT([1, 2, "", 3, false, " ", null])
Result:[1, 2, 3, false, " "]



ARRAYFLATTEN()​

Concatenates multiple arrays into one array.

ARRAYFLATTEN([item1, item2, item3]);

Parameter Description

item: values of array types, for example, values within fields such as Multi-select, Attachment, One-way Link, Two-way Link, and Magic lookup.


Example

// Combine two arrays into one array.
ARRAYFLATTEN([1, 2, 3], [false]);
Result: [1, 2, 3, false];



ARRAYJOIN()​

Concatenates elements in an array with a specific delimiter.

ARRAYJOIN([item1, item2, item3], separator);

Parameter Description

item: values of array types, for example, values within fields such as Multi-select, Attachment, One-way Link, Two-way Link, and Magic lookup. separator: indicates the separator used for the connection.

Example

// Concatenate the elements in the array with ";".
ARRAYJOIN ([1, 2, 3] ";" )
Result: "1; 2; 3"



ARRAYUNIQUE()​

Filters the array for duplicate elements and returns an array of unique values.

ARRAYUNIQUE([item1, item2, item3]);

Parameter

item: values of array types, for example, values within fields such as Multi-select, Attachment, One-way Link, Two-way Link, and Magic lookup.


Example

// Returns the unique item in the array.
ARRAYUNIQUE([1, 2, 3, 3, 2, 1]);
Result: [1, 2, 3];



Log function

The record function is a type of function in the formula that can operate on a line of records.


RECORD_ID()​

Returns the ID of the current record.

RECORD_ID();

Parameter description

This function requires no parameters.


Example

// Return the current record ID.
Formula: "Record ID:" & RECORD_ID();
Result: ID;



Last updated on