Error creating calculation property with Properties API

[PD_]

I'm trying to create a calculation property using the Properties API as Properties API documentation explains.

I'd use the Hubspot Portal instead of the Properties API to create the property, but the types of formulas allowed in the portal are quite limited.

I've been able to create calculation properties using the Properties API but only using formulas that don't reference any other properties, such as "1+1" or "concatenate('a','b')".  Whenever I have a calculation formula that references another property, I get a generic error:

{
    "status": "error",
    "message": "There was a problem with the request.",
    "correlationId": "179e982a-fdcf-4714-9b27-b52203a34ea6"
}

I even tried to create a calculated propery that was identical to an existing calculated property but just with a different name. I got the same error.

As an example, I used the property create API (i.e. send a POST to https://api.hubapi.com/crm/v3/properties/{object_type} )

with the following request body

{
    "name": "calc_prop_from_api",
    "label": "calc_prop_from_api",
    "type": "number",
    "fieldType": "calculation_equation",
    "description": "Calculated property from app",
    "groupName": "line_item_record_information",
    "options": [],
    "displayOrder": -1,
    "calculated": true,
    "externalOptions": false,
    "archived": false,
    "hasUniqueValue": false,
    "hidden": false,
    "showCurrencySymbol": false,
    "formField": false,
    "calculationFormula": "(quantity * price)"
}

and I get the error again:

{
    "status": "error",
    "message": "There was a problem with the request.",
    "correlationId": "48e01572-de56-439c-863e-d76e460bd44a"
}

The calculationFormula and the rest of the JSON fields were copied directly from another property that I created in the HubSpot portal, just with a different name.

I was only able to successfully create the property when I changed the calculationFormula to something that doesn't reference another property: "(1 * 2)"

The particular property I'm trying to create is on a custom object, but I did the same thing for a property on a deal and got the same error message. I tried it both in my account sandbox and in production. I tried it both with property creation and property updates. All return the same error.

What can I do to create a calculation property with the Properties API that allows me to use other properties in the formula?

[ShamashA]

I'm also trying to create a calculated property using the same endpoint but with the following calculationFormula:

"calculationFormula": "time_between(date__enrolled, date__exited)"

but I get this error message: "numberDisplayHint must be 'duration' for a time-between property"

If you don't mind, can you do a test to see if you're able to create a property using that function @PD_ @GregP 

Or is this a know limitation/bug @Jaycee_Lewis ?

[GregP]

I do get that error when trying to create a time_between via API.

If it is created via the UI first, I do not get the error when updating the property via API, so if something more complex is needed — like including a min, max, or math — updating a UI created time between via API can get you there.

If your just trying to create a whole bunch of properties via API (like moving things from a sandbox to production), this error is very problematic.

Based on the error text, I even gave adding this a try, to no effect.

"numberDisplayHint": "duration"

It seems they have not exposed it to the API, so you currently have to create via the UI to have that get set properly. I would guess this is what is being used behind the scenes for the display of number properties in general (unformatted, formatted with thousands separators, formatted as duration etc.), because that has never been exposed to the API, and we have not been able to set it for calculation_equation properties — it has just defaulted to formatted with thousands separators. [*update edit* There is now a number format selection in the UI when creating a calculation property there, so they are likely actively implementing this way of selecting a number format.]

@ShamashA You should create a new thread for this, if you haven't already.

[GregP · Accepted solution]

Just an update for this thread. I tested this again today and it seems to now be properly working again.

[PD_]

Thanks for pointing this out @GregP . I just created a calculation property from the API without using my workaround. I can confirm that this works as expected now.

I just wish the folks at HubSpot would have confirmed that this is an issue, communicated an ETA on the fix, and informed us when it was fixed. Oh well, I'm glad it's fixed.

[Jaycee_Lewis]

Hey, @GregP Thank you for taking the time to swing back and give an update 🙌 — Jaycee

[ehearty]

@Jaycee_Lewis I saw that this error is due to a known bug - is there an ETA for the fix? Spent about 2 hours pulling my hair out this morning until I stumbled on this thread...

[GregP]

I also have now come across this bug. Succinct explanation:

When setting a calculationFormula via API, making any property reference in the calculationFormula, as specified in the documentation, now causes a "There was a problem with the request" error.

@Jaycee_Lewis have the devs been informed of this bug yet. It is a critical bug for anyone needing to create calculation equations via API, which is needed for anything more than the most basic of basic use of the calculation property feature.

[Jaycee_Lewis]

Hi, @PD_ 👋 Thanks for reaching out. To help with clarity, are the properties you are trying to include also calculation properties? Or are they other number-type properties?

Also, can you include a screenshot of the working version you created in-app? Or what you are referencing here, “I even tried to create a calculated property that was identical to an existing calculated property but just with a different name. I got the same error”?

Best,

Jaycee

[PD_]

The properties I'm referencing in the calculation formula are not calculation properties themselves. They're number properties. In my example, quantity and price are each number properties.

Screenshot of my creating a calculation property in the Hubspot Portal:

For clarification, when I said “I even tried to create a calculated property that was identical to an existing calculated property but just with a different name. I got the same error” what I meant was:
1) I created the calculated property, calc_prop_from_app, in the HubSpot Portal (see screenshot above)

