Table of Contents
Formula column at SeaTable
One of the most powerful features of SeaTable is the formula column. With formulas you can automatically transform, calculate, combine or arbitrarily summarize the values of other columns from the current table or even from another (linked) table of the same base. On top of that, you can of course add other formula columns that take the results from the existing formal columns and calculate them. The formula columns that depend on the other columns are automatically updated as soon as the values there are changed.
Currently SeaTable provides the following functions in the “Formula” column:
- Constants
- Operands
- Mathematical functions
- Text functions
- Date functions
- Logical functions
- Statistical functions
And a collection of link functions in the “Link Formula” column:
- Linking functions
Apart from the above functions, there are one or more column lists in the formula editor. These are those columns in the current table and the linked columns of the other table in Base. If you click on the “+” symbol next to it, the column name will be added as an element to the input field, in the format {ColumnName} or {LinkedTable.ColumnName}. You can use this element directly in your formula. Alternatively, you can of course enter a column name in the form of {ColumnName} by hand.
In this article we will show you a complete overview of all formulas in SeaTable with appropriate examples. If you are looking for a specific function, you can use the key combination Ctrl +F to quickly find an entry on this page.
The SeaTable formula collection with examples
Constants
| Operator | Description | Examples |
|---|---|---|
| e | Returns the Euler number e=2.71828…. | e+1
= 3.71828183 |
| pi | Returns the circle number Pi. | pi
= 3.14159265 |
| true() | Returns the logical value ’true'. | true()
= true |
| false() | Returns the logical value ‘false’. | false()
= false |
Operands
| Operator | Description | Examples |
|---|---|---|
| add(num1, num2) | Adds two numeric values (num1 and num2) and returns the result. | add(1, 2)
= 3 |
| Alternative: + | 1+2
= 3 |
|
| subtract(num1, num2) | Subtracts one numeric value (num2) from another (num1). | subtract(5, 4)
= 1 |
| Alternative: - | 5-4
= 1 |
|
| multiply(num1, num2) | Multiplies two numerical values. | multiply(3, 4)
= 12 |
| Alternative: * | 3*4
= 12 |
|
| divide(num1, num2) | Divides one numeric value (num1) by another (num2). | divide(5, 2)
= 2.5 |
| Alternative: / | 3/2
= 1.5 |
|
| mod(num1, num2) | Calculates the remainder of a division. | mod(10, 7)
= 3 |
| Alternative: % | 15%7
= 1 |
|
| power(num1, num2) | Calculates the power (num2) of a number (num1). | power(3, 2)
= 9 |
| Alternative: ^ | 3^2
= 9 |
|
| greater(num1, num2) | Checks whether a numeric value (num1) is greater than another (num2) and returns the logical value ’true’ or ‘false’. | greater(2, 3)
= false |
| Alternative: > | 2>3
= false |
|
| lessthan(num1, num2) | Checks whether a numeric value (num1) is smaller than another (num2) and returns the logical value ’true’ or ‘false’. | lessthan(2, 3)
= true |
| Alternativ: < | 2<3
= true |
|
| greatereq(num1, num2) | Checks whether a numeric value (num1) is greater than or equal to another (num2) and returns the logical value ’true’ or ‘false’. | greatereq(2, 3)
= false |
| Alternative: >= | 2>=3
= false |
|
| lessthaneq(num1, num2) | Checks whether a numeric value (num1) is less than or equal to another (num2) and returns the logical value ’true’ or ‘false’. | lessthaneq(2, 3)
= true |
| Alternativ: <= | 2<=3
= true |
|
| equal(number1, number2) | Checks whether two values (number1, number 2) are equal and returns the logical value ’true’ or ‘false’. | equal({Old price}, {New price})
= false |
| Alternative: = | {Old price}={New price}
= false |
|
| unequal(number1, number2) | Checks whether two values (number1, number2) are unequal and returns the logical value ’true’ or ‘false’ in each case. | unequal({Old price}, {New price})
= true |
| Alternative: <> | {Old price}<>{New price}
= true |
|
| concatenate(string1, string2, …) | Concatenates several strings (string1, string 2, …) into one string. | concatenate({Supplier}, " is ", {Status})
= Linux is great |
| Alternative: & | {Supplier} & " is " & {Status}
= Linux is great |
Mathematical functions
| Operator | Description | Example input | Result |
|---|---|---|---|
| abs(number) | Returns the absolute value of a number. | abs(-2) | 2 |
| ceiling(number, significance) | Rounds a number to the nearest integer or to the nearest multiple of the specified step (significance). If either argument is non-numeric, the formula returns an empty value. | ceiling(2.14) | 3 |
| If the number is an exact multiple of the significance, then there is no rounding. If the number and the significance are negative, then the rounding is away from 0. If the number is negative and the significance is positive, then the rounding is towards 0. | ceiling(-2.14, 4) | 0 | |
| even(number) | Assigns the nearest larger even number to a real number (number). | even(2.14) | 4 |
| exp(number) | Exponential function for Euler’s number e. Returns the value of e raised to the power of a given number. | exp(1) | 2.71828… |
| floor(number, significance) | Rounds a number to the nearest integer or to the nearest multiple of the specified step (significance). If either argument is non-numeric, the formula returns an empty value. | floor(2.86) | 2 |
| If the number is an exact multiple of the significance, there is no rounding. If the sign of the number is positive, the rounding is towards 0. If the sign of the number is negative, the rounding is away from 0. | floor(-3.14, 5) | -5 | |
| int(number) | Assigns the nearest smaller integer to a real number (number). | int(-3.14) | -4 |
| lg(number) | Logarithm function (number) with 10 as base. | lg(100) | 2 |
| ln(number) | Logarithm function (number) with Euler’s number e as the base. | ln(e) | 1 |
| log(number, base) | Logarithm function (number) with definable base. | log(81, 3) | 4 |
| However, if no base is given, this function works exactly like lg(), i.e. with 10 as the base. | log(1000) | 3 | |
| odd(number) | Assigns the nearest larger odd number to a real number (number). | odd(-2.14) | -1 |
| round(number, digits) | Rounds a number to a specified number of decimal places (digits). If no decimal place (digits) is specified, the number is rounded to the 1st place to the left of the decimal point. | round(3.14) | 3 |
| If a positive decimal (digits) is given, round to the digit to the right of the decimal point. | round(3.14, 1) | 3.1 | |
| If a negative decimal place (digits) is given, is rounded to the left of the decimal point. | round(3.14, -3) | 0 | |
| round(99.1, -2) | 100 | ||
| rounddown(number, digits) | Round a number in the direction of zero. If no decimal place (digits) is given, the number is rounded to the 1st place to the left of the decimal point. | rounddown(3.12, 1) | 3.1 |
| rounddown(-3.15) | -3 | ||
| roundup(number, digits) | Rounds a number up from zero. If no decimal place (digits) is given, the number is rounded to the 1st digit to the left of the decimal point. | roundup(3.12, 1) | 3.2 |
| roundup(-3.15) | -4 | ||
| sign(number) | Checks whether a number is greater than, equal to or less than 0. Returns the value 1, 0 and -1 respectively. In other words, it returns the sign of a number, respectively for ‘+’, ‘zero’ and ‘-’ with 1, 0, and -1. | sign(-2) | -1 |
| sqrt(number) | Returns the square root of a number. | sqrt(81) | 9 |
| sum(num1, num2, …) | Returns the sum of the numbers (num1, num2, …). | sum(7,8,9) | 24 |
Text functions
| Operator | Description | Example input | Result |
|---|---|---|---|
| exact(string1, string2) | Checks whether two strings (string1, string2) are exactly identical. Returns the values ’true’ or ‘false’. Upper/lower case is taken into account. | exact(‘SeaTable’, ‘Seatable’) | false |
| find(findString, sourceString, startPosition) | Returns the start position of a string (findString) within another string (sourceString). Case sensitivity is respected. Without a find, 0 is returned. If the start position (startPosition) is given as decimal, it is rounded down. If the cell in the column for the keyword (findString) is still empty, 1 is returned. If the cell in the column for the target string (sourceString) is still empty, an empty value (’’) is returned. | find(‘Sea’, ‘seaTable’, 1) | 0 |
| The search will start from the given ‘startPosition’. However, this ‘startPosition’ has no influence on the result: it then always returns the absolute start position. If the ‘startPosition’ of the string to be found (findString) was given after the actual start position of the string (sourceString), 0 is returned, because nothing was found from this position. | find(’table’, ‘big table’, 4) | 5 | |
| find(’table’, ‘big table’, 6) | 0 | ||
| left(string, count) | Returns the specified number (count) of characters at the beginning of a string. | left(‘SeaTable’, 3) | Sea |
| len(string) | Returns the number of characters in a string. | len(‘SeaTable’) | 8 |
| lower(string) | Converts a string into lower case letters. | lower(‘DeutscH’) | german |
| mid(string, startPosition, count) | Returns the specified number (count) of characters from the specified start position (startPosition) from a string. | mid(‘SeaTable is the best’, 1, 8) | SeaTable |
| Start position (startPosition) and number (count) must not be empty, negative or equal to zero. However, if startPosition and count are given as decimal, they are rounded down. Too much given number (count) will be ignored. | mid(‘SeaTable is the best.’, 10.9, 27.3) | is the best. | |
| replace(sourceString, startPosition, count, newString) | Replaces a part (count) of a string (sourceString) from a certain start position (startPosition) with another string (newString). The number (count) of characters is only taken into account for the old string (sourceString), but not for the new string (newString). | replace(‘SeaTable is the best.’, 1, 8, ‘Seafile’) | Seafile is the best. |
| If count is given as zero, the new string is simply added from the start position in the old string. | replace(‘SeaTable is the best.’, 1, 0, ‘Seafile’) | SeafileSeaTable is the best. | |
| rept(string, number) | Repeats a string as many times (number) as specified. | rept(‘Sea ‘, 3) | Sea Sea Sea |
| right(string, count) | Returns the specified number (count) of characters at the end of a string. | right(‘SeaTable’, 5) | Table |
| search(findString, sourceString, startPosition) | Returns the start position of a string (findString) within another string (sourceString). Case sensitivity is not taken into account. Without a find, 0 is returned. If the start position (startPosition) is given as decimal, it is rounded down. If the cell in the column for the keyword (findString) is still empty, 1 is returned. If the cell in the column for the target string (sourceString) is still empty, an empty value (’’) is returned. | search(‘Sea’, ‘seaTable’, 1) | 1 |
| The search will start from the given ‘startPosition’. However, this ‘startPosition’ has no influence on the result: it then always returns the absolute start position. If the ‘startPosition’ of the string to be found (findString) was given after the actual start position of the string (sourceString), 0 is returned, because nothing was found from this position. | search(’table’, ‘big table’, 4) | 5 | |
| search(’table’, ‘big table’, 6) | 0 | ||
| substitute(sourceString, oldString, newString, index) | Replaces existing text (oldString) with new text (newString) in a string (sourceString). If there is more than one text (oldString) in the string (sourceString), only the ‘index’-th text is replaced. Case sensitivity is respected. | substitute(‘SeaTableTable’, ‘Table’, ‘file’, 1) | SeafileTable |
| If the index is given as 0 or not, all found text parts (oldString) are replaced by the new text (newString). | substitute(‘SeaTableTable’, ‘Table’, ‘file’) | Seafilefile | |
| T(value) | Checks whether a value is text. If yes, the text is returned. If no, the return value is empty. | T(123) | (blank) |
| T(‘123’) | 123 | ||
| text(number, format) | Converts a number into text and formats it in the specified format. The format can be percent and number, dollar, euro and yuan. | text(150, ’euro’) | €150 |
| When a number is directly converted to a percentage, its absolute value is retained. That is, 50 is converted to 5000%. However, if you want 50%, you still have to divide the number by 100 before the conversion. | text(50, ‘percent’) | 5000% | |
| text(50/100, ‘percent’) | 50% | ||
| trim(string) | Removes spaces at the beginning and end of a string. | trim(’ SeaTable ‘) | SeaTable |
| upper(string) | Converts a string into upper case letters. | upper(‘DeutscH’) | GERMAN |
| value(string) | Converts a text (string) representing a number into a number. | value(‘123’) | 123 |
Date functions
| Operator | Description | Example input | Result |
|---|---|---|---|
| + | Adds days or duration to a date with or without minutes. If the date column has no minutes, 00:00 of that day is taken as the default value. | date(2021, 1, 31)+1 | 2021-02-01 |
| For example, if a duration of 8 hours 20 minutes is entered in the column {duration} has: | date(2021, 1, 31)+{duration} | 2021-01-31 08:20 | |
| - | Subtracts days or duration from a date with or without minutes. If the date column has no minutes, 00:00 is taken as the default value from that day. | date(2021, 1, 31) - 30 | 2021-01-01 |
| For example, if a duration of 8 hours 20 minutes is entered in the column {duration} has: | date(2021, 1, 31)-{duration} | 2021-01-30 15:40 | |
| With {Date1}-{Date2} it returns the time difference in days. | date(2021, 1, 31)-date(2020, 12, 23) | 39 | |
| date(year, month, day) | Returns a date in international format (ISO) from the entered year, month and day. If the year is entered with two digits, it is automatically understood as a year in the 1900s. If the number of the month or day is too large (greater than 12 or 31 respectively), these months or days are automatically converted to the next year or month. | date(2021, 1, 3) | 2021-01-03 |
| dateAdd(date, count, unit) | Adds the specified number (count) of years, months, weeks, days, hours, minutes or seconds to a date/time. | dateAdd(‘2020-02-03’, 2, ‘days’) | 2020-02-05 |
| Tip: if you want to add a complex duration (count) such as 1 day 12 hours, you can convert it to e.g. 24+12=36 hours and enter it as a single duration (count) in the formula. Convert the duration to the smallest unit: in this case, hours. | dateAdd(‘2020-09-04 13:05:18’, 36, ‘hours’) ODER dateAdd({form submission}, 36, ‘hours’) | 2020-09-06 01:05:18 | |
| datedif(startDate, endDate, unit) | Calculates the seconds, days, months or years between two date values. The optional unit argument can be one of the following: S (seconds), D (full days), M (full months), Y (full years), YD (full days, ignoring years), YM (full months, ignoring days and years), MD (full days, ignoring months and years). | dateDif(‘2018-01-01’, ‘2020-01-01’) | 2 |
| The optional unit argument can be one of the following: S (seconds), D (full days), M (full months), Y (full years), YD (full days, without taking years into account), YM (full months, without taking days and years into account), MD (full days, without taking months and years into account). | dateDif(‘2019-10-11’, ‘2020-12-12’, ‘M’) | 14 | |
| day(date) | Returns the day of a date as a number. The number returned is between 1 and 31. | day(‘2020-01-03’) | 3 |
| days(startDate, endDate) | Returns the number of days between two date values (startDate and endDate). | days(‘2020-01-01’, ‘2020-02-03’) | 33 |
| eomonth(startDate, months) | Determines the date of the last day of the month that is the specified number (months) of months after the specified date (startDate). If the number (months) is given as 0, the last day of the month is simply determined. | eomonth(‘2020-01-01’, 1) | 2020-02-29 |
| If the number (months) is given as negative, the date of the last day of the month that contains the absolute number (months) of months before the specified date (startDate) is determined. | eomonth(‘2020-01-01’, -1) | 2019-12-31 | |
| hour(date) | Returns the hour of a time (date) as a number. The number returned is between 0 and 23. | hour(‘2020-02-14 13:14:52’) | 13 |
| If there is no hour in the time (date), 0 is returned. | hour(‘2020-02-14’) | 0 | |
| hours(startDate, endDate) | Returns the number of hours between two date values (startDate and endDate). The minutes in the date values are not taken into account. | hours(‘2020-02-14 13:14’, ‘2020-02-14 15:14’) | 2 |
| If no hours are included in the time specification (startDate or endDate), 0 o’clock on that day is automatically assumed. | hours(‘2020-02-14’, ‘2020-02-14 15:14’) | 15 | |
| minute(date) | Returns the minutes of a time (date) as a number. The number returned is between 0 and 59. | minute(‘2020-02-14 13:14:52’) | 14 |
| If there are no minutes in the time (date), 0 is returned. | minute(‘2020-02-14’) | 0 | |
| month(date) | Returns the month of a date as a number. The number returned is between 1 (January) and 12 (December). | month(‘2020-02-14 13:14:52’) | 2 |
| months(startDate, endDate) | Returns the number of months between two date values (startDate and endDate). The days and time in the date values are not taken into account. | months(‘2020-02-01 13:14’, ‘2020-03-31 15:54’) | 1 |
| If no month is given in the date values (startDate, endDate), January is automatically assumed as the month. | months(‘2020’, ‘2021’) | 12 | |
| networkdays(startDate, endDate, holiday1, holiday2, …) | Returns the number of whole working days between two date values (startDate and endDate). You can also define holidays other than Saturday and Sunday (holiday1, holiday2, etc.), these will also be deducted. If no holidays are to be included in the calculation, these parameters can simply be omitted. | networkdays(‘2020-01-01’, ‘2020-01-07’,‘2020-01-01’) | 4 |
| Note that the specified last day (endDate) is also counted in the formula. That is, as for example in this formula, three working days are counted: the 7th, 8th and 9th of September, 2020. | networkdays(‘2020-09-07’, ‘2020-09-09’) | 3 | |
| now() | Returns the current date and time. This column is only updated automatically when the base has been reloaded. | now() | 2020-09-07 12:59 |
| second(date) | Returns the seconds of a time (date) as a number. The number returned is between 0 and 59. | second(‘2020-02-14 13:14:52’) | 52 |
| today() | Returns the current date. This column is only updated automatically when the base has been reloaded. | today() | 2020-09-07 |
| This function is convenient for calculating time duration between a certain date & time and now. On each reload or recalculation of the base, the calculation is updated. | networkdays(‘2020-09-01’, today()) | 4 | |
| weekday(date, weekStart) | Returns the day of the week of a date as a number. The returned number is between 1 and 7. You can define the first day of the week (weekStart): Monday (‘Monday’) or Sunday (‘Sunday’ or leave out, because the start as Sunday is default). A third option is not possible. Upper/lower case is not taken into account. | weekday(‘2020-01-01’, ‘Monday’) | 3 |
| If no ‘weekStart’ is given or if a ‘weekStart’ other than ‘Monday’ or ‘Sunday’ is given, it is always assumed to be ‘Sunday’. Therefore, if it should be ‘Monday’, enter ‘Monday’; if it should be ‘Sunday’, you can omit this parameter. | weekday(‘2020-01-01’, ‘Thursday’) OR weekday(‘2020-01-01’) | 4 | |
| weeknum(date, return_type) | Returns the absolute week number of a date as a number. The returned number is between 1 and 53. Here you can define the first day of the week (return_type). Enter the number 1 or 2, or 11 to 17, and 21 as “return_type” to define the start of a week: 1/Sunday、2/Monday、11/Monday、12/Tuesday、13/Wednesday、14/Thursday、15/Friday、16/Saturday、17/Sunday. If the week number is to be returned according to the ISO standard, specify the number 21 as the “return_type”, or use the isoweeknum function. | weeknum(‘2020-01-12’, 11) | 2 |
| If no ‘return_type’ is given, it is always assumed to be ‘Sunday’. | weeknum(‘2020-01-12’) | 3 | |
| isoweeknum(date) | Returns the ISO week number (KW) of a date as a number. | isoweeknum(‘2021-01-04’) | 1 |
| Different than the function weeknum(): with the function isoweeknum() you sometimes also get a week number that is still in the previous year. In this example: 1 January 2021 belongs to the 53rd calendar week of 2020 according to the ISO standard. | isoweeknum(‘2021-01-01’) | 53 | |
| year(date) | Returns the year of a date as a number. | year(‘2020-01-01’) | 2020 |
Logical functions
| Operator | Description | Example input | Result |
|---|---|---|---|
| and(logical1, logical2, …) | Checks whether all arguments (logical1, logical2, …) are true (valid, non-empty and non-zero). If so, ’true’ is returned, otherwise ‘false’. | and(1, ‘’, 2) | false |
| if(logical, value1, value2) | Checks if an argument (logical) is true and if yes, returns the first value (value1) and if no, returns the second value (value2). Attention: the two values (value1 and value2) must be the same type, e.g. both must be string, or number. Otherwise, the first result will define the column type and where it should add the second value, an empty value will be filled. | if(1>2, 3, 4) | 4 |
| For condition (logical), only a comparison with is allowed. If you only enter condition (logical) and the first value (value1): it will return the first value (value1) if condition (logical) is true; and it will return an empty value (’’) if condition (logical) is false. | if({Budget}>{Price}, ‘Yes’) | Yes | |
| ifs(logical1, value1, logical2, value2, …) | Prüft, ob eine oder mehrere Bedingungen (logical1, logical2, …) zutreffen und gibt einen Wert (value1, value2, …) zurück, der mit zur ersten WAHR-Bedingung passt. Versuchen Sie möglichst alle zurückzugebenen Werte (value1, value2) in den gleichen Typen (entweder alles “string” oder alles “number”) anzugeben, wie z.B. in ifs({Area}=0,“mini”,{Area}<=100,“small”,{Area}1000,“large”), ist das Ergebnis immer “string”, oder in dem Beispiel rechts, ist das Ergebnis immer “number”. Wenn Sie aber Nummer als String gegeben haben, funktioniert die ifs Funktion auch, aber in diesem Fall wird keine Summe mehr automatisch für diese Spalte berechnet, da diese dann als String wahrgenommen werden. | ifs( 1>2, 3, 5>4, 9) | 9 |
| not(boolean) | Reverses the logical value (boolean). In other words: converts ’true’ to ‘false’ and ‘false’ to ’true’. | not(and(1, ‘’, 2)) | true |
| or(logical1, logical2, …) | Checks if at least 1 of the arguments (value1, value2, …) is true (valid, non-empty and non-zero) and returns ’true’ in this case. If all arguments are false, then returns ‘false’. | or(1, ‘’, 2) | true |
| switch(logical, matcher1, value1, matcher2, value2, …, default) | Evaluates an expression (logical) against a list of values (matcher) and returns the result (value) that matches the first matching value. If there is no match, then an optional default value is returned. At least 3 parameters (logical, matcher, value) must be specified. | switch({grades}, 1, ‘very good’, 2, ‘good’, 3, ‘satisfactory’, 4, ‘passed’, ‘failed’) | Very good |
| If there are several identical values in the value list (matcher), only the first match is taken into account. | switch(int(68/10), 6, ‘OK’, 6, ‘KO’) | OK | |
| xor(logical1, logical2, …) | Returns the contravalence of all arguments. In other words: Checks if the number of true arguments (logical) is odd and returns the value ’true’. | xor(1, 0, 2<1, 1) | false |
Statistical functions
| Operator | Description | Examples |
|---|---|---|
| average(number1, number2, …) | Returns the average of the numbers (number1, number2, …). | average(1, 2, 3, 4, 5)
= 3 |
| count(number1, number2, …) | Returns the number of numbers (number1, number2, …). Only valid and non-empty numbers are considered. In this example, ’’ is an empty value, and ‘3’ is not a number but a text. | count(1, '', 2, '3')
= 2 |
| counta(textORnumber1, textORnumber2, …) | Counts the number of non-empty cells (textORnumber1, textORnumber2, …). These cells can be text or number. In this example, 1 and 2 are numbers, ‘3’ is a text, and ’’ is an empty value. | counta(1, '', 2, '3')
= 3 |
| countall(textORnumber1, textORnumber2, …) | Counts the number of elements (textORnumber1, textORnumber2, …) including numbers (1, 2), text (‘3’) and empty cells (’’). | countall(1, '', 2, '3')
= 4 |
| countblank(textORnumber1, textORnumber2, …) | Counts the number of empty cells. | countall(1, '', 2, '3')
= 1 |
| countItems(“column-name”) | Counts the number of elements in a cell. Supported column types are multiple selection, employee, file and image. | countItems("members")
= 6 |
| max(number1, number2, …) | Returns the largest value from the specified numbers (number1, number2, …). | max(-1, 3, 4, 2, -1)
= 4 |
| min(number1, number2, …) | Returns the smallest value from the specified numbers (number1, number2, …). | min(-1, 3, 4, 2, -1)
= -1 |
| large(num1, num2, …, k) | Returns the k-largest value from the list of numbers (num1, num2, …). In this example: large(35, 92, 10, 2) returns the 2-largest value of (35, 92, 10), which is 35. | large(35, 92, 10, 2)
= 35 |
| small(num1, num2, …, k) | Returns the k-smallest value from the list of numbers (num1, num2, …). In this example: small(9, 2, -7, -3, 3) returns the 3-smallest value of (9, 2, -7, -3), which is 2. | small(9, 2, -7, -3, 3)
= 2 |
Linking functions
You can use the link functions as a formula, or set them up more easily with the “Link Formula” column.
| Operator | Description | Example input |
|---|---|---|
| countlinks(“Column”, [condition]) | Counts the linked entries in the given link column. Entries can be filtered with the optional parameter condition([condition]). In the example: In the current table there is a link column “Speakers”. In one of its cells there are several linked entries. Now we want to count how many links there are in each cell where the “Price” in the linked entry is greater than 500. | countlinks("Speakers", "{Price}>500")
= 3 (The number of entries linked to the “Speakers” column where “Price” is greater than 500). |
| rollup(“Column”, “Other column”, summary, [condition]) | Returns an evaluated value of linked entries in other tables. By linking from the column “Column”, entries in the column “Other column” in the linked table are evaluated. Summary methods that can be performed are ‘average’, ‘concatenate’, ‘count’, ‘max’, ‘min’ and ‘sum’. Optionally, filter conditions can be applied. In the example: In the current table there is a link column ‘Speakers’ which is linked to another table. Since each “Speaker” is linked to multiple entries, we want to find out, with a “Price” smaller than 500, what is the largest “Power” for each “Speaker”. | rollup("Speakers", "Power", "max", "{Price} < 500")
= 80 (The largest value of the entries linked to the “Speakers” column in “Power” where the “Price” is less than 500). |
| lookup(findmax(“Column”, “Other column”), “Target”) | Searches for the largest value in the linked column (“Other column”) and returns the value in the same row from another column (“Target”). Specify which linking column (“Column”) and which linked column (“Other column”) and target column (“Target”) are to be used. In the example: In the current table there is a link column “Speakers” which is linked to another table. Since each “Speaker” is linked to several entries, we want to find out what “Price” each “Speaker” has for its biggest “Power”. | lookup(findmax("Speakers", "Power"), "Price")
= 350 (Returns the largest value “Price” in “Power” linked to the “Speakers” column). |
| lookup(findmin(“Column”, “Other column”), “Target”) | Searches for the smallest value in the linked column (“Other column”) and returns the value in the same row from another column (“Target”). Specify which linking column (“Column”) and which linked column (“Other column”) and target column (“Target”) are to be used. In the example: In the current table there is a link column “Speakers” which is linked to another table. Since each “Speaker” is linked to several entries, we want to find out what “Price” each “Speaker” has for its smallest “Power”. | lookup(findmin("Speakers", "Power"), "Price")
= 210 (The “Price” of the smallest value in “Power” linked to the “Speakers” column). |