Getting Started with Column Type - Formulas

Let's learn about one of the most powerful column types in Stackby: Formulas. 

What are Formulas?
How are they different from spreadsheet formulas? 
Types of Formulas

Take this 30-minutes live tutorial to learn everything about Stackby Formulas: 

Formulas 

You can represent a single column type in a different way or compute between two or more different column types (Text or Number) using Formulas. 

It may involve numerical operations, logical operations, functions and text operations that operate on columns. 

How are they different from spreadsheet formulas? 

Unlike spreadsheet formulas, that operate on individual cell (A2, B3), formulas in Stackby operate on each column (defined by column name). It functions like a database, and columns are first-class citizen instead of cells. 

Watch the video on Formulas and Calculations in Stackby: 

Types of Formulas 

• Numerical operators & Functions
• Logical operators & Functions
• Date and Time Functions
• Text Functions 
• Array Functions (Only used in Aggregation column type) 
• REGEX Formulas
• Other Formulas 

A note about formula syntax:

• All functions are denoted in upper case and the parameters required for them are surrounded by round brackets ( )
• All Optional parameters are surrounded by square brackets [ ]
• All column type parameters are surrounded by curved braces { }

Below is the table for all the formulas in Stackby for your reference. Copy this stack and add it your workspace for reference. 

Numerical operators & Functions

Numerical operators:

• Numeric operators are used with logical functions
• Numerical operators return a numeric value 
• We support 4 numerical operators

'+' : Adds two numerical values
Example: {Column Name} + 3 

'-' : Subtracts two numerical values 
Example: {Column Name} - 3.00 

'*' : Multiply two numerical values
Example: {Size}*{Price}

'/' - Divide two numerical values 
Example: {Weight}/{Height} 

Numerical functions: 

1. ABS(value) : Returns the absolute value.

2. AVERAGE(number1, [number2, ...]) : Returns the average of the numbers.

3. CEILING(value, [significance]) : Returns the nearest integer multiple of significance that is greater than or equal to the value. If no significance is provided, a significance of 1 is assumed.

4. COUNT(number1, [number2, ....]) : Count the number of numeric items.

5. COUNTA(textOrNumber1, [number2, ....]) : Count the number of non-empty values. This function counts both numeric and text values.

6. COUNTALL(textOrNumber1, [number2, ....]) : Count the number of all elements including text and blanks.

7. EVEN(value) : Returns the smallest even integer that is greater than or equal to the specified value.

8. EXP(power) : Computes Euler's number (e) to the specified power.

9. FLOOR(value, [significance]) : Returns the nearest integer multiple of significance that is less than or equal to the value. If no significance is provided, a significance of 1 is assumed.

10. INT(value) : Returns the greatest integer that is less than or equal to the specified value.

11. LOG(number, base=10) : Computes the logarithm of the value in provided base. The base defaults to 10 if not specified.

12. MAX(number1, [number2, ...]) : Returns the largest of the given numbers.

13. MIN(number1, [number2, ...]) : Returns the smallest of the given numbers.

14. MOD(value1, divisor) : Returns the remainder after dividing the first argument by the second.

15. ODD(value) : Rounds positive value up the the nearest odd number and negative value down to the nearest odd number.

16. POWER(base, power) : Computes the specified base to the specified power.

17. ROUND(value, precision) : Rounds the value to the number of decimal places given by "precision." (Specifically, ROUND will round to the nearest integer at the specified precision, with ties broken by rounding half up toward positive infinity.)

18. ROUNDDOWN(value, precision) : Rounds the value to the number of decimal places given by "precision," always rounding down, i.e., toward zero. (You must give a value for the precision or the function will not work.)

19. ROUNDUP(value, precision) : Rounds the value to the number of decimal places given by "precision," always rounding up, i.e., away from zero. (You must give a value for the precision or the function will not work.)

20. SQRT(value) : Returns the square root of a nonnegative number.

21. SUM(number1, [number2, ...]) : Sum together the numbers. Equivalent to number1 + number2 + ...

22. VALUE(text) : Converts the text string to a number.

23. SUB({column1}, {number2}): Subtract the number. Equivalent to (value of column1) - (value of column2)

Logical Operators & Functions

Logical operators:

• Logical operators are used with logical functions
• Logical operators return a TRUE or FALSE value when compared with the data in cells. This value is then used by logical functions for further processing.
• We support 6 logical operators

'>' : Greater than
Example: {ColumnName} > 15

