Let "Number" and "Money"-type custom fields be nullable
Overview
There are many times when NULL would be a meaningful/useful value in a custom field of type "Number" (float) or "Money" (decimal). However, it is not currently possible, via the form layer, to set these fields to NULL. Blank values are currently turned into zero.
Example use-case
Let's say we need to track the elevation (meters above or below sea level) of the addresses in our database.
- Create a custom field "Geographic Elevation" of type "Number", extending the Address entity. Leave the "Default value" blank.
- Go to a contact and edit one of their addresses, or create a new one.
- On the create/edit form, leave the "Geographic Elevation" field blank -- because let's say we don't know the elevation at this address.
- Submit the form and view the saved value.
Current behaviour
"Geographic Elevation" is set to zero. That means sea level. But that's not correct -- we actually don't know the elevation.
Proposed behaviour
When the field submitted with a blank value (empty string), the database field should be set to NULL, and displayed as blank.
Comments
NULL is also a meaningful value for Money fields. Say we have a field on individuals called "Net Worth". Zero (the person is destitute) and NULL (we don't know how much money they have) are quite different.
It is possible to set the field to NULL through the API, but only by passing the string 'null' (due to a PEAR DB limitation).
Show API example
// NULL will be saved as 0
Civi\Api4\Address::update()
->addValue('Geography.Elevation', NULL)
->addWhere('id', '=', 27)
->execute();
$address = Civi\Api4\Address::get()
->addSelect('Geography.Elevation')
->addWhere('id', '=', 27)
->execute()->single();
// [
// 'id' => 27,
// 'Geography.Elevation' => 0,
// ]
// but 'null' will be saved as NULL
Civi\Api4\Address::update()
->addValue('Geography.Elevation', 'null')
->addWhere('id', '=', 27)
->execute();
$address = Civi\Api4\Address::get()
->addSelect('Geography.Elevation')
->addWhere('id', '=', 27)
->execute()->single();
// [
// 'id' => 27,
// 'Geography.Elevation' => null,
// ]