Custom fields
A custom field is a calculated column you add to one widget: an average price, a sales band, a share of the category, two fields joined into one. You build it in a wizard by clicking fields and operators, and the app writes the SQL. If you know SQL, you can also write the expression yourself on the SQL tab.
Custom fields belong to the widget, not the dataset. They are worked out in that widget’s query, and another widget on the same dataset doesn’t see them. If many widgets need the same calculation, add it to the dataset instead (in custom SQL if a model dataset can’t express it).
Open the wizard
Section titled “Open the wizard”Every field picker in the Dashboard Builder ends with Custom… (calculator icon). Pick it to create a custom field for that spot. When the picker already holds a custom field, Edit Custom Field opens it again.
-
In the Dashboard Builder, select the widget and open the Configuration tab.
-
Click the field picker you want to fill, for example a column’s field in Table Columns Configuration.
-
Scroll to the end of the list and pick Custom….
-
Build the field (see the modes below). On a table, also type a Field Label: it becomes the column header.
-
Click Apply, then Save & Preview.
Apply stays disabled until the field is complete; hover over it to see what’s missing. Cancel closes the wizard without changes.
Which modes each picker offers
Section titled “Which modes each picker offers”| Opened from | Modes |
|---|---|
| A table column | Arithmetic (with Remaining Quantity (FIFO) and Percent of Total (Share)), Condition, Format, Combine, SQL |
| A tile’s data field | Arithmetic, Condition, Format, SQL |
| A chart’s measure | Arithmetic, Condition, Format, SQL |
| A chart’s dimension | Condition and SQL |
| A chart’s Stacking Group By | Arithmetic, Condition, Format, SQL, one value per row only (see below) |
| A Matrix Report measure | Arithmetic, Condition, Format, SQL |
| A filter’s Filter By | Arithmetic, Condition, Format, SQL |
Only table columns have a Field Label. Elsewhere the widget’s own labels apply.
A chart’s Stacking Group By needs one value per row, so a field built there can’t use SUM, AVG, MIN, MAX or COUNT, and the Condition mode has no Wrap Result.
Arithmetic
Section titled “Arithmetic”Use it for any sum, difference, ratio or date calculation: average price, margin, days between two dates.
- Expression is built from left to right. Click a chip under Numeric Fields to add a field, an operator (+ − × ÷), or type a number under Numeric Value and click Add value. The × on a piece removes it.
- Each field in the expression has its own aggregation picker: none, SUM, AVG, MIN, MAX, COUNT or COUNT DISTINCT. In a table that groups rows, aggregate every field, as in SUM(sales) ÷ SUM(quantity) above.
- Wrap Expression puts one aggregation around the whole expression, for example
SUM( quantity × price ). - Empty values count as 0.
- SQL Operand adds a piece of SQL as one term of the expression, for anything the chips don’t offer:
order_date::date,COUNT(*), or a guard against dividing by zero such asNULLIF(COUNT(DISTINCT partner), 0). Type it and click Add SQL (or press Enter). To change it later, click it in the expression, edit it in place and press Enter, or click elsewhere. Esc puts the term back as it was. - Date Tools (when the source has date fields) add intervals (Add INTERVAL), extract a part such as the month (Add EXTRACT), truncate to the start of a period (Add DATE_TRUNC), use NOW() or CURRENT_DATE, and compute the gap between two dates in days, months or years (Add difference).
- On a table with date filters, Date Filter Range inserts the picked Date from or Date to of one filter into the expression. With no range picked, they read as minus and plus infinity.
The wizard refuses expressions that end with an operator, have two operators in a row, or divide by the number 0.
Remaining Quantity (FIFO)
Section titled “Remaining Quantity (FIFO)”Tables only. It caps each row’s quantity at what is still available after the outflows of its group, in first-in-first-out order: for example how much of each purchase receipt is still in stock once all sales are taken out.
- Signed Quantity: positive for inflows, negative for outflows.
- Partition By (Group): what the stock is counted per, such as the product.
- Order By (Sequence): the order in which inflows are used up, such as the date.
All three are required. Your source needs one row per movement with a signed quantity, which usually means a custom SQL dataset.
Percent of Total (Share)
Section titled “Percent of Total (Share)”Tables only. It gives each row’s share of the overall total, or of its group: a product’s share of its category’s sales.
-
Pick the Measure, the value whose share you want.
-
To get the share within a group, add a field under Partition By (Group), such as
category. Leave it empty for the share of all rows. -
Apply, then open the column’s Column Settings and set its format to Percentage. The field produces a ratio (0.2 for 20%).
Which measure to pick depends on the table:
- On a table with Group By (one row per product, say), the measure must be aggregated. First add an Arithmetic custom field such as SUM(sales), then pick it as the measure. A plain column there makes the table fail with a grouping error.
- On a table without Group By (one row per record), pick the plain column. The share is then worked out per record.
The share is of the rows the table reads, after its filters. With a row limit, it’s still the share of everything that passed the filters, not of the rows on screen. See also Totals and % of group.
Condition
Section titled “Condition”Use it to put rows into bands or rename values: “Top seller / Steady / Slow”, “Overdue / Due”, a readable label for a technical status.
- Wrap Result puts one aggregation around the whole condition, so it adds up a value per row:
SUM( CASE … END )totals the balance of asset accounts only. It offers SUM, AVG, MIN, MAX, COUNT and COUNT DISTINCT. Tick Negate to multiply the result by −1, for liabilities and equity that are stored as negative balances. - Each When / Else When branch has one or more conditions: an optional aggregation, a field, a comparison and a value. Add sub-condition adds another, and the AND/OR button sets how they combine.
- The code button (</>) before the field switches the left-hand side to SQL, for a value that isn’t a column, such as
CURRENT_DATE - due_date(days overdue). Click it again to go back to a field. - Comparisons depend on the field: equals, greater than, before, contains, starts with, is empty and so on. Number and text fields also have is one of and is not one of, and true/false fields have is true and is false.
- The box after the comparison sets what you compare with: a Value, another Field, or SQL. For is one of, type the values separated by commas (
asset_cash, asset_receivable); the wizard quotes them to match the column, so text gets quotes and numbers don’t. is one of offers Values or SQL, not Field. - Then is the result, again a Value, a Field or SQL.
- Add condition adds a branch. Branches are checked from top to bottom; drag the handle to reorder them.
- Otherwise sets the result when no branch matches: a Value, a Field, SQL, or Nothing, which leaves those rows empty.
On a chart’s dimension, Condition is the only mode, so you can group a chart by bands.
Format
Section titled “Format”Use it to change how one value looks without changing the data: a date as “January 2024”, a number rounded to 2 decimals, a name in capitals.
Pick the Field; the wizard shows its type and the operations that fit:
- Date: Extract part (year, quarter, month, week, day…), Truncate to (year start, month start, day…), or Format as text with a Pattern. Click a preset or type a PostgreSQL
TO_CHARpattern. - Number: Round, Round up (ceil), Round down (floor), Absolute value, Multiply by 100 (percent), with Decimal places for rounding.
- Text: UPPERCASE, lowercase, Proper Case, Trim whitespace, Character count.
Format works on one value per row. In a grouped table, format a field you group by, or an aggregated custom field.
Combine
Section titled “Combine”Tables only. It joins several fields into one text column, per row: “category - product”, “customer (salesperson)”.
- Add at least two Fields to combine, in the order they should appear. Any field type works.
- Separator goes between values. Leave it blank to join them with nothing in between. Empty values are skipped, so you never get two separators in a row.
In a table with Group By, group by every field you combine.
Use it when you’d rather type the expression, or when it’s something the other modes can’t build: a ROUND(…) around a ratio, a NULLIF guard, a cast. It is also where a custom field opens when the form can’t show it exactly (see Editing a custom field).
-
Click SQL at the end of the mode buttons.
-
Type a PostgreSQL expression over the dataset’s columns in SQL Expression. It is stored exactly as you write it.
-
To insert a column at the cursor, click it under Fields. On a table with date filters, the chips under Date Filter Range insert a filter’s picked dates in the same way.
-
Click Apply.
If you built something in another mode first, switching to SQL shows it as SQL, ready to edit.
Open in visual builder moves the SQL into the form when the form can show it exactly as written. When it can’t, the expression stays on the SQL tab and the wizard says so:
Switching tabs never throws away SQL you typed. If you click another mode and the form can’t show your SQL, you stay on the SQL tab with the notice The visual builder can’t show this expression without changing it. Clear the SQL to start from a blank form. To start that mode from scratch, empty SQL Expression first.
Editing a custom field
Section titled “Editing a custom field”The wizard stores only a custom field’s SQL. When you open one again with Edit Custom Field, it reads that SQL back:
- If the form can show it exactly, the field opens in its mode (Arithmetic, Condition and so on) with every piece filled in.
- If not, it opens on the SQL tab with the stored text unchanged. An Arithmetic expression whose every term would be a SQL term also opens there, since the form would add nothing.
Clicking Apply without changing anything keeps the expression exactly as it was, so Save & Preview has nothing to save. This is what makes the columns and tiles of template dashboards safe to open and edit. For a worked example, see Edit a template’s column.
Checks on SQL
Section titled “Checks on SQL”Wherever the wizard takes SQL (the SQL tab, a SQL Operand, a SQL side of a condition, a SQL result), the text must have balanced parentheses and quotes, and it can’t contain --, ;, /* or */. Once you’ve changed something, the problems are listed above Apply, which stays disabled until they’re fixed:
| Where | Message |
|---|---|
| SQL tab, empty | Enter a SQL expression. |
| SQL tab | Expression has unbalanced parentheses or quotes. / Expression can't contain -- ; /* or */. |
| An edited SQL term in Arithmetic | SQL operand “…” has unbalanced parentheses or quotes. (or can’t contain…) |
| A condition’s SQL left-hand side | Condition 1.1: the expression has unbalanced parentheses or quotes. |
| A condition’s SQL value | Condition 1.1: the SQL value has unbalanced parentheses or quotes. |
| is one of with no values | Condition 1.1: enter at least one value, separated by commas. |
| A SQL Then | Condition 1: the result SQL has unbalanced parentheses or quotes. |
| A SQL Otherwise | The “Otherwise” result SQL has unbalanced parentheses or quotes. |
| Stacking Group By with an aggregation | Stacking needs one value per row, so it can’t use SUM, AVG, MIN, MAX or COUNT. Remove the Wrap Expression or the aggregation on a result. |
The numbers say which branch and which condition in it: Condition 2.1 is the first condition of the second branch. In the SQL Operand box itself, Add SQL stays disabled until the text passes the same checks.
Good to know
Section titled “Good to know”- A custom field is an SQL expression stored on the widget. If you rename a dataset column it uses, edit the custom field too; the SQL tab is the quickest place to do it.
- Custom fields don’t make the dataset slower for other widgets; they only run in their own widget.
- To reuse a calculation in many widgets, put it in the dataset, or convert the table to a dataset.