'<' : Less than
Example: {ColumnName} < 15

'>=' : Greater than or Equal to
Example: {ColumnName} >= 15

'<=' : Less than of Equal to
Example: {ColumnName} <= 15

'==' : Equal to
Example: {ColumnName} == 15

'!=' : Not Equal to
Example: {ColumnName} != 15

Logical functions:

1. AND(logical1, [logical2, ...]) : Returns true if all the arguments are true, returns false otherwise. 
   Example: AND({Employee}, {Employer})

2. BLANK() : Returns a blank value.
   Example: IF({Value} > 1000000, "Value less than 1000000", "Value more than 1000000")

3. ERROR() : Returns the error value.
   Example: IF({Grade} == "F", ERROR(), "OK")

4. FALSE() : Logical value false.

5. IF(<Condition>, <exp1>, <exp2>) : Returns 'exp1' if the logical argument is true, otherwise it returns 'exp2'. 
   - Exp1/Exp2 can either be values, text, or other conditions
   - You can also add more IF statements in exp1/exp2
   - Can also be used to check if a cell is blank/is empty.
   
   Example: 
   - IF({GPA} > 2.5, "Good", "Bad")
   - IF({Date} = BLANK(), "Please enter date", "Date entered")
   - IF({Sky} == "Clear", IF({Temp} < 30, "Great weather!", "I'm burning!"), "Disappointing Weather.")

6. ISERROR(expr) : Returns true if the expression causes an error. 
   Example: ISERROR(SQRT(-99))

7. NOT(boolean) : Reverses the logical value of its argument. 
   Example: NOT(Total > 50)

8. OR(logical1, [logical2, ...]) : Returns true if any one of the arguments is true. 
   Example: OR({Fire}, {Smoke})

9. TRUE() : Logical value true. 
   Example: IF({Value} > 50, TRUE(), FALSE())

10. XOR(logical1, [logical2, ...]) : Returns true if an odd number of arguments are true. 
    Example: XOR(TRUE(), {Complete}, {Incomplete})

11. SWITCH(): It is an alternative to IF statement.
    Example: SWITCH({Name},4, ‘Planning’, 3, ‘Execution’, 2, ‘Review’, 1, ‘Launch’, ‘Out of range’)

Text Functions

1. CONCAT() : Concatenates all the column values together 
   Example: CONCAT({Column1},{Column 2},{Column 3}) 
   Note: If you need space between columns, you need add it like below 
   CONCAT({Column1}," ",{Column 2}," ",{Column 3})

2. SUBSTITUTE() : Replaces occurrences of old_text with new_text.
   Example: SUBSTITUTE("gold mold", "old", "et")  -> get met
   Note: You can optionally specify an index number (starting from 1) to replace just a specific occurrence of old_text. If no index number is specified, then all occurrences of old_text will be replaced.
   SUBSTITUTE("gold mold", "old", "et", 1) -> get mold

3. UPPER() : Makes string uppercase.
   Example: UPPER("Hello!") => HELLO!

4. LOWER() : Makes string lowercase.
   Example: LOWER("Hello!") => hello!

5. T() : Returns the argument if it is text and blank otherwise.
   Example: T("text only")  => text only  T(42) => blank

6. REPT(): This function repeats any references string by a specified number of times.
   Example: REPT("Hi!",2) => Hi!Hi!

7. LEN() : Returns the length of a string.
   Example: LEN("quick brown fox")  => 15

8. REPLACE() : Replaces the number of characters beginning with the start character with the replacement text.
   Example: REPLACE("database", 2, 5, "o")  => dose
   Note: If you're looking for a way to find and replace all occurrences of old_text with new_text, see SUBSTITUTE().

9. SEARCH() : Searches for an occurrence of stringToFind in whereToSearch string starting from an optional startFromPosition. For no occurence, Result is Empty
   Example: SEARCH("Hello World","World")  => 7
   Note: Similar to FIND(), though FIND() returns 0 rather than empty if no occurrence of stringToFind is found.

10. RIGHT() : Extract howMany characters from the end of the string.
    Example: RIGHT("quick brown fox", 5)  => n fox

11. LEFT() : Extract how Many characters from the beginning of the string.
    Example: LEFT("quick brown fox", 5)  => quick

12. MID() : Extract a substring of count characters starting at whereToStart.
    Example: MID("quick brown fox", 6, 5)  => brown

13. TRIM() : Removes whitespace at the beginning and end of string.
    Example: TRIM(" Hello! ")  => Hello!

