function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jay WeerappuligeJay Weerappulige 

Returning aggregate values on lightning page

I have built a lightning page where I want to display summarized data from an aggregate apex method. my method has some return value problem. this is my method and I want to return Ass and Credits 
my code is below
public class OutstandingCreditAuraController {
@AuraEnabled public static List<Assessment_Schedule__c> getOutstandingCredits(){
List<AggregateResult> agrResults = [SELECT Assessor_name__c ass,
Sum(Credits__c) credits
FROM Assessment_Schedule__c
WHERE (TEC_Status__c = 'Active' OR TEC_Status__c = 'On Hold') and (Company__c = 'CityFitness HO')
Group By Assessor_name__c];

for (AggregateResult ar: agrResults){
return((decimal)ar.get('credits'));
}
}
}
Best Answer chosen by Jay Weerappulige
Shun KosakaShun Kosaka
Hi Jay,
Try to run below sample!
- In the component, a list of AssessmentQueryResult is defined and doinit method is called for initialization.
- doinit method calls getSumOfCreditList method and getSumOfCreditList returns the list of AssessmentQueryResult. 
- component shows the list values by <aura:iteration>

Component: 
<aura:component controller="OutstandingCreditAuraController">
    <aura:attribute name="aqrList" type="AssessmentQueryResult[]"/> 
    <aura:handler name="init" value="{!this}" action="{!c.doInit}" />

    <aura:iteration items="{!v.aqrList}" var="aqr">
        <p>
            {!aqr.assessorName} - {!aqr.credits}
        </p>
    </aura:iteration>
</aura:component>

Controller (client-side): 
({
    doInit : function(component) {
        var action = component.get("c.getSumOfCreditsList");
        action.setCallback(this, function(a){
        component.set("v.aqrList", a.getReturnValue());
                           });
        $A.enqueueAction(action);
    } 
 })

Controller(Apex) 
public class OutstandingCreditAuraController{
    @AuraEnabled
    public static List<AssessmentQueryResult> getSumOfCreditsList(){
        List<Assessment_Schedule__c> assessmentScheduleList = [SELECT Id, Assessor_name__c, 
        Credits__c FROM Assessment_Schedule__c
        WHERE (TEC_Status__c = 'Active' OR TEC_Status__c = 'On Hold') and (Company__c = 'CityFitness HO')
        ]; 
        
        Map<String, Decimal> sumOfCreditsMap = new Map<String,Decimal>();
        for(Assessment_Schedule__c asch : assessmentScheduleList){
            If(sumOfCreditsMap.containsKey(asch.Assessor_name__c)){
                Decimal curSumValue = sumOfCreditsMap.get(asch.Assessor_name__c);
                sumOfCreditsMap.put(asch.Assessor_name__c, curSumValue + asch.Credits__c); 
                } else {
                sumOfCreditsMap.put(asch.Assessor_name__c,asch.Credits__c); 
                }
        }
        
        List<AssessmentQueryResult> asResults = new List<AssessmentQueryResult>();
        for(String assessorName : sumOfCreditsMap.keySet()){
            asResults.add(
                	new AssessmentQueryResult(assessorName, 
                                              sumOfCreditsMap.get(assessorName)
                                             )
            );
        }
        return asResults;
    }
}

Wrapper Class: 
public class AssessmentQueryResult {
	@AuraEnabled
	public String assessorName {get; set;}
	@AuraEnabled
	public Decimal credits {get; set;}

	//constructor
	public AssessmentQueryResult(String assessorName, Decimal credits){
		this.assessorName = assessorName;
		this.credits = credits;
	}
}
In above sample, to simplify client-side controller, getSumOfCreditList returns list not map directly. <aura:iteration> is used only for list. So, when you want getSumOfCreditList return map, client-side controller will be more complex to convert map to list.
References:
http://salesforce.stackexchange.com/questions/78903/is-it-possible-to-iterate-over-a-map-in-lightning
 

All Answers

Shun KosakaShun Kosaka
Hi,
Return type shouled be same return() as definition of getOutstandingCredits().
In your code, getOutStandingCredits() requires List<Assessment_Schedule__c> and return() method returns decimal value.

Your requirement is returning sum values. But I think Assessment_Schedule__c doesn't have the sum field. So define a new class to return query results. 
public class OutstandingCreditAuraController {
	@AuraEnabled
	public static List<AssessmentQueryResult> getOutstandingCredits(){
		List<AggregateResult> agrResults = [SELECT Assessor_name__c ass,
		Sum(Credits__c) credits
		FROM Assessment_Schedule__c
		WHERE (TEC_Status__c = 'Active' OR TEC_Status__c = 'On Hold') and (Company__c = 'CityFitness HO')
		Group By Assessor_name__c];

		List<AssessmentQueryResult> aqResults = new List<AssessmentQueryResult>(); //this list will be returned.
		for (AggregateResult ar: agrResults){
				aqResults.add(new AssessmentQueryResult(ar));  //add instances of AssessmentQueryResult
			}
		return aqResults; //return List of AssessmentQueryResult
	}
}

class AssessmentQueryResult {
	@AuraEnabled
	public String assessorName {get; set;}
	@AuraEnabled
	public Decimal credits {get; set;}

	//constructor
	public AssessmentQueryResult(AggregateResult ar){
		assessorName = (String)ar.get('ass');
		credits = (Decimal)ar.get('credits');
	}
}
Add <aura:attribute> with type AssessmentQueryResult to your lightning page.
Jay WeerappuligeJay Weerappulige
Thanks Shun for early reply It doesn't save -> unexpected token : List at this line of code List aqResults = new List(); Thanks again jay
Shun KosakaShun Kosaka
Hi,
Sorry for mistake. It will save when AssessmentQueryResult is inner class. However, inner class cannot be an attribute of lightning component. Try to save AssessmentQueryResult as top level class. (Add public keyword and save as another class)
Jay WeerappuligeJay Weerappulige
Thanks Shun I can save the class now. Jay
Jay WeerappuligeJay Weerappulige
Hi Shun, I have built the lightening page. But as the group by field Assessor_name__c is a formula field I can't fetch the data on the page. System.UnexpectedException: field 'Assessor_name__c' can not be grouped in a query call Is there an alternative way to group by a formula field. I know creating a text field and updating using workflow rule is a one solution. But I do not want to give more weight on this object as it already has much stuff running. This class can be modified to accommodate this issue?? Thanks Jay
Shun KosakaShun Kosaka
Hi Jay,
How about using map? 
List<Assessment_Schedule__c> assessmentScheduleList = [SELECT Id, Assessor_name__c, 
        Credits__c FROM Assessment_Schedule__c
        WHERE (TEC_Status__c = 'Active' OR TEC_Status__c = 'On Hold') and (Company__c = 'CityFitness HO')
        ];  // without GROUP BY
        
Map<String, Decimal> sumOfCreditsMap = new Map<String,Decimal>();  //Key:assessor name, Value : sum of credits
        for(Assessment_Schedule__c asch : assessmentScheduleList){
            If(sumOfCreditsMap.containsKey(asch.Assessor_name__c)){
                Decimal curSumValue = sumOfCreditsMap.get(asch.Assessor_name__c);
                sumOfCreditsMap.put(asch.Assessor_name__c, curSumValue + asch.Credits__c);  //if map has already key, add credits
                } else {
                sumOfCreditsMap.put(asch.Assessor_name__c, asch.Credits__c); //if not, put as new value
                }
        }
You can also set sums in a wrapper class like AssessmentQueryResult.
Jay WeerappuligeJay Weerappulige
Thanks so much, I will try this on Monday and update you Jay
Jay WeerappuligeJay Weerappulige
Hi Shun,
Sorry for hasseling again. I get the 'Expecting right curly bracket, found 'for'' error when compiling this piece of code line no 7 after the Map.
Also how do I get the return vales to the inner class AssessmentQueryResult

