+ Start a Discussion

SOQL Query Help please


   I have the following query:


SELECT Student__c, Name, MintoPass__c, Term,Subject FROM Courses Where Student__c='XXX'

 Which gives me table like this:

Subject AreaMinPassTerm 
Social Studies0.52 


I want to Group Arts together into one Line...


Subjectarea MinPass Term

Arts                1.5        1

English             .5         1


Is there a way to break this table up into Terms???

I have place the query above into a List<Courses> student...







Depending on the size of the dataset you can look at SOQL Aggregates

Yeah I saw those try even doing those... but for some reason you can not do GROUP BY on Formula fields or Number fields...

Go figure... Salesforce comes out with something real good and its not even close to possible to do anything unless u have strings...


Like I would love to group by Term and Grade and Subject and rollup or SUm up Credits earned...

But Term, Grade one is a formula field and the other is a number field...

In apex it will not give an error until you spit the result out in VF... it will tell you Term can not be aggregated field...etc...



Yes you need to translate the aggregate result object into a structure for use with Visualforce. This is a known issue, effectively Visualforce needs to support a map accessor syntax and perhaps maps in Apex need an entry member structure a la java.


As for addressing your problem with formulas you could create additional fields with the static value and a workflow field update that copies the formula value to the field that can be grouped.  

I've been experimenting with the new aggregate functions and I too ran into the same issue with a formula field. Fortunately, the formula can only take 3 possible values- so I created a picklist with the 3 values. My trigger looks at the formula and sets the same value in the picklist- allowing me to GROUP BY the new picklist ;)