I Need to Store Number fields per used based on User role Hierarchy and need to do hierarchical Rollup later on once data is updated. Did anyone tried to do this in Salesforce.
One Approach I am thinking is store it on user record but not sure if there any way to auto rollup this data. Wanted to check what will be best data model for this.
Please find below is the sample data –
Note: I am aware if forecasting in Salesforce, which is not going to work in this scenario.
I would recommend creating a custom object with fields that mirror the roles in your organization’s hierarchy. I strongly suspect that you’ll want to have separate fields for the roll-ups since, depending on where you start, those fields will also be populated by a certain number of managers and other people who occupy those roles (assuming roles mirror positions). Adding an extra field to a User’s record to query on or use as a look-up, doesn’t seem to serve any real purpose here.
Its one thing to be off by 1 where there’s only 1 person in the hierarchy that fills that role, but its an entirely different matter to be off by 20 to 200 (depending on the size of your org) when trying to roll-up the lower and mid level management positions.
That having been said, its not clear to me why you couldn’t just do this with a report which could easily be run on a regular basis. I’d think that users in different roles could easily be bucketed then the results manipulated to display however you want.[shrug]
The real issue you’re going to have is getting the data into the fields on your custom object and maintaining it. Off-hand, I don’t see an obvious way other than to query the number of users in each role which is why I’d recommend doing a report instead. The only initially obvious way to maintain it that I can see is to run a regularly scheduled batch update unless you have a way of tracking changes to User roles via a trigger updates any role changes recorded into your custom object fields.
Hopefully, this will give you some ideas to consider in terms of how to approach your project. I’m confident there are numerous others you could use as well.