Hello Salesforce people,
I have something I am trying to do in Salesforce, and I am coming up on a lot of limitations within the platform, and I wanted to ask the best way to do what I am trying to get set up.
The goal is to have an "always up to date" ARR value in my accounts that my business can report on. To do this, I need to roll up the values between all of the active opportunities connected to those accounts, and live-update the value when we are before, within, or have passed the opportunity start and end dates.
The first limitation is that I cannot use Today() or check if date fields are blank in rollup field criteria, this forces me to make a formula checkbox in the opportunities that does the date checking, then query the status of that checkbox field in the rollup.
The next is that I cannot actually use formula fields in rollup field criteria either, since those values are only created on-read and are not stored in the Salesforce database.
This requires that I build a workflow or process build that checks the value of the formula checkbox and updates a second non-formula checkbox field, then query that second field in the rollup criteria.
Then the final limitation is that, though this will report the accurate number, after editing the opportunity to check the boxes by filling in the correct date fields, there is a time when the end date on the opportunity arrives and passes, which does not trigger the workflow, does not check the second box, which then makes the rolled-up values incorrect. The workflow is only triggered when the record is updated by hand, eg. by a user.
This became frustrating, because this seems like something that should be easy to do, but has me stumped. Please let me know if I am correctly going about this or if there is something I am missing that I could be using to have more success.