Skip to main content

Calculated Columns in a database - Knowledgebase Articles / Functions of Kahootz / Databases - Software Support

Calculated Columns in a database

Authors list

Calculations

You can set a text, long text, formatted text, number or date column in a Kahootz Database to be calculated instead of directly entered. This means that the values in that column are calculated based on other values in the entry each time it is saved - for example adding up a set of other number columns, or combining some text.

Your calculation can be a simple expression, such as adding or multiplying values together, or it can contain code and logic. There is a range of operators and functions you can use in your calculation. More information about the operators and functions you can use is given below.

If you add or update a calculation on a column, the existing database entries will be updated in the background; For a database with lots of rows, this can take some time. Calculations will also be updated when you add or change an entry.

> A calculation column is added after you've initially created a database by following these simple steps below.

1. Open the database you wish to add a calculated column too, under the "Actions" section, select "manage database"
2. Select the row you wish to apply an expression too, at the bottom under the "other options" section - tick the checkbox for calculated - shown below.

3. Add expression or code and click save - eg: please see example below.

* This would allow the following database below to add all the hours from each week day, to then totals them all together into the last column.
- Note: adding val ( {{column}} ) ignores any blank columns within the database

> Please see below for more details on formats, expressions & coding.

 

Basic Format

You can use the value of other columns in your calculation by putting the column name between {{ and }}. A list of the available column names will be shown on the add / modify column page when you're adding calculations, and you can click on them to insert them into the calculation.

You can use round brackets () to make sure your calculation is evaluated in the order you expect - so ( 2 + 4 ) / 2 will do the addition first, then the division - giving 3. Without the brackets, 2 + 4 / 2, normal mathematical precedence will apply, so it will do 4 / 2 first, then + 2, giving 4. 

You can search and sort on calculated columns.

You can use one calculated column in another calculation. They are evaluated in column order, so if you want to use one calculation in another, make sure the first one is higher up the column order.

If there is an error from your code due to particular inputs for a row, the column will be set to blank.

 

Simple Expressions

Examples

Adding two number columns together: {{days on activity 1}} + {{days on activity 2}}

Multiplying two number columns together: {{cost per hour}} * {{total hours}}

Showing one number column as a percentage of another: {{hours spent on activity}} / {{total hours}} * 100

Showing that percentage as a whole number (no decimal places):
int( {{hours spent on activity}} / {{total hours}} * 100 )

Work out the number of days between two dates: daysBetween( {{start date}}, {{end date}} )

 

Numbers

Numeric operators

You can use the following operators to combine numbers:

Additionnumber1 + number2 Example: To calculate the total number of days spent on activity1 and activity2:
{{days on activity1}} + {{days on activity2}}
Subtractionnumber1 - number2 Example: To calculate the cost after discount:
{{Original cost}} - {{discount}}
Multiplicationnumber1 * number2 Example: To calculate the total cost of a number of items:
{{Cost per item}} * {{number of items}}
Divisionnumber1 / number2 Example: To calculate the cost per hour:
{{Total cost}} / {{number of hours}}
Integer divisionnumber1 \ number2 

How many times one number can be divided by another in whole numbers, ignoring the remainder.
eg:5 / 2 = 2.5 but 5 \ 2 = 2

Division-remaindernumber1 mod number2 The remainder after dividing one number by another.
eg: 5 mod 2 = 1 (2*2=4, with 1 remaining)
Blank column/valueval ( {{column}} )In all these operators, if a numeric column/value is blank it'll be treated as an error, to treat empty columns as 0.
Use this expression - please refer to the example/screenshot above.

 

Number Functions

You can use the following functions to manipulate numbers:

Absolute valueabs( number )The absolute value of a number is the number without a sign, eg: abs(2) = 2 and abs(-2) = 2
Roundinground( number )Gives the closest whole number, rounding up or down as nearest.
eg: round(1.1) = 1 and round(1.9) = 2
Halves will be rounded to the nearest even number to avoid bias
eg: round(1.5) = 2 and round(2.5) = 2 and round(3.5) = 4
Rounding upceiling( number )Gives the closest whole number, always rounding up.
eg: ceiling(1.1) = 2 and ceiling(1.9) = 2
Rounding downint( number )Gives the closest whole number, always rounding down.
eg: int(1.1) = 1 and int(1.9) = 1
Maximummax( number1, number2 )Return the maximum of number1 and number2. Only handles two numbers, not more.
Minimummin( number1, number2 )Return the minimum of number1 and number2. Only handles two numbers, not more.

 

