Frequently asked questions - Base

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

How do I handle empty (null) fields?

Calculations on fields

In LibreOffice nulls are always propogated, except when a calculation is performed on a null field. The result of such an expression is always Null.

For example, in operation like:

"Total" - "Account"

will give the result Null' if one of the fields hasn't been entered. The solution is to use a function that tests whether fields contain data or not. Amongst the functions available IFNULL' is often used. Its syntax is

IFNULL(exp, value)

If exp is Null', value is returned, otherwise the value of exp is returned.

For this example, IFNULL is used to return zero when the field is empty.

The SQL command is

SELECT "ID", "Acompte", "Total", "Total" - "Acompte", IFNULL( "Total", 0 ) - IFNULL( "Acompte", 0 ) FROM "Table1"

Selecting rows with null values

Selecting rows containing empty fields.

The selection criterion to use in draft mode is :

IS EMPTY

The selection criterion in SQL mode is

IS NULL

For the example above

Notes

In draft mode it is also possible to enter the criterion IS NULL. After validation it will automatically be replaced by IS EMPTY
In SQL mode only the syntax IS NULL is valid

SELECT "ID", "Acompte", "Total" FROM "Table1" WHERE "Acompte" IS NULL

Managing boolean fields

The possible states of boolean fields

The selection of rows having unfilled boolean fields uses the same criterion, IS EMPTY.
It should be noted that a boolean field can have three states: checked, unchecked and empty.

The two latter states are not equivalent: unchecked = FALSE (false or 0), and empty = indeterminate/unspecified.

LibreOffice shows these different states as follows: unchecked = blank, unfilled = background colour
- IS EMPTY selects unfilled cases (not entered)
- FALSE select unchecked (value is false or 0)
- TRUE selects checked (value is true or 1)