Skip to content
We're live on the Odoo App StoreView listing ↗

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).

The Product Performance table with Product, Quantity, Sales, Average Price, Share of Category and Sales Band columns
Product Performance: every column after Sales is a custom field.

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.

  1. In the Dashboard Builder, select the widget and open the Configuration tab.

  2. Click the field picker you want to fill, for example a column’s field in Table Columns Configuration.

  3. Scroll to the end of the list and pick Custom….

  4. Build the field (see the modes below). On a table, also type a Field Label: it becomes the column header.

  5. 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.

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.

Use it for any sum, difference, ratio or date calculation: average price, margin, days between two dates.

The Arithmetic mode building SUM(sales) divided by SUM(quantity), with Numeric Fields, Operators and Numeric Value below
  • 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 as NULLIF(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.
A new custom field in Arithmetic mode: SUM(balance) divided by the SQL term NULLIF(COUNT(DISTINCT partner), 0), with the SQL Operand box and its Add SQL button at the bottom
The SQL term (code icon) sits in the expression like any field.
The SQL term NULLIF(COUNT(DISTINCT partner), 0) open for editing inside the expression, in a box as wide as the term
Click a SQL term to edit it where it is.

The wizard refuses expressions that end with an operator, have two operators in a row, or divide by the number 0.

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.

The Remaining Quantity (FIFO) tool with Signed Quantity, Partition By (Group) and Order By (Sequence)
  • 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.

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.

The Percent of Total (Share) tool with Measure Sales and Partition By (Group) category
  1. Pick the Measure, the value whose share you want.

  2. 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.

  3. 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.

Use it to put rows into bands or rename values: “Top seller / Steady / Slow”, “Overdue / Due”, a readable label for a technical status.

The Condition mode: When SUM sales greater than 10000000 then Top seller, Else When SUM sales greater than 6000000 then Steady
  • 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.
A Payment Status column: When reconciled is true then Reconciled; Else When the SQL CURRENT_DATE - due_date is greater than the value 30 then Overdue over 30 days; Otherwise Nothing, with the note Rows matching no condition get an empty value
The second branch compares SQL with a value; Otherwise is set to Nothing.

On a chart’s dimension, Condition is the only mode, so you can group a chart by bands.

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.

The Format mode with Field order_date, Operation Format as text and Pattern presets such as 31/01/2024 and January 2024

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_CHAR pattern.
  • 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.

Tables only. It joins several fields into one text column, per row: “category - product”, “customer (salesperson)”.

The Combine mode with category and product under Fields to combine and a dash as separator
  • 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).

  1. Click SQL at the end of the mode buttons.

  2. Type a PostgreSQL expression over the dataset’s columns in SQL Expression. It is stored exactly as you write it.

  3. 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.

  4. 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:

The SQL tab with the Current Ratio expression and the notice The visual builder can't show this expression without changing it, so it stays as SQL.

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.

The SQL tab after clicking Condition: the expression is kept and the notice says The visual builder can't show this expression without changing it. Clear the SQL to start from a blank form.

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.

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:

The SQL tab with the Current Ratio expression missing its last parenthesis, the hint Expression has unbalanced parentheses or quotes., and Apply disabled
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.

  • 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.