Rollup fields have been fantastic for aggregating number properties. I think the next step should be to include date properties for rollups. This would allow us to easily create properties to represent the earliest, oldest, most recent, or next upcoming date on associated records.
As an example, our financial services clients need to know when an individual’s join date with their institution is. This is not always readily known in their core data and must be inferred from the individual’s open accounts. Within HubSpot this must be solved through a workflow using a complex format data calculation or a custom code action and requires Operations Hub Pro+.
A property like this would also make it easy to create all sorts of activity date rollups for custom objects.
@Jon-PrairiePath, the simplest implementation I have come up with is to use custom Today properties on each object that are updated through workflows and a Time Between calculation on the desired roll-up date property and the custom Today property. You cannot use the simpler Time Since calculation because those cannot be used in rollup properties. Once you have this Time Between calculation, you can create a rollup on the desired object using the duration from the Time Between property. Finally, create another calculation property using custom equation and a Date output to calculate: Today - [Time Between Rollup]. I’ll detail my use case as an example of how this works: I want to rollup the earliest open date on a custom object, Account, to represent the Join Date for a custom object, Member.
Create a date property, Today, on the Account object.
Create a workflow to update the Account Today property daily.
Create a calculation property, Time Since Account Open Date, on the Account object using Time Between with start date set as Open Date and end date set as Today.
Create a rollup property, Time Since Earliest Open Account Date, on the Member object using rollup type Max (this will give me the greatest duration or oldest Open Date. Min would provide the least duration or newest Open date.) and number format Duration.
Create a date property, Today, on the Member object.
Create a workflow to update the Member Today property daily.
Create a calculation property, Join Date, on the Member object, using Custom Equation and Date output type with the calculation: Today - Time Since Earliest Open Account Date
This method avoids the need for a custom code action and the worklfows are simple. However, this is still overly complex given the outcome. If we were at least able to use the Time Since calculation in a rollup, then we would be able to eliminate the Today property and workflow at the Account level.
@chris-profitpad, this will work for date properties that are tightly coupled to the record create date. This is not viable for records that are sourced from external systems and are not natively created in HubSpot. For example, in my use case, Account and Member are sourced from a core banking system. The create date will represent the date the record was first synced to HubSpot which is not representative of the rollup I want to perform.
For anyone looking to implement a date rollup, I would check first to see if your use case fits the pattern that @chris-profitpad has presented. If so, then you may use the much easier to implement solution that they have shared.
@JTBuys totally agreed. Fortunately what I’ve shared is totally fine for my use case, since I always want the invoiced date of the most recently created record. It is a pain for imports though.
I really want hubspot to implement a “today” property on objects as well, rather than having to create a workflow that re-enrolls every record every single day (rendering the “last modified date” property completely useless). It would have so many use cases.
