Calculated fields return null values

[Huntscape-Ops]

I am trying to use a calculated field, but if one of the values is empty in the calculation, then it returns a null value.

([properties.imported_total] + [properties.total_donations])

There has to be an easy way to fix this but chatgpt is telling me to use ops hub (which is fine) and use this formula in the format data step of the workflow: {{ properties.imported_total | default(0) }} + {{ properties.total_donations | default(0) }}

But that causes an error in the custom code step.  Please help

[karstenkoehler · Accepted solution]

@Huntscape-Ops there's no special trick to this - just find the screenshot I shared in my earlier reply and type that exactly into the formula field, character by character 🙂

[karstenkoehler]

Hi @Huntscape-Ops,

You can solve this with an 'if' statement and the 'is known' function, both available through drop downs in the formula editor - no Operations Hub required. If the number is known, return the number, if not, return a 0. Repeat the same for the second part of the calculation and that should do the trick.

Best regards

[Huntscape-Ops]

Thank you for the quick reply but I am not advanced enough to do that. I
keep getting errors in the calculation

[karstenkoehler]

@Huntscape-Ops can you please share a screenshot? If you type the shown formula exactly, you shouldn't get any errors.

[Huntscape-Ops]

This is my original calculation but it puts out null value when one of the fields is empty.  

I have no idea how to adapt this to "if" and "is known" as you were describing. 

Any help would be greatly appreciated

[karstenkoehler · Accepted solution]

@Huntscape-Ops there's no special trick to this - just find the screenshot I shared in my earlier reply and type that exactly into the formula field, character by character 🙂

[Huntscape-Ops]

Wow that worked. I had 1 space off.

Thank you so much

[Huntscape-Ops]

I lied.  The formula no longer has errors but I am still getting null values in the calculated field

[karstenkoehler]

@Huntscape-Ops can you please share a screenshot and the error message? I can't help if I don't have any details.

[Huntscape-Ops]

Does this help

[karstenkoehler · Accepted solution]

@Huntscape-Ops strange - can you save this and see if it populates correctly on records? It should work, see my screenshot. If it doesn't after refreshing, I'd recommend checking with HubSpot support.

[Huntscape-Ops]

I refreshed the screen and put the formula back in, and it is working now.

Thank you so much