1. Home
  2. Documents
  3. User Manual
  4. Become a Pro
  5. Formula reference
Print

Formula reference

Formula column for SeaTable

One of the most powerful features of SeaTable is the formula column. With formulas you can automatically transform, calculate, combine or merge 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 offers 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:

  • Link functions

In addition to the above functions, there are 1 or more column lists in the formula editor. These are the columns in the current table and the linked columns of the other table in the base. If you click on the "+" symbol next to it, the column name is added as an element in 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 examples. If you are looking for a specific function, you can use the Ctrl+F to quickly find an entry on this page.

 

SeaTable's formulary with examples

Constants

OperatorDescriptionExample inputResult
eReturns the Euler number e=2.71828...e+13.71828183
piReturns the circle number Pi.pi3.14159265
true()Returns the logical value 'true'.true()true
false()Returns the logical value 'false'.false()false

Operands

OperatorDescriptionExample inputResult
add(num1, num2)Adds two numeric values (num1 and num2) and returns the result.add(1, 2)3
Alternative: +1+23
subtract(num1, num2)Subtracts one numeric value (num2) from another (num1).subtract(5, 4)1
Alternatively: -5-41
multiply(num1, num2)Multiplies two numeric values.multiply(3, 4)12
Alternatively: *3*412
divide(num1, num2)Divides one numeric value (num1) by another (num2).divide(3, 2)1.5
Alternative: /3/21.5
mod(num1, num2)Calculates the remainder of a division.mod(15, 7)1
Alternative: %15 % 71
power(num1, num2)Calculates the power (num2) of a number (num1).power(3, 2)9
Alternatively: ^3 ^ 29
greater(num1, num2)Checks if a numeric value (num1) is greater than another (num2) and returns the logical value 'true' or 'false'.greater(2, 3)false
Alternative: >2 > 3false
lessthan(num1, num2)Checks if a numeric value (num1) is less than another (num2) and returns the logical value 'true' or 'false'.lessthan(2, 3)true
Alternatively:2 < 3true
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 >= 3false
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
Alternative: <=2 <= 3true
equal(number1, number2)Checks if two values (number1, number 2) are equal and returns the logical value 'true' or 'false'.equal({Old price}, {New price})
false
Alternatively: ={Old price}={New price}false
unequal(number1, number2)Checks whether two values (number1, number2) are not equal and returns the logical value 'true' or 'false'. unequal({Old price}, {New price})
true
Alternative:{Old price}<>{New price}true
concatenate(string1, string2, ...)Combines several character strings (string1, string 2, ...) into one character string.concatenate({Supplier}, " has the product ", {Product})Microsoft has the product Windows
Alternative: &{Supplier} & " has the product " & {Product}Microsoft has the product Windows

Mathematical functions

OperatorDescriptionExample inputResult
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 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 no rounding occurs. 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 a real number to the nearest larger even number.even(2.14)4
exp(number)Exponential function for Euler's number e. Returns the value of e given high (number).exp(1)2.71828...
floor(number, significance)Rounds a number to the nearest integer or to the nearest multiple of the specified 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, then no rounding takes place. If the sign of the number is positive, then the rounding is towards 0. If the sign of the number is negative, then the rounding is away from 0. floor(-3.14, 5)-5
int(number)Assigns the nearest smaller integer to a real 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 base.In(e)1
log(number, base)Logarithm function (number) with definable base.log(81, 3)4
But if no base is given, this function works exactly like lg(), with 10 as base.log(1000)3
odd(number)Assigns a real number to the nearest larger odd number.odd(-2.14)-1
round(number, digits)Rounds a number to the nearest integer. If no decimal place (digits) is specified, the number is rounded to the 1st digit to the left of the decimal point.round(3.14)3
If a positive decimal place (digits) is given, the digit to the right of the decimal point is rounded.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)Rounds a number towards zero. If no decimal place (digits) is given, the number is rounded to the 1st digit left of the decimal point.rounddown(3.12, 1)3.1
rounddown(-3.15)-3
roundup(number, digits)Rounds a number from zero to the nearest whole number. If no decimal place (digits) is given, the number is rounded to the 1st digit left of the decimal point.roundup(3.12, 1)3.2
roundup(-3.15)-4
sign(number)Checks whether a number is greater, equal or less than 0. Returns the values 1, 0 and -1 respectively. In other words: it returns the sign of a number, for '+', 'zero' and '-' with 1, 0, and -1 respectively.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