Text

Text Operator

Joining texttest1 & text2 

Note that this does not use + which is for adding numbers. You will also need to put in spaces explicitly where wanted.
eg: {{first name}} & " " & {{surname}}

Text Functions

Comparing textcompare( text1, text2 )
compareNoCase( text1, text2 )
Performs a case-sensitive or insensitive comparison of two text columns.
Return a negative number if text1 is less than text2; returns 0 if text1 is equal to text2; returns a positive number if text1 is greater than text2.
Find positionfind( text_to_find, text )
findNoCase( text_to_find, text )
Finds the first occurrence of a text_to_find in text. find is case sensitive, findNoCase is not.
Returns the position of text_to_find in text; or 0, if text_to_find is not in text
Insert at positioninsert( text_to_insert, text, position )Return text with text_to_insert inserted into text after character position. If position=0, it prefixes text_to_insert to text.   eg:insert(" My ","Hello Friend",5) returns "Hello My Friend"
Remove from positionremoveChars( text, start_position, num_chars )Return a text with num_chars removed starting at position start_position.
eg:removeChars("Hello Friend",5,7) returns "Hello"
Convert to lower caselcase( text )Return text converted to lower case.
Convert to upper caseucase( text )Return text converted to upper case.
Reversereverse( text )Return text in reverse order.
eg: reverse("kahootz") returns "ztoohak"
Length of textlen( text )Return the length - how many characters - are in text. Includes spaces and other punctuation.
Characters from leftleft( textnum_chars )Return the leftmost num_chars characters of text. Counting includes spaces and other punctuation.
Characters from rightright( textnum_chars )Return the rightmost num_chars characters of text. Counting includes spaces and other punctuation.
Characters from positionmid( text, start_positionnum_chars )Return num_chars of characters from text starting at position start_position.eg: mid("kahootz",3,4) returns "hoot"
Find and replacereplace( text, remove, insert [, scope] )
replaceNoCase( text, remove, insert [, scope] )
Return text with occurrences of remove replaced by insert. If the scope is "1" then just the first occurrence is replaced.
If the scope is "ALL" then all occurrences are replaced.
(Versions using Regular Expressions for very advanced use are available - ask support!)
Substring untilspanExcluding( text, characters_to_exclude )Return characters from text, from the beginning until the first character in characters_to_exclude. The search is case sensitive, so if you want to stop at either A or a, then put both in characters_to_exclude.
eg: spanExcluding("kahootz.doc",".,/") returns "kahootz"
Substring until notspanIncluding( text, characters_to_include )Return characters from text, from the beginning until the first character that is NOT in characters_to_include. The search is case sensitive, so if you want to include both A and a, then put both in characters_to_include.
eg: spanIncluding("aardvark","aeiou") returns "aa"
Trim spacestrim( text )Return text with any leading and trailing spaces removed.
Trim leading spacesltrim( text )Return text with any spaces at the beginning removed.
Trim trailing spacesrtrim( text )Return text with any spaces at the end removed.
Convert to numberval( text )Return text converted to a number. Handles decimal places. Text that can't be returned to a number will cause an error, and thus a blank calculated column (but see conditional operator 'isNumeric() in the code section below)

 

Dates and Times

Date values in the following functions can either be taken from columns (of date, date and time, month and year, entry creation date / date-time or entry modify date / date-time types) or entered as explicit dates in the format yyyymmdd - eg 20170401 is 1st April 2017

Time values in the following functions can either be taken from columns (of date and time, time, entry creation date-time or entry modify date-time types) or entered as explicit times in the format hhmmss

To show a calculated value in a 'date' column the result must be a valid date, but you can use the other result formats in text or number columns.

Date Functions - returning a number

Day of WeekdayOfWeek( date )Return a number for the day of the week of date in the range 1 (Sunday) to 7 (Saturday)
Day Of YeardayOfYear( date )Return a number of the day of the year, in the range 1 (1st Jan) - 365  (31st Dec - or 366 in leap year)
Days in MonthdaysInMonth( date )Returns the number of days in the specified month (ie: 28, 29, 30 or 31)
Days In YeardaysInYear( date )Return the number of days in the specified year (ie: 365 or 366 for leap years)
Parts of a Date / Timeyear( date )
month( date )
day( date )
hour( time )
minute( time )
Return a number for the appropriate part of the specified date/time. Year is returned in four figures (2017); Month as 1-12; Day as 1-31; Hour in 24-hour notation as 0-23; Minute as 0-59
Days afterdaysAfter( date1, date2 )Return the number of days that date2 is after date1. If date2 is before date1, a negative number is returned.
If either is not a valid date, then empty text is returned.
Days betweendaysBetween( date1, date2 )Return the number of days between date1 and date2. It doesn't matter which date is earlier, and will always return a positive number. If either is not a valid date, then empty text is returned.
Date / Time DifferencedateDiff(datepart, date1, date2 )

