Custom calculation property field IF logic not working?

Smart CRM & Sales Hub CRM
1

Hi - I’m trying to build a calculated field that follows this logic like this:

if([properties.dealstage] == “1234567”, if([custom_property] == “Option 1 Condition”,“Option 1 Result”,if([custom_property] == “Option 2 Condition”,“Option 2 Result”,“No Option Result”)), "Wrong Dealstage Result)

Basically, if the deal stage is 1234567, then it moves into a sub-series of IF statements that check the value of another datafield “custom_property”. I’m trying to get it to return “No Option Result” if the “Custom_Property” field has no selected value (i.e. is unknown).

It seems like if the value is unknown, it doesn’t treat that as a FALSE result to return “No Option Result”, although that is what should happen when none of the other options are matched. What is the best way to check that a field is “Unknown” in a calculated field?

2

In HubSpot, when you want to account for an unknown or no value situation within a calculated field, especially in nested IF statements like yours, you might need to explicitly check for those conditions. Unfortunately, HubSpot’s calculated fields don’t directly offer an “IS UNKNOWN” function, but we can work around this by using logical comparisons.

Your original formula is structured well, but to handle an unknown “custom_property” value, consider treating it as an empty string or explicitly checking for nullity, if the syntax and logic of your HubSpot environment allow. Since direct null checks might not always be straightforward, here’s a slightly adjusted approach that assumes an empty or null “custom_property” would not equal any of your conditions:

if([properties.dealstage] == “1234567”, if([custom_property] == “Option 1 Condition”, “Option 1 Result”, if([custom_property] == “Option 2 Condition”, “Option 2 Result”, if([custom_property] == “” OR [custom_property] IS NULL, “No Option Result”, “Unknown Condition”))), “Wrong Dealstage Result”)

In this adjusted formula, I’ve added an additional check to explicitly handle the case where “[custom_property]” might be empty (“”) or NULL. Please note, the OR and IS NULL are conceptual here, as the exact syntax might vary based on HubSpot’s current capabilities and your specific context. HubSpot’s formula syntax does not directly support an IS NULL check, so you might need to adapt this part. The key idea is to ensure you have a condition that captures the scenario where “custom_property” does not have a value.

If HubSpot’s calculated fields do not support checking for nullity in the way you need, another approach might be to ensure that “custom_property” always has a default value that you can check against, though this might not always be practical depending on your use case.

Remember, testing is your best friend here. Due to the specific syntax and behavior of calculated fields in HubSpot, you might need to adjust the formula slightly to fit the platform’s current functionalities.

---

Got help from https://www.spheroflow.com/ for this question. You just ask and it answers!!!

3

Thanks and I agree with your logic, but I think there is a lack of an “IS NULL” check as an if conditional - that’s what I was trying to determine of to bring up. Ideally, there would be an “IS_UNKNOWN” function that would return TRUE when the field is NULL - this would mirror the user interface advanced filtering functionality where there is an option to filter where a specific data field is unknown. In the interim, I found that the best solution was to drop in a “-” value and then added a workflow to set every new deal to that value (since I don’t think there is a way to set a default value on a drop-down list, but would love to know if there is).

4

You are close here! the “is_known” operator is what you’ll need to incorporate here. However, it returns a boolean, meaning that you’ll need to add this throughout your formula to return results as desired. When using this check for null values, use && instead of == to determine if there is a value there FIRST, then move onto your decisions of what to do if a value is there. The function to check if a value is there or not should look like the below:

if(is_known([property])&&true,[value if true], [value if false])

you can use the above in the middle of your formula and add nested if statements to get your formula working as needed

5

Thank you! This solved a different issue I was having!