topic Re: Truncate a Property Using a Calculation Property in Tips, Tricks & Best Practices

Truncate a Property Using a Calculation Property

LWest0 — Tue, 30 Jan 2024 21:31:43 GMT

Hello,

I'd like to create a new property on the Deal object that takes the Record ID and truncates it to 5 values. Is this possible? I've tried to do calc like LEFT(Record ID, -5), etc. but I haven't been able to land on anything. Any known sollution?

Thanks!

Re: Truncate a Property Using a Calculation Property

trevordjones — Tue, 30 Jan 2024 22:16:10 GMT

Hi @LWest0 The only idea I can offer you is to use a custom coded workflow action to grab those last five digits and copy them into another property. However, custom coded workflow actions will require an OperationsHub Professional license.

@Jnix284 and @danmoyle , do you have any other ideas that might help, here?

Re: Truncate a Property Using a Calculation Property

LWest0 — Tue, 30 Jan 2024 23:53:57 GMT

Hi Trevor!

Thanks for getting back to me & exposing a solution and our limitations! We don't currently have that license so any other suggestions would gladly be accepted.

Thank you!

Re: Truncate a Property Using a Calculation Property

trevordjones — Wed, 31 Jan 2024 02:46:53 GMT

I wish I had more to offer! I can say that I personally know of no solutions that will do what you are asking for without custom code.

Maybe Jennifer or Dan will have an alternative to offer.

Re: Truncate a Property Using a Calculation Property

karstenkoehler — Wed, 31 Jan 2024 05:45:30 GMT

Hi @LWest0,

I can confirm what @trevordjones shared; as far as I know this requires an Operations Hub Professional or Enterprise subscription if you want to do it natively. https://developers.hubspot.com/docs/api/workflows/custom-code-actions

You could alternatively explore third-party solutions like Zapier to see if their automation capabilities help you solve this: https://knowledge.hubspot.com/integrations/how-to-use-zapier-and-hubspot

Best regards

Re: Truncate a Property Using a Calculation Property

danmoyle — Wed, 31 Jan 2024 19:38:26 GMT

Hi @LWest0 (thanks for the tag @trevordjones). Could I ask - why change the record ID in a new property? Is there a different solution for something you're trying to solve?

If record IDs are 11 digits like "19283620712" and you truncate it, you could have a bunch of Deals with the same 5-digit property "19283." I'm unsure what this would do for you?

As Trevor & Karsten mentioned, there's probably an Ops Hub custom cide you might be able to do, but if we know the why, maybe there's something altogether different to offer?

Re: Truncate a Property Using a Calculation Property

Jnix284 — Thu, 01 Feb 2024 12:02:17 GMT

@trevordjones and @LWest0, I also don't have a workaround solution without having access to custom coded actions...unless you want to go the really really manual route.

You can export your data to Excel, create a second column for your custom property and use the =TRUNC(number,[num_digits]) formula to generate your new value, then import it back to HubSpot.

As @danmoyle mentioned, if you can share your use case or more details, we can likely come up with a solution that could solve for the need in a different way.

Re: Truncate a Property Using a Calculation Property

LWest0 — Thu, 01 Feb 2024 18:09:22 GMT

Thanks to you all and good point Dan! We are essentially looking for an automated unique ID # with 5 digits. You're right that truncating Record ID wouldnt work in that regard. Didn't see a way to set an automated unique number either. Is this possible? Thanks so much again!

Re: Truncate a Property Using a Calculation Property

trevordjones — Thu, 01 Feb 2024 18:15:47 GMT

Hi @LWest0 ,

Actually this community post from @karstenkoehler explains a workaround that you could use to do this quite well.

Re: Truncate a Property Using a Calculation Property

Jnix284 — Thu, 01 Feb 2024 18:39:45 GMT

Nice find @trevordjones, thanks for sharing!

Re: Truncate a Property Using a Calculation Property

LWest0 — Fri, 02 Feb 2024 01:45:52 GMT

Yes! Thank you so much Trevor & everyone else who helped out.