2) I used the "Read a property" endpoint of Properties API (

GET/crm/v3/properties/{objectType}/{propertyName}) to get the property specification as JSON.

3) I used the JSON specification from step 2 as the request body to the "Create a Property" endpoint of the Properties API (POST/crm/v3/properties/{objectType}) ), except I changed the name from "calc_prop_from_app" to "calc_prop_from_api"

4) The attempt to create the property via the API failed with the error

{
    "status": "error",
    "message": "There was a problem with the request.",
    "correlationId": "179e982a-fdcf-4714-9b27-b52203a34ea6"
}

I supposed that if I used the same JSON that I got from the Properties API to create a new property then the new property would be identical to the old one. 

[PD_]

I found a workaround for creating calculated properties that only involve number properties and at most one non-number property. As of this writing, you can't create a calculated property that references other properties by only using the Properties API. This is clearly a bug in the Properties API implementation and HubSpot should fix it.

The workaround involves creating the calculated property in the Hubspot Portal and then updating the property using the Properties API.

The steps of the workaround:

1. In the Hubspot Portal click on the Settings button, the gear icon in the top right
2. Click on Properties in the left sidebar
3. Select the object that you'll create a property on
4. Click the Create Property button on the right
   
5. Fill out the basic information in the form and click Next
   
6. Select Calculation as the Field Type and click Build calculation
   
7. At this point we need to create a dummy calculation that exists solely for the purpose of allowing us to use specific properties in the custom calculation formula that we'll set later. Any properties you want to use in your custom calculation must be included in this dummy formula. For example, based on the screenshot below, we'll be able to use Quote Lines, Annual Contract Value, and Priority in our custom calculation formula, but no other properties. Any other properties will trigger an error when we set our custom formula.
   
8. Click Create. The Calculation property is created, but it doesn't have the formula we want.
9. To set the formula we want, we update the "calculationFormula" attribute on the property using the "Update a Property" endpoint of the Properties API. An example request body where you're only updating the calculationFormula:
   
    {
   "calculationFormula": "if string(priority)='high' then hs_acv/quote_lines else 5"
   }
10. Wait a few minutes and look up some values for your calculated property. You'll see that it calculates according to your new formula.

Limitations: You can only use 1 non-number property in the calculation. You can only reference that 1 non-number property in the "Additional Condition" section of the calculation builder in the HubSpot Portal. You can use an arbitrary number of number properties since you can keep using "Add to equation" button and including other properties.

You can only use properties listed in the HubSpot Portal's calculation builder. So some built-in properties like hs_object_id can't be used.