topic Re: Calculation Property Doesn't Work if a Property is Blank in CMS Development

Calculation Property Doesn't Work if a Property is Blank

GiantVoices — Tue, 05 Feb 2019 22:56:16 GMT

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,

Re: Calculation Property Doesn't Work if a Property is Blank

Stephanie-OG — Wed, 06 Feb 2019 13:19:52 GMT

Hi KP,

I've ran into similar issues (mostly with IE not handling calc well in some cases) and what I did was add a first "default" declaration in the CSS with the calc afterwards. If the calc isn't working it should apply the previous CSS declaration. Like:

.your-selector {
  height: 100px;
  height: calc( {{ value }} * 3 );
}

If the calc height value isn't working it should default to 100px above.

In my case it's getting the opacity from the colour picker:

    .sog_team-member .sog_team-member-content {
      background: linear-gradient(
        rgba({{ module.default_image_overlay_colour_1.color|convert_rgb }}, 0.6), 
        rgba({{ module.default_image_overlay_colour_2.color|convert_rgb }}, 0.6)  
      ); /* IE has trouble with nested calculations - defaulting to 0.6 */
      background: linear-gradient(
        rgba({{ module.default_image_overlay_colour_1.color|convert_rgb }}, calc({{ module.default_image_overlay_colour_1.opacity }} / 100)), 
        rgba({{ module.default_image_overlay_colour_2.color|convert_rgb }}, calc({{ module.default_image_overlay_colour_2.opacity }} / 100))  
      );
    }

In the first "background" above it uses an rgba value with an opacity of 0.6 and in the second it calculates that opacity based on what the user selected in the colour picker.

I hope that helps!

Stephanie O'Gay Garcia

HubSpot Design / Development

Website | Contact

If this helped, please mark it as the solution to your question, thanks!

Re: Calculation Property Doesn't Work if a Property is Blank

GiantVoices — Wed, 06 Feb 2019 14:33:18 GMT

Hello Stephanie,

Although this a great solution, I don't believe it solves the issue with the "Calculation" property and the inability to complete the equation if there is a blank property. Below is one of the more basic equations we're looking to compute across all companies.

Please let me know your thoughts.

Thank you,

Re: Calculation Property Doesn't Work if a Property is Blank

Stephanie-OG — Wed, 06 Feb 2019 14:54:50 GMT

Apologies, I spend a lot of time stuck in CMS and was thinking of the CSS calc() function.

To solve the problem with your calculation property, perhaps you can use a Workflow to set a value of 0 (or some other default) if the property is unknown?

I think @MFrankJohnson or @bradmin use the CRM more than myself and might have a better solution.

Stephanie O'Gay Garcia

HubSpot Design / Development

Website | Contact

Re: Calculation Property Doesn't Work if a Property is Blank

GiantVoices — Wed, 06 Feb 2019 14:58:29 GMT

No problem, Stephanie!

The workflow is a great idea. However, the client does not like the idea as some of their "0" values within their properties does not mean the value as 0 so we are unable to implement that plan of attack. Are there any other options like conditional formatting, etc.?

Thank you,

Re: Calculation Property Doesn't Work if a Property is Blank

TheoB — Tue, 08 Oct 2019 09:19:25 GMT

I agree with this entirely - except, I'd add another bigger issue.

The proposed solution of setting the input properties to 0 if blank doesn't work.

It seems that calculated properties treat "0" and "unknown" as the same thing. This is crazy for a tool that is supposed to allow you to add and multiply values - sometimes it's important that you can add "0".

Case in point - we use calculated properties to track costs relating to a project. Our costs are input in various categories. We use a calculated property to add these up.

The issue - We dont always have costs in all the categories, so naturally we input "0".

The consequence of this is that the calculated property "total costs" does not add up!

If there's a reason for this I dont understand it?

Why not give us some control over this?

E.G.

- Calculate if any of the input values are known

- Calculate if all of the input values are known

- Treat unknown numbers as "0" or some other integer?

Etc?

Re: Calculation Property Doesn't Work if a Property is Blank

huentt — Thu, 17 Oct 2019 04:21:36 GMT

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

Re: Calculation Property Doesn't Work if a Property is Blank

SteveES — Fri, 24 Apr 2020 02:51:58 GMT

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

Re: Calculation Property Doesn't Work if a Property is Blank

GiantVoices — Fri, 24 Apr 2020 14:18:16 GMT

Hello Steve,

Unfortunately, I've reached out to many HubSpot Support team members and they were unable to provide an acceptable solution.

It's disappointing. However, my team is continually testing within the platform and will let you know if we can find an acceptable solution.

Re: Calculation Property Doesn't Work if a Property is Blank

MelindaNelson — Sun, 08 Nov 2020 13:54:30 GMT

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.

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.

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

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

Thanks,

Melinda | (She/Her)

^{Customer Support Specialist}

Re: Calculation Property Doesn't Work if a Property is Blank

MFrankJohnson — Fri, 06 Nov 2020 23:46:36 GMT

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. 🙂

Re: Calculation Property Doesn't Work if a Property is Blank

CTeeling — Mon, 26 Apr 2021 16:27:29 GMT

@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?

Re: Calculation Property Doesn't Work if a Property is Blank

sgerrits — Thu, 27 May 2021 18:31:31 GMT

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.

Re: Calculation Property Doesn't Work if a Property is Blank

Deflow — Thu, 01 Jul 2021 05:21:54 GMT

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.

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.

Re: Calculation Property Doesn't Work if a Property is Blank

MHill4 — Thu, 15 Jul 2021 19:04:48 GMT

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!

Re: Calculation Property Doesn't Work if a Property is Blank

andresmorera — Thu, 28 Oct 2021 15:27:40 GMT

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

Re: Calculation Property Doesn't Work if a Property is Blank

MHill4 — Thu, 28 Oct 2021 16:33:56 GMT

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?

Re: Calculation Property Doesn't Work if a Property is Blank

MarijnAIHR — Thu, 25 Nov 2021 13:03:28 GMT

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...

---

Re: Calculation Property Doesn't Work if a Property is Blank

Ryamak — Wed, 15 Dec 2021 20:38:08 GMT

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.

Re: Calculation Property Doesn't Work if a Property is Blank

JoshBarber — Tue, 12 Jul 2022 23:09:13 GMT

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.