CMS Development

GiantVoices
Participante | Parceiro
Participante | Parceiro

Calculation Property Doesn't Work if a Property is Blank

resolver

Hello, 

 

Currently, we have the enterprise level HubSpot account and have been working on creating calculation properties to add together transactional pieces. There is a major roadblock with this property as it doesn't allow a calculation formula to run if there is a blank property. Is there any sort of workaround for this problem? 

 

Thanks, 

 

 

KP

2 Solução aceitas
MelindaNelson
Solução
HubSpot Employee
HubSpot Employee

Calculation Property Doesn't Work if a Property is Blank

resolver

Hi all,

 

I tested this with a customer today. Setting the properties included in the calculation property to 0 did work.

 

To configure this, you can:

  • Create the calculated property using two number properties.

Calculation Property.png

  • On the contact record, add 0 for any values that are unknown.

When the value for Number property 2 is blank, the Calculated number property is unknown.

Calculated Property2.png

When the value for Number property 2 is 0, the Calculated number property is calculated.

Calculated Property3.png

 

I hope this helps! Please let me know if you have any questions! 

 

Thanks,

Melinda | (She/Her)

Customer Support Specialist

Exibir solução no post original

JoshBarber
Solução
Colaborador(a)

Calculation Property Doesn't Work if a Property is Blank

resolver

In case anyone stumbles upon this like I did - I also couldn't get this to work and instead used workflows to set the default property to 0.

Exibir solução no post original

26 Respostas 26
Clare_HS
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

I couldn't use `is_present` for some reason. I wound up setting the unknown values to 0 within the formaul like:

 

if(is_known([properties.abc]),[properties.abc], 0) + if(is_known([properties.xyz]),[properties.xyz], 0)

 

So we're saying that if the NUMBER property has a value, use that vlaue in the calculation. If there's no value, call it 0 and calculate accordingly. 

That worked like a charm for me after a little trial and error 😀

markmhaddad
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

Yup, HubSpot changed the is_present function to is_known (presumably to align with the terminology they use in filters)!

0 Avaliação positiva
hub_muc
Participante | Parceiro Platinum
Participante | Parceiro Platinum

Calculation Property Doesn't Work if a Property is Blank

resolver

Alternative solution without using a workflow: Use Advanced Mode in the Calculation Property and combine if and is_present function before adding the properties:

