Hubspot created calculation fields have incorrect syntax

[Zayne]

Some background. I'm trying to create another calculated field on a deal that shows the deal amount in a 3rd currency. The use case is that our South African sales teams sometimes sell a deal in CAD, but we still need this to show against a targeted ZAR amount. Our company currency is USD, so the deal already shows in CAD and USD.

Via the API, I've looked at the properties of the field "amount_in_home_currency" to see if I can use that calculation as a starting point. It is this:

"if is_present(string(deal_currency_code)) then if bool(hs_is_closed) then (amount * dated_exchange_rate(string(deal_currency_code), if is_present(string(closedate)) then string(closedate) else timestamp(hs_is_closed))) else (amount * exchange_rate(string(deal_currency_code))) else amount"

However, when I add that as the calculation for a new field via the API, I get an error:

"Unable to parse calculation formula in line 1 and column 133 - line 1, column 133:\nnot, -, round_down, round_up, round_nearest, is_present, number_to_string, string_to_number, concatenate, max, min, contains, date, time_between, begins_with, to_lower_case, to_upper_case, has_email_reply, has_plain_text_email_reply, extract_most_recent_email_reply_html, extract_most_recent_email_reply_text, extract_most_recent_plain_text_reply, pipeline_probability, month, year, period_to_months, dated_exchange_rate, currency_decimal_places, exchange_rate, set_contains_string, is_engagement_type, fetch_single_currency_portal_currency, format_full_name, (, NOW, DECIMAL, scientific number literal, string literal, true, false, IDENTIFIER, bool, string or timestamp expected, if encountered."

I've managed to get my formula working by moving the nested 'ifs' up a level, but the issue for me is why the formula works for a HubSpot field, but doesn't work for me? Does the API have more stringent validation? 

[Jaycee_Lewis]

Hey, @Zayne 👋 Thank you very much for taking the time to share your example. Furthermore, thanks for letting us know how you found a workaround.

It's possible that there could be differences in the syntax validation for internally defined HubSpot fields and user-defined custom fields. The internally defined fields might have access to more complex functions or syntax that are not exposed to users when they create custom fields.

In other words, some fields used internally by HubSpot might leverage additional functions or use different syntax rules that are not publicly documented or supported for custom field creation.

To your point, this doesn't seem to be addressed in the documentation. Not that I could find. 

I am curious about the distinction you uncovered and will take it to my internal resources. This is not a fast fix, but hopefully will yield us come clarity into this matter. Are there any other details you would like to share? I'm happy to bundle up your example into my request and handle it from here. 

Best,

Jaycee

[Zayne]

Hi Jaycee. Here is the final calculation that I came up with that works and is (I hope) functionally the same, but calculates a ZAR value:

"calculationFormula": "if (not (string(deal_currency_code) equals 'ZAR') and is_present(string(deal_currency_code)) and bool(hs_is_closed)) then round_nearest((amount / dated_exchange_rate(string(deal_currency_code), timestamp(hs_is_closed)) / dated_exchange_rate('ZAR', timestamp(hs_is_closed))), 2)\nelseif (not (string(deal_currency_code) equals 'ZAR') and is_present(string(deal_currency_code)) and not (bool(hs_is_closed))) then round_nearest((amount / dated_exchange_rate(string(deal_currency_code), string(closedate)) / dated_exchange_rate('ZAR', string(closedate))), 2) else amount"

There seems to be a discrepancy with the way that dated_exchange_rate() validates. In the HubSpot example, there is an IF statement in the second argument. In my version, I had to remove that to get it to validate.

In one of my Support tickets, there is a similar issue with the second argument of a function in a workflow. An extract from the support engineer:|

"The team informed me that the second parameter of the plus_time action (diff/amount to add) only accepts a static number. It's not built to accept a property. Thus, it cannot recognize the property type and isn't able to pull out the actual value of "Contract Duration"."

I don't know if this is related, but it points to inconsistencies in the features that are offered to us as customers and makes it difficult for us to easily build the solutions we want to.

I am however enjoying my experience with HubSpot immensely and often marvel at what we're able to achieve on the platform!