OperatorDescriptionExample inputResult
exact(string1, string2)Checks whether two character strings (string1, string2) are exactly identical. Returns the values 'true' or 'false' respectively. Case sensitive.exact('SeaTable', 'Seatable')false
find(findString, sourceString, startPosition)Returns the start position of a string (findString) within another string (sourceString). It is case sensitive.
Without 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'. This 'startPosition' has no influence on the result: it always returns the absolute start position. If the 'startPosition' of the character string to be searched for (findString) is given after the actual start position of the character string (sourceString), 0 is returned, since 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 character string to lower case letters.lower('German)German
mid(string, startPosition, count)Returns the specified number (count) of characters from the specified start position (startPosition) of a string.mid('SeaTable is the best', 1, 8)SeaTable
Start position (startPosition) and count must not be empty, negative or zero. However, if start position (startPosition) and number (count) are given as decimal, they are rounded down. Too much count is ignored.mid('SeaTable is the best.', 10.9, 27.3)is the best.
replace(sourceString, startPosition, count, newString)Replaces a part (count) of a character string (sourceString) from a certain start position (startPosition) with another character 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 number (count) is given as zero, the new string (newString) is simply added to the old string (sourceString) from the start position (startPosition).replace('SeaTable is the best.', 1, 0, 'Seafile')SeafileSeaTable is the best.
rept(string, number)Repeats a string as often (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). It is not case-sensitive.
Without 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'. This 'startPosition' has no influence on the result: it always returns the absolute start position. If the 'startPosition' of the character string to be searched for (findString) is given after the actual start position of the character string (sourceString), 0 is returned, since 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. The text is case-sensitive.substitute('SeaTableTable', 'Table', 'file', 1)SeafileTable
If the index is given as 0 or not, all found text (oldString) will be replaced by the new text (newString).substitute('SeaTableTable', 'Table', 'file')Seafilefile
T(value)Checks whether a value is text. If so, the text is returned. If no, the return value is empty.T(123)(empty)
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 as well as dollar, euro and yuan.text(150, 'euro')€150
When a number is converted directly to percent, its absolute value is retained. In other words, 50 is converted into 5000%. But if you want 50%, you 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 to uppercase letters.upper('German)GERMAN
value(string)Converts a text (string) representing a number into a number.value('123')123

Date functions

OperatorDescriptionExample inputResult
+Adds days or durations 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)+12021-02-01
For example, if a duration of 8 hours 20 minutes 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) - 302021-01-01
For example, if a duration of 8 hours 20 minutes 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 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 ('years'), months ('months'), weeks ('weeks'), days ('days'), hours ('hours'), minutes ('minutes') or seconds ('seconds') to a date/time ('date').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 ('hours') and enter it into the formula as a uniform duration (count). The duration is converted 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, ignoring years), YM (full months, ignoring days and years), MD (full days, ignoring months and years).dateDif('2019-10-11', '2020-12-12', 'M')14
day(date)Returns the day of a date as a number. The returned number is between 1 and 31.day('2020-01-03)3
days(startDate, endDate)Returns the number of days between two dates (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 date as a number. The number returned is between 0 and 23.hour('2020-02-14 13:14:52)13
If no hour is contained in the time specification (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 this day is automatically assumed.hours('2020-02-14', '2020-02-14 15:14')15
minute(date)Returns the minutes of a time specification (date) as a number. The number returned is between 0 and 59.minute('2020-02-14 13:14:52)14
If no minutes are included in the time (date), 0 is returned.minute('2020-02-14)0
month(date)Returns the month of a date as a number. The returned number 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 to be the month.months('2020', '2021'12
networkdays(startDate, endDate, holiday1, holiday2, ...)Returns the number of full working days between two dates (startDate and endDate). You can also define holidays other than Saturday and Sunday (holiday1, holiday2, etc.), which are also deducted. If you do not want to include public holidays, you can simply omit these parameters.networkdays('2020-01-01', '2020-01-07','2020-01-01')4
Please note that the specified last day (endDate) is also included in the formula. That means, as 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 is 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 if the Base has been reloaded.today()2020-09-07
This function is handy for calculating time 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 weekday of a date as a number. The returned number between 1 and 7, where you can define the first day of the week (weekStart): Monday ('Monday') or Sunday ('Sunday' or omitted, since the start as Sunday is the default). A third option is not possible. Upper/lower case is not considered. 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'. So 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, where 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 you want the week number to be returned according to ISO standard, specify the number of 21 as "return_type", or use the function isoweeknum.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 which is still in the previous year. In this example: the 1st of January 2021 belongs to the 53rd calendar week of the year 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

OperatorDescriptionExample inputResult
and(logical1, logical2, ...)Checks if all arguments (logical1, logical2, ...) are true (valid, not empty and not equal to zero). If yes, '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). if(1>2, 3, 4)4
For the condition (logical) only a comparison with is allowed. If you enter only condition (logical) and the first value (value1): it will return the first value (value1) if the condition (logical) is true; and it will return an empty value ('') if the condition (logical) is false.if({Budget}>{Price}, 'Yes')Yes
ifs(logical1, value1, logical2, value2, ...)Checks if one or more conditions (logical1, logical2, ...) are true and returns a value (value1, value2, ...) that matches the first TRUE condition. If possible, try to specify all returned values (value1, value2) in the same types (either all "string" or all "number"), as for example in ifs({area}=0, "mini",{area}<=100, "small",{area}<=1000, "medium",{area}>1000, "large"), the result is always "string", or in the example on the right, the result is always "number". However, if you have given number as a string, the ifs function will also work, but in this case no sum will be calculated automatically for this column, as it will then be perceived as a string.ifs( 1>2, 3, 5>4, 9)9
not(boolean)Inverts 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, not empty and not equal to 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) corresponding to the first matching value. If there is no match, 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 hit 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 is (logical) odd and returns 'true'. xor(1, 0, 2<1,false

Statistical functions

OperatorDescriptionExample inputResult
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 numbers. In this example, 1 and 2 are numbers, '3' is 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
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

Link functions

You can use the link functions as a formula or set them up more easily with the "Link Formula" column.

OperatorDescriptionExample inputResult
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 column "Speakers" where "Price" is greater than 500 is returned)
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 from the entries linked by "Speakers" in "Power" where "Price" is less than 500 is returned)
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.00
(The "Price" of the largest value in "Power" linked by "Speakers" is returned. In the example, the format setting of the formula is adjusted)
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.00
(The "Price" of the smallest value in "Power" linked by "Speakers" is returned. In the example, the format setting of the formula is adjusted)
Was this article helpful Yeah, right! No 7 out of 9 users found this article helpful.