(if(is_present([properties.xy]), [properties.xy], 0) +

JoshBarber
Colaborador(a)

Calculation Property Doesn't Work if a Property is Blank

resolver

This works a treat for calculations that require you to calculate with other calculations/rollups where you can't set the default value to 0, and should be marked as the solution. No longer need workflows.

markmhaddad
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

Under-rated comment here - this is the best solution!

0 Avaliação positiva
FZangerl
Membro

Calculation Property Doesn't Work if a Property is Blank

resolver

At first: big shame on Hubspot that the paying users have to find workarounds for problems like this - problems, that appeared already years ago. 

 

At second: in my opinion the easiest solution is to use a workflow and the funktion

 

"Increase or decrease property" and increase the value by 0, because unknown + 0 = 0

 

Best, 

 

Florian

 

MarijnAIHR
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

Hi @MHill4 
Thanks for this workaround. Could you maybe share a screenshot of your solution? I cannot get it to work myself - if I multiply an unknown value times 1, I still get unknown, not 0...

MarijnAIHR_0-1637845371723.png

---

MarijnAIHR_1-1637845395174.png

 

 

JoshBarber
Solução
Colaborador(a)

Calculation Property Doesn't Work if a Property is Blank

resolver

In case anyone stumbles upon this like I did - I also couldn't get this to work and instead used workflows to set the default property to 0.

MHill4
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

Sorry, by Constant 1, I just meant the number 1. 

In the calculation property A for example, your formula would be: (Property A x 1)

 

That way unknowns for Property A stay unknown, but you can still include those in calculations as 0.

 

So for @GiantVoices example above. You would make a new calculation property for each of the properties in the proposed formula. For example "2015 Q1 Payment Amount Calc," which would equal (2015 Q1 Payment Amount x 1).

 

For records with unknown 2015 Q1 Payment Amount, 2015 Q1 Payment Amount Calc = 0

 

For records with a number entered in 2015 Q1 Payment Amount, 2015 Q1Payment Amount = the same number.

 

does that help?

0 Avaliação positiva
MHill4
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

Hi All,

 

I believe I may have found a solution to this issue. A bit of effort upfront, but it definitely works!

 

Lets say: Property A + Property B + Property C = Calculation Property X

For each of Property A, B and C, just create a new calculation property, maybe labeled "Property A Calc", where you do (Property A x Constant 1) = Property A Calc. If Property A is blank, the Property A Calc property will be 0. If Property A is known, Property A Calc = Property A.

 

So then to get Calculation Property X, you would just do Property A Calc + Property B Calc + Property C Calc. Then it runs perfectly! AND you don't need to do any workflows and you maintain the blank status of the original field which is important!

 

andresmorera
Participante | Parceiro Ouro
Participante | Parceiro Ouro

Calculation Property Doesn't Work if a Property is Blank

resolver

Thank you MHill4,

 

What should be the value for Constant 1 in the formula or should I create a new porperty named Constant 1?
Thanks for clarifying.

Andres

0 Avaliação positiva
sgerrits
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

I've built workflows that set the property to 0 if unkown but then the problem is if you have a calculation property that references another calculation, there's no way of getting the second calculation to work if all of the criteria are not met.  Pretty frustrating.  

CTeeling
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

@MFrankJohnson 

Hi, Frank, 

 

I'm running into this exact issue now - Is there a best practice to set multiple default values to zero in a single workflow, or did you have to create multiple workflows to set multiple default values?

0 Avaliação positiva
Deflow
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

The simplest way I have found to do this is as follows:

 

  • First we want to check the value to see if there is anything entered in there. Hence "less than or equal to $0 or EMPTY".
  • If value is empty, then set the property to be 0.
  • If value is not empty or is greater than $0 then "go to action"

 

  • This second action is repeating the same steps as above on the next value that needs to be checked.

Deflow_0-1625116689033.png

 

I do like the method above using calculated values but if you are not on an enterprise account then you need to use your 5 calulated fields sparingly.

 

0 Avaliação positiva
MelindaNelson
Solução
HubSpot Employee
HubSpot Employee

Calculation Property Doesn't Work if a Property is Blank

resolver

Hi all,

 

I tested this with a customer today. Setting the properties included in the calculation property to 0 did work.

 

To configure this, you can:

  • Create the calculated property using two number properties.

Calculation Property.png

  • On the contact record, add 0 for any values that are unknown.

When the value for Number property 2 is blank, the Calculated number property is unknown.

Calculated Property2.png

When the value for Number property 2 is 0, the Calculated number property is calculated.

Calculated Property3.png

 

I hope this helps! Please let me know if you have any questions! 

 

Thanks,

Melinda | (She/Her)

Customer Support Specialist

CleberAlexandre
Membro

Calculation Property Doesn't Work if a Property is Blank

resolver

Where can I configure this?

  • On the contact record, add 0 for any values that are unknown.
0 Avaliação positiva
Ryamak
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

I'm not sure how this solution would help if the calculation property is calculating "Time Between". How do you put in a 0 value for a date? And even if there's a way, what about in an example where you're trying to calculate how long a deal has been in each stage for? If the deal is still in that stage you don't want a date set for it yet, but it's still a running count which isn't even being counted.

0 Avaliação positiva
MFrankJohnson
Autoridade no assunto

Calculation Property Doesn't Work if a Property is Blank

resolver

Just want to add a workflow to make sure that all blank properties used in calcuated property formulas are auto-updated to zero as new contacts/objects are added to the CRM. 🙂

 

Note: Please search for recent posts as HubSpot evolves to be the #1 CRM platform of choice world-wide.

 

Hope that helps.

 

Be well,
Frank


www.mfrankjohnson.com
0 Avaliação positiva
huentt
Membro

Calculation Property Doesn't Work if a Property is Blank

resolver

I also encountered the same case
I think Hubspot should add one condition when setting up fields with the following numeric format: if the field has no value, there are two options for users to choose to be blank or zero.
so the calculated fields will work

SteveES
Participante

Calculation Property Doesn't Work if a Property is Blank

resolver

Any updates on this? I just ran into the same challenge and I'm hoping someone has a work around.  Thanks.