Substitute Nulls with Zeros for Bionic Rule Formula to Bring Results

  • 8
  • Problem
  • Updated 1 year ago
  • Solved
It is found, when using bionic rule formula to do for example, Field 1 + Field 2 + Field 3. If one of those values is null, total would come out to null as well. 

Putting in another example, a 1 + 2 + " " , result will be null. Please add an accommodation for bionic rules to handle nulls.

You can use pivot as a workaround, but Pivot only allows it on one field at a time per task.
Photo of Kevin Ly

Kevin Ly, Employee

  • 3,268 Points 3k badge 2x thumb

Posted 1 year ago

  • 8
Photo of Spencer Engel

Spencer Engel, Employee

  • 5,638 Points 5k badge 2x thumb
Big time +1
Photo of Srinivas Muthyam

Srinivas Muthyam, Employee

  • 1,294 Points 1k badge 2x thumb
Hi Kevin,

Current formula builder component will enforce the user to use the same data type fields to be added. For example in an expression we will allow only numeric field/value or resultant numeric function. This will ensure that numbers and string values can not be clubbed together.

However per my knowledge, at the time of designing the rule, we may not be able to predict whether the field value in record holds null or zero value. This is because the rule will not be executed once. 

We will think about the possibilities of gracefully handling this situations and update on this. One quick thought which might help is: Have a setting that will ensure null/empty values adjusted "Adjust/Omit empty values".
Photo of Jitin Mehndiratta

Jitin Mehndiratta, Product Manager

  • 3,252 Points 3k badge 2x thumb
Hi Kevin,

I agree that current way of handling nulls in expressions and functions might be a blocker in getting the desired output. 
While we are working on some new formula functions for fall release, we will try to accommodate NullToZero() function or introduce a flag "Treat null values as zero" for functions and expressions which takes numeric values as source. 

I will keep you posted on this.

Photo of Spencer Engel

Spencer Engel, Employee

  • 5,638 Points 5k badge 2x thumb
Hi Jitin - this would be a great solution for the specific problem I'm encountering with my customer. Thanks for considering.