2

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?

Improve this question

2 Answers 2

Reset to default
3

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
Improve this answer
6
  • Ok this is awesome. I managed to get the first two fields to work, but for Term__c I cannot do it that way. For reference:
    – ohseekay
    May 18, 2018 at 3:18
  • CASE(MONTH(Sales_Date__c), 1,"1H / 1Q", 2,"1H / 1Q", 3,"1H / 1Q", 4,"1H / 2Q", 5,"1H / 2Q", 6,"1H / 2Q", 7,"2H / 3Q", 8,"2H / 3Q", 9,"2H / 3Q", 10,"2H / 4Q", 11,"2H / 4Q", "2H / 4Q")
    – ohseekay
    May 18, 2018 at 3:19
  • @ohseekay Oh, I see. Yeah, you'd have to make a backing field (plain text) that could be populated by a workflow field update/process builder/trigger to set the correct value.
    – sfdcfox
    May 18, 2018 at 3:20
  • It derives its value based on the month of the sales date, and I want to be able to group by this term value in the table too.
    – ohseekay
    May 18, 2018 at 3:20
  • Sorry I don't understand. What is a backing field? How do I do this?
    – ohseekay
    May 18, 2018 at 3:20
0

Since you are referring all the fields on Parent object Pipeline__c and only you are performing SUM(Profit__c) from Child object Sub_Pipeline__c, I would suggest you to create a Rollup Summary field on Pipeline__c object since relationship is Master-Detail.

And then you can perform the query on Parent object easily and might not to use Group By and simply use ORDER BY on those attributes.

Just check that how you can get Fiscal Year on Parent object.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .