Skip to main content

Using Functions

Sasha Collecutt
  • Updated

When you create calculations in Smartabase, you're often able to write your own equations to calculate the result you're interested in. Some calculations don't allow you to use your own equations (for example, linked calculations), but for the calculation types that do, you have a wide range of built-in functions that simplify the process of writing equations. 

The functions that are available for use depend on the calculation type. To see the full list of functions for the specific calculation field you're working with, select the Available functions drop-down at the bottom of the Calculation tab and open the tooltip to see details about each function.

This article covers functions that you can use in:

The calculations above can be used within tables, and you will find that many of the functions described here are useful for table calculations as well (calculations about the contents of a table in a form). You can find more information about additional functions that are used exclusively in table calculations in the article on setting up table calculations. 

This article also doesn't cover the functions that are specially designed for history calculations, because historical functions are included in the article on building history calculations.

Since functions can be used across a variety of calculation types, this article organizes functions according to their purpose:

Note that Smartabase functions are not yet internationalized and therefore use British English, which means you must use non-American spelling in functions like textsummarise. 

Numeric functions

Numeric functions are for mathematical operations. You can use them across all calculations as long as the arguments for the functions are numeric.

  • abs: returns the absolute value of a number.
abs(2) = 2
abs(-2) = 2
  • ceiling: rounds a number up.
ceiling(12.3) = 13
  • floor: rounds a number down.
floor(12.7) = 12
  • gt: determines if the first value is greater than the second value.
gt(10, 5) = true
  • gteq: determines if the first value is greater than or equal to the second value.
gteq(10, 5) = true
gteq(10, 10) = true
  • ln: calculates the natural logarithm of a number.
ln(9) = 2.19722...
  • log: calculates the logarithm (base 10) of a number.
log(12) = 1.07918...
  • lt: calculates if the first value is less than the second value.
lt(5, 10) = true
  • lteq; calculates if the first value is less than or equal to the second value.
lteq(5, 5) = true
lteq(5, 10) = true
  • mod: calculates the remainder when dividing one number by another.
mod(9, 1) = 0
  • pow: raises the first value to the power of the second value.
pow(2, 3) = 8
pow(2, 4) = 16
  • safe: returns a number value or zero. This can be useful if fields that are part of a calculation might be empty but you still want a result. 
When Strength rating contains 3, safe(Strength rating) = 3
When Strength rating is empty, safe(Strength rating) = 0
  • sqrt: calculates the square root of a value.
sqrt(9) = 3
  • sum: calculates the sum of all values.
sum(1, 5, 61) = 67
  • trunc: returns the closest whole number. It is possible to use trunc to round to the nearest x (x could be 0.5, 2.5, 5, 10, etc.). To do so, divide the number by x and then multiply it by x again as in the second and third examples.
trunc(5.3) = 5
trunc(5.4/0.5) * 0.5 = 5.5
trunc(12.97738/10) * 10 = 10

Statistical functions

Statistical functions are useful when you want to do descriptive statistical analysis. You can use these functions across all calculation types.

The sprint testing examples below assume that the following values are entered for number fields named Sprint 1 through to Sprint 5. 

Sprint 1 = 4.434 
Sprint 2 = 4.597 
Sprint 3 = 4.561 
Sprint 4 = 4.482 
Sprint 5 = 4.545
  • count: counts the number of items given.
count(5, 3, 2, 9, 19, 24) = 6 
count("football", "cricket", "swimming") = 3
  • max: calculates the maximum value of a range.
