Calculated Columns

There are various calculations and commands which can be executed through these custom fields.

The 'Calculated Columns' editor can be used to create a custom function for a particular field.

📋 Steps to Create a Calculated Columns

1. Click on columns
2. Click on the 'Add Custom Column' icon.

3. Click on 'Calculated Columns'

4. Enter the field name in the respective space provided.

5. Click on the blank space below to write a custom function. The function needs to be written in PostgreSQL.

Note: Click on Show Documentation to view various functions that you can use. It is recommended to use this feature with basic knowledge of PostgreSQL.

Hint: Type a keyword for the field to receive suggestions to add t the editor.

String

These are the commands available under the 'String' format along with its Syntax.

• Character Length

  char_length([field_name])

• All characters in Lower Case

  lower(string)

• All letters in Upper Case

  upper(string)

• String Concatenation 2

  'String: ' || integer

Numbers

These are the commands available under the 'Number' format along with its Syntax.

• Adding two or more field values

  [field_name1] + [field_name2]

• Subtracting two or more field values

  [field_name1] - [field_name2]

• Multiplying two or more field values

  [field_name1] * [field_name2]

• Dividing two field values

  [field_name1] / [field_name2]

• Limit or Truncate the decimal values to 'n' number of decimal places.

  trunc([field_name],n)

Boolean

• Greaater Than ( > ) : Returns 'true' if first condition is greter than second condition, else return 'false'

  [field_name1] > [field_name2]

• Lesser Than ( < ) : Returns 'true' if the first condition is greter than the second condition, else return 'false'

  [field_name1] < [field_name2]

• Greater than or Equal to ( >= ) : Returns 'true' if the first condition is greater than or equal to the second condition, else return 'false'

  [field_name1] >= [field_name2]

• Lesser than or Equal to ( <= ) : Returns 'true' if the first condition is less than or equal to the second condition, else return 'false'

  [field_name1] <= [field_name2]

• Equal to ( = ) : Returns 'true' if the first condition is equal to the second condition, else return 'false'

  [field_name1] = [field_name2]

• Not Equal to ( != ) : Returns 'true' if both conditions are not equal else, return 'false'

  [field_name1] !=[field_name2]

Date & Time

• Display Current date

  current_date

• Extracts only Day from Order Date

  extract (day from [Order Date])

• Extracts only Month from Order Date

  extract (month from [Order Date])

• Extracts only Year from Order Date

  extract (year from [Order Date])

Other custom fields

Trunc: Trunc removes the fractional part of the number and rounds numbers down to the nearest integer based on the value of the fractional part of the number.

trunc(([fieldnme])::numeric, 2)

Round: The round() function rounds a number to a specified number of decimal places.

round(([fieldnme])::numeric, 2)

Float: Float is used to store approximate values, not exact values. It has a precision from 1 to 53 digits.

float(([fieldnme])::numeric, 2)

Concat: Concat() function adds two or more expressions together.

concat([field_1],' ',[field_2],' ',[field_3])