Calculated field

[CClark3]

I'm having problems trying to think of of this calculated field. I have 3 seperate fields all yes and no dropdowns. Has this been sent to billings, Has resources been allocated, Is budget available. I created a custom calculated field that I need to return back true when these 3 fields on the deal are all yes. When i try to make the calculated field its saying a have an error. Im using the If statement from below. Could anyone help with this anmd getting into a calcualted field? I know I could use a workflow but woul prefer to have the calculation in the field. 

if([properties.resources_officially_allocated_to_project_]= "Yes",[properties.billings_added_on_project_]= "Yes", [properties.budget_approved_on_project_]= "Yes",  True, False)

[Shadab_Khan · Accepted solution]

Hi @CClark3 

The problem seems to be that you’re passing string values (like “Yes”) into the calculation, which isn’t how HubSpot handles Boolean fields (like checkboxes or yes/no fields). Here's a better approach:

• The Issue:
  • You're using an equal sign ( = ) to compare the string values "Yes" or "No" in your If statement, which doesn't work with Boolean fields.
• The Solution:
  • Instead of comparing strings, simply pass the property names directly in an AND condition within your If statement.
  • This will evaluate the properties as Booleans, returning True when all three fields (billings, resources, and budget) are "Yes," and False if any are "No."
• Here’s the corrected approach:

  if([properties.resources_officially_allocated_to_project_] and 
     [properties.billings_added_on_project_] and 
     [properties.budget_approved_on_project_], True, False)

  • The AND condition ensures that all fields must be "Yes" (or true) for the custom field to return True. If any field is "No" (false), it will return False.
• Video Walkthrough:
  I’ve attached a Loom video to help you visualize the solution, where I walk through a demo instance and show how to set up this calculated field correctly.

Let me know if this helps, and feel free to reach out if you need further clarification!

Stay Awesome & Keep Doing it BIG 🔥 

[karstenkoehler · Accepted solution]

Hi @CClark3,

Could you confirm whether your properties are dropdowns with options "Yes" and "No", or single checkboxes?

If they're dropdowns, the formula (with output type boolean) would have to look like a bit more complicated like this, to check whether a property has a value first, if not, return false, followed by nested if-statements:

if(is_known([properties.billings_added_on_project_]) and is_known([properties.budget_approved_on_project_]) and is_known([properties.resources_officially_allocated_to_project_]),if([properties.billings_added_on_project_]="Yes",if([properties.budget_approved_on_project_]="Yes",if([properties.resources_officially_allocated_to_project_]="Yes",true,false),false),false),false)

If they're single checkboxes, the formula (with output type boolean) could be simplified to this:

if([properties.resources_officially_allocated_to_project_] and [properties.billings_added_on_project_] and [properties.budget_approved_on_project_], true, false)

Please note: HubSpot expects "true" and "false" to be lowercase in a boolean custom equation

Please share a screenshot of your formula and the issues if this still doesn't work.

Best regards

[CClark3]

Hello,

Thank you so much for your help. @karstenkoehler and @Shadab_Khan  I do have an additional question, is it possible to add an Or function for this? For example those 3 fields still have to be Yes in order for the calculated field to be marked true but we are adding a 4th field called Manually forecasted that if this is marked yes the calculated field should also be changed to true. is it possible to add this as well? 

[karstenkoehler]

@CClark3 sure, it depends, which formula did you end up using?

[CClark3]

Yes I ended up using the IF function I copied it below:

if((([properties.resources_officially_allocated_to_project_] && [properties.billings_added_to_project_]) && [properties.budget_added_to_project_]), true, false)

[karstenkoehler]

@CClark3 you would nest two if functions then.

1. The first if function checks whether your manual forecast property is checked, if yes, return true, if no (as the second argument) move on to the nested second if function
2. The second if function would be what you have already

[CClark3 · Accepted solution]

Tahnk you, I believe I did this correctly but I'm concerned that when I tested the formula if the Manual forecast is blank (which it will be if the others are filled out) that the formula does not produce an output, is this correct? i attached a screenshot 

[karstenkoehler]

@CClark3 please review my previous answer again, the if functions shouldn't be combined by an OR operator, the second IF function is within the first, as it's second argument.

[karstenkoehler]

@CClark3 here's the entire formula:

This would:

• return true when manual forecast is true
• return true when all of the three other properties are true
• return false in all other cases (including empty fields)

Please test and mark as accepted solution 🙂

[CClark3]

Thank you I removed this and still received the same issue if the manual field was left blank. Its not big of an issue because i can create a workflow that has the manual forecast field selected to No whenever a deal is created since this field would be changed manually.

[karstenkoehler · Accepted solution]

Hi @CClark3,

Could you confirm whether your properties are dropdowns with options "Yes" and "No", or single checkboxes?

If they're dropdowns, the formula (with output type boolean) would have to look like a bit more complicated like this, to check whether a property has a value first, if not, return false, followed by nested if-statements:

if(is_known([properties.billings_added_on_project_]) and is_known([properties.budget_approved_on_project_]) and is_known([properties.resources_officially_allocated_to_project_]),if([properties.billings_added_on_project_]="Yes",if([properties.budget_approved_on_project_]="Yes",if([properties.resources_officially_allocated_to_project_]="Yes",true,false),false),false),false)

If they're single checkboxes, the formula (with output type boolean) could be simplified to this:

if([properties.resources_officially_allocated_to_project_] and [properties.billings_added_on_project_] and [properties.budget_approved_on_project_], true, false)

Please note: HubSpot expects "true" and "false" to be lowercase in a boolean custom equation

Please share a screenshot of your formula and the issues if this still doesn't work.

Best regards

[Shadab_Khan · Accepted solution]

Hi @CClark3 

The problem seems to be that you’re passing string values (like “Yes”) into the calculation, which isn’t how HubSpot handles Boolean fields (like checkboxes or yes/no fields). Here's a better approach:

• The Issue:
  • You're using an equal sign ( = ) to compare the string values "Yes" or "No" in your If statement, which doesn't work with Boolean fields.
• The Solution:
  • Instead of comparing strings, simply pass the property names directly in an AND condition within your If statement.
  • This will evaluate the properties as Booleans, returning True when all three fields (billings, resources, and budget) are "Yes," and False if any are "No."
• Here’s the corrected approach:

  if([properties.resources_officially_allocated_to_project_] and 
     [properties.billings_added_on_project_] and 
     [properties.budget_approved_on_project_], True, False)

  • The AND condition ensures that all fields must be "Yes" (or true) for the custom field to return True. If any field is "No" (false), it will return False.
• Video Walkthrough:
  I’ve attached a Loom video to help you visualize the solution, where I walk through a demo instance and show how to set up this calculated field correctly.

Let me know if this helps, and feel free to reach out if you need further clarification!

Stay Awesome & Keep Doing it BIG 🔥