max(39, 41, 40, 28, 34) = 41
max(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.597
  • min: calculates the minimum value of a range.
min(120, 124, 119, 134, 129) = 119
min(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.434
  • mean: calculates the mean value of all values within a range.
 mean(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.5238
  • median: returns the median value of a range.
median(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 4.545
  • nthmax: returns the nth largest value of a range, with n being any positive whole number.
nthmax(2, 10, 45, 12, 15) = 15
nthmax(4, 10, 45, 12, 15) = 10
  • nthmin: returns the nth smallest value of a range, with n being any positive whole number.
nthmin(2, 10, 45, 12, 15) = 12
nthmin(4, 10, 45, 12, 15) = 45
  • sem: calculates the standard error of the mean from a range of values, which is the standard deviation divided by the square root of the number of values. 
  • stddev: calculates the standard deviation for a range of values.
stddev(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 0.065197393
stddev(Lift 1, Lift 2, Lift 3)
  • var: calculates the variance of a range of values.
var(Sprint 1, Sprint 2, Sprint 3, Sprint 4, Sprint 5) = 0.0042507

Reference functions

Reference functions are essentially look up operations, meaning that they are used to find values using a certain criterion. They can be used in all calculation types as long as the output is consistent with what the calculation type expects. For example, when using a reference type function in a simple (numeric) calculation, the result of the calculation should be a number, not text or a date.

  • contains: determines if the first value contains the second value.
contains("Three green pairs of shoes", "green") = 1
contains("Three green pairs of shoes", "red") = 0
  • firstnonzero: returns the first value from a range which is not zero:
firstnonzero(0,0,3,2,4) = 3
  • firstpositive: returns the first positive value from a range:
firstpositive(-2,-3,2,6,7) = 2
  • firstvalue: returns the first value from a range:
firstvalue(4,2,6,7,9) = 4
  • index: returns a specified item from a range:
index(3, "blue", "orange", "purple", "red", "violet", "yellow") = purple
  • lastnonzero: returns the last value from a range that is not zero:
lastnonzero(7,2,5,6,0) = 6
  • lastpositive: returns the last positive value from a range:
lastpositive(4,6,1,3,-5) = 3
  • lastvalue: returns the last value from a range:
lastvalue( 4, 7, 2, 6) = 6

Logical functions

  • and: evaluates n number of statements and returns 1 if all of them are true and 0 if at least one of them is false.
and(5 < 6, 2 > 0, contains(“abc”, “a”), 2 = 2) = 1
and(2 > 1, 3 < 7, 0 > 4) = 0
  • anyempty: checks whether any of the fields being referenced are empty. If any fields are empty, the function returns a 1, otherwise 0.
When any of the fields referenced are empty, anyempty(Strength, Weakness, Opportunity, Threat) = 1
When none of the fields referenced are empty, anyempty(Strength, Weakness, Opportunity, Threat) = 0
  • if: checks whether a statement is true or false and returns pre-defined values for each state.
When Score contains 96, if(Score > 80, "Pass", "Fail") = Pass
When Score contains 31, if(Score > 80, "Pass", "Fail") = Fail
  • isempty: checks whether all of the fields being referenced are empty. If all fields are empty, the function returns a 1, otherwise 0.
When all four fields referenced are empty, isempty(Strength, Weakness, Opportunity, Threat) = 1
When any of fields referenced contain data, isempty(Strength, Weakness, Opportunity, Threat) = 0
  • or: evaluates n number of statements and returns 1 if at least one of them is true or 0 if none of them are true.
When Fatigue contains 5, or(Fatigue > 3, Energy < 3, Motivation < 3) = 1
When Fatigue contains 2, Energy contains 4, Motivation contains 5, or(Fatigue > 3, Energy < 3, Motivation < 3) = 0

Text functions

  • charat: returns the nth character of the specified text, with 0 being the first character.
When Ticket Code contains 234-4532-029450-A, charat(Ticket Code, 1) = 3
  • concatenate: combines all items specified into a single text string, which is useful for summarizing important information.
When Sets contains 3 and Reps contains 12, concatenate(Sets, “x”, Reps) = 3x12
  • customtextsummarise: combines all items specified into one text string, using the first item as the divider.
customtextsummarise(" --- ", Motivation, Energy, Fatigue) = High --- Low --- Moderate
  • option: if option fields have scores, Smartabase will use the score in calculations. You can use the option function to use the text result of an option field for the calculation.
When the option called Extreme (scored as 5) for Rating is selected, option(Rating) = Extreme
  • replace: replaces all occurrences of the second value in the first value with the third value. Note that the arguments for the replace function are case sensitive.
replace("Example", "e", "@") = Exampl@
  • replaceall: replaces any occurrences of the third or subsequent values in the first value with the second value.
replaceall("one two three four five six", "zero", "one", "three", "five") = zero two zero four zero six
  • split: splits a specified text using the second value. The third value specifies which occurrence of the second value will be used to split the text. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, split(Ticket Code, "-", 2) = 029450
  • splitfirst: this function works the same way as as split but there is no need to specify a third value. It automatically returns the first segment of the text, like using a a split function with 0. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, splitfirst(Ticket Code, "-") = 234
  • splitgreaterthan: splits a string of text using the second value and returns everything after the occurrence specified by the third value. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, splitgreaterthan(Ticket Code, "-", 1) = 4532 029450 A
  • splitlast: same as splitfirst but in this case the function returns the last value. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, splitlast(Ticket Code, "-") = A
  • splitlessthan: same as splitgreaterthan but in this case it returns the everything before the third value. You cannot split a value with a period.
When Ticket Code contains 234-4532-029450-A, splitlessthan(Ticket Code, "-", 2) = 234 4532
  • substring: specify the start position (second argument) and number of characters (third argument) to return from a field specified in the first argument. 
When Ticket Code contains 234-4532-029450-A, substring(Ticket Code, 0, 3) = 234
  • textsummarise: combines all items specified into one string of text separated by commas.
When Color contains Blue, Shape contains Square and Size contains Small, textsummarise(Color, Shape, Size) = Blue,Square,Small
  • whitespacetextsummarise: combines all items specified into one string of text separated by blank spaces.
When Color contains Blue, Shape contains Square and Size contains Small, whitespacetextsummarise(Color, Shape, Size) = Blue Square Small

Date and time functions

  • date: returns a date value using the default date format for the Smartabase site.
When Graduation Date contains 05/31/2021 and the site default date format is mm-dd-yyyy, date(Graduation Date) = 05-31-2021
  • dateformat: converts a date value to the specified format.
When Graduation Date contains 05/31/2021, dateformat(Graduation Date, "DDDD") = Monday
  • duration: converts milliseconds to a duration in the specified format.
When Result contains 1326000, duration(Result, "hh:mm:ss") = 00:21:06
  • weekstart: tells you which week of the year a date falls within. However, you must specify which day you consider the week to start on (Sunday = 0, Saturday = 6), which week of the year you want to start counting from (0 - 52) and which week of the year you want the start week to be.
When Graduation Date contains 05/31/2021, weekstart(Graduation Date, 0, 1, 1 ) = 22

 

Related articles