Page tree
Skip to end of metadata
Go to start of metadata

Replacement of CLF (column-level formulas)

Since 2.10, formulas for cells use a simpler, more powerful language (see Formulas 2). Formulas for totals and columns (CLF, column-level formulas) keep using the old SQL syntax, which is very difficult to understand and inconsistent with the Formulas 2 syntax. We hope to remove the old SQL syntax and replace it in the future. The notable point is, formulas for columns currently don't update the contents of the column; They just replace what is displayed when viewing the table.

This page explains the old SQL syntax, used for CLFs, which is difficult to understand and may be replaced.

If you need to use formulas, please rather use cell-level formulas, using the Formulas 2 syntax.

 

With Totals, users can write a SQL formula and display results at the bottom of the tables. It requires a bit of SQL knowledge but it is just as powerful as Excel formulas. Start small!

In Column formulas only

ExamplePrerequisiteFormulaParameters
Link to Google MapsThe field must be of type "Link"

= TO_URL('http://www.google.com?q=%s', address, address || ' on Google Maps')

Example: Link to 341 George Street, Sydney

Parameter 1: The url with %s where the expression will be replaced

Parameter 2: The column name with the searched expression

Parameter 3: Optional - The label of the link

Value from another table-

= othertable.column WHERE othertable.id = x

Example: Display the Name of a person instead of their ID

othertable: The name of another table

column: The column you want to display

x: The column of the current table, which serves as a foreign key

Any other SQL-

(SELECT * FROM othertable)

Example: Display the value of another column

There is no '=' for SQL formulas.

Any SQL is permitted. It could be as simple as the name of another column, or a litteral label between single quotes ('Ok').

Parenthesis are optional for simple expressions.

In both Totals and Column formulas

ExampleLevelFormulaResult
Best score(grey lightbulb) BeginnerMAX(points)82
Average score(grey lightbulb) BeginnerROUND(AVG(points), 1)40.3
Formatting a currency(lightbulb) IntermediateTO_CHAR(SUM(price), 'L 999G999D99')$ 4,432.83
Formatting the result(lightbulb) IntermediateSUM(points) || ' points'824 points
Number of people without score(star) Expert(SELECT COUNT(*) FROM games WHERE points IS NULL)1
Advanced formatting(star) Expert(SELECT FORMAT('%s (%s points)', name, points)
FROM games ORDER BY points DESC LIMIT 1)
John (82 points)

 

Aggregation Operators

SQL has hundreds of operators (see Postgresql's documentation page). Here's the ones you may use most often.

Aggregation Operators on numbers

OperatorResult

MIN(column)

MAX(column)

The min/max.
SUM(column)The sum of all number in the column
AVG(column)The average
ROUND(column, 2)Rounding to 2 decimals

Operators on text

OperatorResult

TO_CHAR(SUM(price), 'L 999G999D99')

Also try the double-colon: SUM(price)::numeric::money

$ 432,111.02

  • L is replaced by the currency of the database,
  • 0 and 9 will be replaced by numbers (mandatory or optional)
  • G is the group separator (thousands)
  • D is the decimal separator
  • Decimals are rounded
column || ' American Dollars'Concatenation of 'column' and ' American Dollars'
CASE column WHEN 1 THEN 'One' ELSE 'Not one' ENDConditional result

LOWER(column)

UPPER(column)

Change to lowercase / uppercase
POSITION('president' IN column)Position of the substring 'president' in the text of the column
LENGTH(column)Length of the text

Subqueries

The formula you're writing is executed in a query, so you can write subqueries, provided you enclose it in parenthesis.

ExampleQueryResult
Sum if...(SELECT SUM(points) FROM games WHERE points > 24)Sum all points above 24
Join (or "Lookup" or "VLOOKUP")

(SELECT address
FROM people
WHERE people.name = (SELECT name FROM games ORDER BY points DESC LIMIT 1))

Joins the two tables to get the address of the winner:

Table "games"
namepoints
Marsha939
Anna23
Table "people"
nameaddress
Marsha83/3 Darlinghurst Road
Your forumla will be inserted in a query of this form:
SELECT [formula 1], [formula 2], ...
FROM (the table)
LIMIT 1
  • No labels