Group aggregate model on a formula field

  • 2
  • Question
  • Updated 9 months ago
  • Answered
Can you not group an aggregate model on a text formula field? It doesn't come up as an option.
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb

Posted 3 years ago

  • 2
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
This is a limitation of SOQL aggregate queries.  Sorry. 

The very ugly work around is to create a text field that gets updated with workflow rules every time your formula field is updated.  That text field will be available to aggregate...
Photo of and thorough

and thorough

  • 736 Points 500 badge 2x thumb
Is there another workaround option, e.g., something that takes advantage of aggregation in charts?  It is impractical to create a workflow-updated field for every formula field one might want to group by (plus all existing records would need to be updated to trigger the workflow).  

For example, if I want to group by contacts' age at the time they started services (a custom formula field, let's call it AgeAtStart), I can create an aggregate model, group by contact, and include AgeAtStart as an aggregation (with min or max as the aggregate function).  I can then create a chart based on that model to show the number (and maybe percent?) of contacts in each age group by splitting on the minAgeAtStart aggregation, using contact as the data field, and a count aggregate function in the chart setup.  So that gets most of the way there.  But, I also need those chart results in a table.  Is that possible?

I know I can get these results in standard Salesforce reports, which do allow grouping by a formula field, but I am trying to include the age results within a Skuid page that is reporting on a number of areas from one page.
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Thank you for the clarification.
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
This reply was created from a merged topic originally titled Can't Group by Salesforce formula field in Agg Model..

I don't have the option to group an aggregate model using a salesforce formula field. Is that a limitation, or am I just doing something wrong?
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
Use process builder to take the result of the formula and place it in a non formula field - you can then use that to group
Photo of Sofware Developer Guy

Sofware Developer Guy

  • 1,354 Points 1k badge 2x thumb
I tried this but the process builder does not trigger when the formula field is updated.

Am I thinking of this the wrong way?
Photo of Ben Murray

Ben Murray

  • 1,084 Points 1k badge 2x thumb
Other way round use the process builder to take result from formula field and put it in a plain number/ text field you can group on. Process builder will trigger on record update/ create, not on formula update. Alternatively, take a look at Andyin the cloud declarative roll up summaries if your formula sums other records.
(Edited)