[SalesForce] Formula field cannot be grouped in SOQL GROUP BY query call

I have a Pipeline__c object with fields Segment__c, Status__c, and Sales_Date__c (among many other fields).

I also have a Sub_Pipeline__c object which is related to Pipeline__c. Inside I have the following fields:

  • Pipeline_Project_Name__r: Master-detail relationship with Pipeline__c

  • Segment_f__c: formula field TEXT(Pipeline_Project_Name__r.Segment__c) (I do this because I want the value of Segment__c in Sub_Pipeline__c to be read-only.)

  • Status_f__c: formula field TEXT(Pipeline_Project_Name__r.Status__c) (similar reason as above)

  • Term_f__c: formula field similar to above, that derives its value from Sales_Date__c in Pipeline__c

I would like to create a query similar to the following:

SELECT Segment_f__c, Status_f__c, Term__c, SUM(Profit__c)
FROM Sub_Pipeline__c WHERE Fiscal_Year__c = '2018'
GROUP BY Segment_f__c, Status_f__c, Term__c

However, when I tried running this I got the error message

Field 'Segment_f__c' can not be grouped in a query call

This error message is the same for the other two fields as well.

Does this mean I cannot use formula fields in GROUP BY clause? If so, what should I do to get to the end-result I want?

Best Answer

You can't group by formulas, but you can group the fields, if they are groupable:

SELECT Pipeline_Project_Name__r.Segment__c, Pipeline_Project_Name__r.Status__c, Pipeline_Project_Name__r.Sales_Date__c, SUM(Profit__c)
FROM Sub_Pipeline__c WHERE Fiscal_Year__c = '2018'
GROUP BY Pipeline_Project_Name__r.Segment__c, Pipeline_Project_Name__r.Status__c, Pipeline_Project_Name__r.Sales_Date__c
Related Topic