Return the number of "units" by which date1 is less than date2. datepart should be one of the following strings

  • "yyyy": Years
  • "q": Quarters (any 3 month period)
  • "m": Months
  • "d": Days
  • "ww": Weeks
  • "h": Hours
  • "n": Minutes
If date2 is before date1, a negative number is returned. If either is not a valid date, then empty text is returned.
Date / Time ComparisondateCompare( date1, date2 )Return -1 if date1 is earlier than date2; Return 0 if date1 is the same as date2; Return 1 if date1 is later than date2; Accurate to the second if used with date-times or times.
Current Datenow ()Uses the date the entry was last saved or updated of which can be used in various calculations, see below.

For example, you have a database using a "date" column and you want to return the total number of days the entries have been open/outstanding.
You can use this function to show the elapsed days between the created date and today's date by adding "now ()" to the calculation as shown below.

Kahootz Tip: The example above will not update automatically, therefore, when you view the database the next day - the values will not have changed.
The calculation for "current date" uses the date of when the calculation was last saved/updated - (please remember this if you're going to use this value)


Date Functions - returning a date

Add to / Subtract from a datedateAdd(datepart, number, date )

Return a new date by adding the specified number of units to date. datepart should be one of the following strings

  • "yyyy": Years
  • "q": Quarters
  • "m": Months
  • "d": Days
  • "w": Weekdays (Mon-Fri, skipping Sat and Sun. Simple addition, not aware of public holidays etc)
  • "ww": Weeks
  • "h": Hours
  • "n": Minutes
If number is positive you'll get dates after date, ie: forwards in time.
To go backwards in time use a negative value for number.
Create DatecreateDate( year, month, day )Create a date from three numbers, eg: CreateDate(2017,2,14) represents 14th Feb 2017
Create Date - TimecreateDateTime( year, month, day, hour, minute, second )Create a date-time from six numbers, eg: CreateDateTime(2017,2,14,15,5,17) represents 14th Feb 2017 15:05:17 - just after 3pm

 

Writing Code

As well as simple expressions, you can write code to make decisions.

There is a range of tags and logical operators for this. You can also use variables in your code to store intermediate values.

When you write code, you must set a variable called calcResult which will be displayed in the database cell.


Tags

<SET var_name = expression> - Set the variable var_name to the result of calculating expression.  Note that all variable names must begin with the string "var_".

Conditional Operators - return true or false, used in IF or ELSEIF conditions

The logical operators AND, OR and NOT are supported, returning true or false

 value1 EQ value2  - Test if value1 equals value2 - works on both numbers and text (case insensitive)

 value1 NEQ value2  - Test if value1 is not equal to value2- works on both numbers and text (case insensitive)

 number1 GT number2  - Test if number1 is greater than number2

 number1 GTE number2  - Test if number1 is greater than or equal to number2

 number1 LT number2  - Test if number1 is less than number2

 number1 LTE number2  - Test if number1 is less than or equal to number2

isNumeric( text ) - Test if text can be converted to a number - true if it can, false if it can't. (eg: can be used to check if something is a valid number, and explain the error if it can't rather than let the calculation fail and return blank.)


Using Conditions

<IF condition>
    CODE
 </IF>
Executes CODE if the condition is true.
<IF condition1>
    CODE1
 <ELSEIF condition2>
    CODE2
 <ELSEIF condition3>
    CODE3
 <ELSE>
    CODE4
 </IF>
Executes one of the CODE blocks depending on which condition is true. If none of the conditions are true then the CODE following <ELSE> is executed (CODE4). You can have as many <elseif condition> blocks as you like, but only one <else>.


Example

<SET var_daysAfterTargetDate = daysAfter( {{delivery date}}, {{planned delivery date}} )>

<IF var_daysAfterTargetDate EQ "">  <SET calcResult = "Bad date!">
<ELSEIF var_daysAfterTargetDate LT 0>  <SET calcResult = "Early">
<ELSEIF var_daysAfterTargetDate EQ 0>  <SET calcResult = "On time">
<ELSE>  <SET calcResult = "Late">
</IF>

Helpful Unhelpful