Date and Time Functions

1. NOW () : Returns the current time and day.
   Example: DATE_ADD(NOW(), 5, "days")
   

2. TONOW() : Displays relative time to now.
   Example : TONOW({Deadline})
   

3. FROMNOW() : Displays relative time from now.
   Example: FROMNOW({Deadline})
   

4. CREATED_TIME() : Returns the creation time of the current record.
   Example: CREATED_TIME()
   

5. DATE_ADD() : Adds a "count" units to Datetime, such as DATE_ADD(Datetime, count, unit)
   Example: DATE_ADD(Datetime, 5, "days")
   

6. DATE_SUB() : Substracts a "count" units to Datetime such as DATE_SUB(Datetime, count, unit)
   Example: DATE_SUB(Datetime, 5, "days")
   

7. DATE_DIFF() : Returns the difference between datetimes in specified units such as DATE_DIFF(From, To, units)
   Example: DATE_DIFF({Deadline}, NOW(), "days")
   

8. DATETIME_FORMAT(): Formats a datetime into a specified string such as DATETIME_FORMAT(Datetime, format string)
   Example: DATETIME_FORMAT(NOW(), "YYYY-MM")
   

9. DATETIME_PARSE() : Parses the datetime string with optional format and locale such as DATETIME_PARSE(Datetime, format, local)
   

10. SET_LOCALE() : Sets a specific locale for the datetime, such as SET_LOCALE(Datetime)
    Example: SET_LOCALE(NOW(), "ru")
    

11. SET_TIMEZONE() : Sets a specific timezone for a datetime such as SET_TIMEZONE(Datetime, timezone)
    Example: SET_TIMEZONE(NOW(), "Australia/Sydney")
    

12. MONTH() : Returns the month part of the datetime such as MONTH(date)
    Example: MONTH({Completion date})
    

13. YEAR() : Returns the year part of the datetime such as YEAR(date)
    Example: YEAR({Completion date})
    

14. DAY() : Returns the day part of the datetime such as DAY(date)
    Example: DAY({Completion date})
    

15. TIMESTR() Formats a datetime into a time-only string (HH:mm:ss) such as TIMESTR(Datetime)
    Example: TIMESTR(NOW())
    

16. DATESTR() : Formats a datetime into a string (YYYY-MM-DD) such as DATESTR(Datetime)
    Example: DATESTR(NOW())
    

17. HOUR() : Returns the hour part of the datetime such as HOUR(date)
    Example: HOUR({Completion date})
    

18. MINUTE() : Returns the minute part of the datetime such as MINUTE(date)
    Example: MINUTE({Completion date})
    

19. SECOND() : Returns the second of the datetime such as SECOND(date)
    Example: SECOND({Completion date})
    

20. WEEKDAY() : Returns the day of the week as an integer between 0 (Sunday) and 6 (Saturday)
    Example: WEEKDAY({Date})
    

21. WORKDAY_DIFF() : Counts the number of working days between startDate and endDate. Working days exclude weekends and an optional list of holidays, formatted as a comma-separated string of ISO-formatted dates such as WORKDAY_DIFF(startDate, endDate, [holidays])
    Example: WORKDAY_DIFF({Assignment date}, {Due Date}, "2019-09-04,2019-10-09,2019-11-10")
    

22. ISAFTER() : Checks if date A is after B such as ISAFTER(A, B)
    Example : ISAFTER({Deadline},NOW())
    

23. ISBEFORE() : Checks if date A is earlier than B such as ISBEFORE(A, B)
    Example: ISBEFORE({Deadline}, NOW())
    

24. ISSAME() : Checks if the datetimes are the same up to some unit such as ISSAME(A, B, units)
    Example: ISSAME({Deadline}, NOW(), "hour")

25. WEEKNUM(Date) : Returns the week number of the year. Example: WEEKNUM("04/01/2021") will return 1

26. WORKDAY(startDate,numDays,[holidays]) : Returns a date that is numDays working days after startDate. Working days exclude weekends and list of holidays formatted as comma-seperated string in ISO dates. Example: WORKDAY({Column_name},100,"2019-09-04,2019-10-02")

Array Functions:

1. ARRAYJOIN() : Join the array of aggregation items into a string with a separator. 
   Example : ARRAYJOIN(Values)

2. ARRAYUNIQUE() : Returns only unique items in the array. 
   Example : ARRAYUNIQUE(Values)