Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
mmaxtrammaxtra 

SOQL Query Help please

Hi:

   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 
Arts1.01 
Arts0.51 
English0.52 
Math0.51 
Science0.52 
Social Studies0.52 
 
I want to Group Arts together into one Line...
like 
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...
 
Thanks 
 
 
 
 


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


mmaxtrammaxtra
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...
 


mtbclimbermtbclimber
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.   


CaptainObviousCaptainObvious
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 ;)