I am still learning the basic of Apex.

Thanks for your valuble support sofar.

Jay
 
Shun KosakaShun Kosaka
Hi Jay,
Try to run below sample!
- In the component, a list of AssessmentQueryResult is defined and doinit method is called for initialization.
- doinit method calls getSumOfCreditList method and getSumOfCreditList returns the list of AssessmentQueryResult. 
- component shows the list values by <aura:iteration>

Component: 
<aura:component controller="OutstandingCreditAuraController">
    <aura:attribute name="aqrList" type="AssessmentQueryResult[]"/> 
    <aura:handler name="init" value="{!this}" action="{!c.doInit}" />

    <aura:iteration items="{!v.aqrList}" var="aqr">
        <p>
            {!aqr.assessorName} - {!aqr.credits}
        </p>
    </aura:iteration>
</aura:component>

Controller (client-side): 
({
    doInit : function(component) {
        var action = component.get("c.getSumOfCreditsList");
        action.setCallback(this, function(a){
        component.set("v.aqrList", a.getReturnValue());
                           });
        $A.enqueueAction(action);
    } 
 })

Controller(Apex) 
public class OutstandingCreditAuraController{
    @AuraEnabled
    public static List<AssessmentQueryResult> getSumOfCreditsList(){
        List<Assessment_Schedule__c> assessmentScheduleList = [SELECT Id, Assessor_name__c, 
        Credits__c FROM Assessment_Schedule__c
        WHERE (TEC_Status__c = 'Active' OR TEC_Status__c = 'On Hold') and (Company__c = 'CityFitness HO')
        ]; 
        
        Map<String, Decimal> sumOfCreditsMap = new Map<String,Decimal>();
        for(Assessment_Schedule__c asch : assessmentScheduleList){
            If(sumOfCreditsMap.containsKey(asch.Assessor_name__c)){
                Decimal curSumValue = sumOfCreditsMap.get(asch.Assessor_name__c);
                sumOfCreditsMap.put(asch.Assessor_name__c, curSumValue + asch.Credits__c); 
                } else {
                sumOfCreditsMap.put(asch.Assessor_name__c,asch.Credits__c); 
                }
        }
        
        List<AssessmentQueryResult> asResults = new List<AssessmentQueryResult>();
        for(String assessorName : sumOfCreditsMap.keySet()){
            asResults.add(
                	new AssessmentQueryResult(assessorName, 
                                              sumOfCreditsMap.get(assessorName)
                                             )
            );
        }
        return asResults;
    }
}

Wrapper Class: 
public class AssessmentQueryResult {
	@AuraEnabled
	public String assessorName {get; set;}
	@AuraEnabled
	public Decimal credits {get; set;}

	//constructor
	public AssessmentQueryResult(String assessorName, Decimal credits){
		this.assessorName = assessorName;
		this.credits = credits;
	}
}
In above sample, to simplify client-side controller, getSumOfCreditList returns list not map directly. <aura:iteration> is used only for list. So, when you want getSumOfCreditList return map, client-side controller will be more complex to convert map to list.
References:
http://salesforce.stackexchange.com/questions/78903/is-it-possible-to-iterate-over-a-map-in-lightning
 
This was selected as the best answer
Jay WeerappuligeJay Weerappulige
Thanks very much Shun, it works now. It helped me a lot and learned lot
jay
Jay WeerappuligeJay Weerappulige
Hi Shun, I have an another question regarding couple of data values to fetch as JSON format to display on a lightning page as a bar graph. I have code written and it works and can be display on a lightning page as a normal columns. but I have the template for the graph as well I just need the data retrieve as JSON format. Your help on my previous class was used for this task. The link to thee question as below (it has code as well) https://developer.salesforce.com/forums/ForumsMain?id=9060G000000XbhrQAC Thanks Jay