**Baserow formula breaking change** Introducing null values and automatic coercion

Announcements
, ,
1

Hey it’s Davide here, the Baserow dev :wave:

In the next release of Baserow we will be adding automatic null coercion/replacement for arguments passed to Baserow formula functions.
We are asking for feedback on this change.
Please let us know if it will cause you problems or if you are all for it.

Where do null values come from currently

In the following situations it is possible to introduce nulls into a Baserow formula:

  • A date field with no date selected
  • A single select field with no single select selected
  • A text/long text/email/telephone/url field which has been explicitly set to null by a manual API call.
  • Some formula functions like error_to_null, todate with an invalid date string as argument, date_interval with an invalid interval as argument

Examples of the proposed change

Before: null + 'a' = null
After: null + 'a' = 'a'
Before: null + date = null
After: null + date = date

Some Motivation

We want the Baserow formula language to be easily used and understood by non-technical users. Right now there is confusion introduced by null and how it interacts with non null values.
We hope that by adding automatic null coercion we can make the formulas behave in a more intuitive manner when dealing with null values and also reduce the boilerplate you need to write when working with nulls.

Workarounds for maintaining old behavior

If you really want to continue with the old behavior then please:
Let us know why it is useful for you by replying below :pray:

You can use the isnull function to detect null values before they are passed into functions. You can then choose what you would like the output to be using the if function.

Caveats
Not all functions will automatically replace nulls with blank values, the following do not:

  • when_blank (will soon be renamed to when_null for consistency)
  • isnull
  • equal,greatest,least
  • if

For some types of data there is no sensible blank default to swap nulls with, so we do not. These are:

  • Dates
  • Single selects

So you will continue to see the following behavior:
null date + date_interval('1 day') = null date
get_single_select_value(null single select) = null text

New isnull function

We’ll also introduce a new isnull function.
We have introduced the new isnull function which lets you detect null values explicitly.
The main difference with isblank is that isblank returns true also for empty strings, a number 0 or a date_interval(‘0 hours’), while isnull returns true only if the function returns null or for empty cells.

3 Likes