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
Chris MarzellaChris Marzella 

How to set a result from a SOQL query to a variable?

What do I need to get to assign the result of this query to a variable?
  
trigger SetTotalCreatures on Deck__c (after update) {

    Integer cardTotal = [SELECT Sum(Number_In_Deck__c) FROM Cards_in_Deck__c WHERE Type__c = 'Creature'];
    
    Deck__c.Total_Creatures__c = cardTotal;
}

 
Suraj GharatSuraj Gharat
Use variable of type AggregateResult as below: 
trigger SetTotalCreatures on Deck__c (after update) {

    List<AggregateResult> cardTotal = [SELECT Sum(Number_In_Deck__c) FROM Cards_in_Deck__c WHERE Type__c = 'Creature'];
    
    Deck__c.Total_Creatures__c = cardTotal[0].get('expr0');
}
Suraj GharatSuraj Gharat
What do you need to do exactly here ? I fear, this will bring next error.
Chris MarzellaChris Marzella
Using the edited version I get this error:  [Error] Error: Compile Error: Expression cannot be assigned at line -1 column -1

I am just trying to assign a number to a custom field based on the result from the SOQL query.
Suraj GharatSuraj Gharat
Ok, changed it this:  
trigger SetTotalCreatures on Deck__c (after update) {

    Integer cardTotal = [SELECT Count() FROM Cards_in_Deck__c WHERE Type__c = 'Creature'];
    
    Deck__c.Total_Creatures__c = cardTotal ;
}

 
Chris MarzellaChris Marzella
I get the same error with that code as well. I know the SOQL query is correct. I tested it in workbench and got the expected number so I don't think anything after the equals sign needs to change.
Suraj GharatSuraj Gharat
The error is with second line  
Deck__c.Total_Creatures__c = cardTotal ;
"Deck__c.Total_Creatures__c" is not the record that caused this trigger to fire. Apex Trigger gives us few context variables like "Trigger" to access such info. Try below code once. Notice I've also changed your trigger event. 
trigger SetTotalCreatures on Deck__c (before update) {

    Integer cardTotal = [SELECT Count() FROM Cards_in_Deck__c WHERE Type__c = 'Creature'];
    
    //Deck__c.Total_Creatures__c = cardTotal ;
	for(Deck__c deck:Trigger.new)
		deck.Total_Creatures__c=cardTotal;
}

 
Chris MarzellaChris Marzella
Code executes with no errors, but it is returning the incorrect value due to the change in the SOQL query.
Suraj GharatSuraj Gharat
My bad, I changed it from SUM to COUNT. May be, you need to do type casting here depending on type of "Total_Creatures__c" field.
  
trigger SetTotalCreatures on Deck__c (before update) {

    List<AggregateResult> cardTotal = [SELECT Sum(Number_In_Deck__c) FROM Cards_in_Deck__c WHERE Type__c = 'Creature'];
	    
    //Deck__c.Total_Creatures__c = cardTotal ;
	for(Deck__c deck:Trigger.new)
		deck.Total_Creatures__c=cardTotal[0].get('expr0');
}

 
Amit Chaudhary 8Amit Chaudhary 8
Hi Chris,

Please check below post. I hope that will help u
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm
http://blog.jeffdouglas.com/2010/04/12/using-aggregateresult-in-salesforce-com-soql/
  
trigger SetTotalCreatures on Deck__c (after update) 
{
	AggregateResult[] groupedResults  = [ SELECT Sum(Number_In_Deck__c) aver FROM Cards_in_Deck__c WHERE Type__c = 'Creature'];
	for(Deck__c obj : Trigger.New)
	{
		obj.Total_Creatures__c = groupedResults[0].get('aver');
	}
}
http://blog.terrasky.com/blog/useful-soql

Please let us know if this will help u

Thanks,
Amit Chaudhary
Chris MarzellaChris Marzella

Suraj, using your code I get :  [Error] Error: Compile Error: Illegal assignment from Object to Decimal at line 7 column 9

Amit, using your code I get: [Error] Error: Compile Error: Illegal assignment from Object to Decimal at line 6 column 9

for some clarification on the fields: cards in deck is a number, type is a picklist, total creatures is a number (not sure if that matters)
Amit Chaudhary 8Amit Chaudhary 8
Please try below code. I hope that will help u 
trigger SetTotalCreatures on Deck__c (after update) 
{
	AggregateResult[] groupedResults  = [ SELECT Sum(Number_In_Deck__c) aver FROM Cards_in_Deck__c WHERE Type__c = 'Creature'];
	for(Deck__c obj : Trigger.New)
	{
		double  sum = double.valueOf(groupedResults[0].get('aver'));
		obj.Total_Creatures__c = Decimal.valueOf(sum);
	}
}
Please check  below post for more info
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000BM0kIAG
Please let us know if this will help u

Thanks
Amit Chaudhary
 
Chris MarzellaChris Marzella
Amit this works. Thank you. Now the only issue I have is it adds up the "Number in Deck" field accross all decks. How do I get it to only add the values for the current deck?
Chris MarzellaChris Marzella
User-added image
User-added image

The total creatures should add up to 12. It is accessing the "Number in Deck" field for every record not just the current record.
Chris MarzellaChris Marzella
Cards in Deck is a junction object between Card__c and Deck__c.
Amit Chaudhary 8Amit Chaudhary 8
The you can create the fomula field on jucntion object for Number of Deck and. Thn create on roll up summery field on deck object to get the sum .. no need of any trigger here
Chris MarzellaChris Marzella
Yes, that is what I orginally tried to do, but for some reason it does not let me filter by the "type" field. 
Amit Chaudhary 8Amit Chaudhary 8
In the rollup summery field you can use "Filter Criteria". like below
User-added image

I hope that will help u
 
Chris MarzellaChris Marzella
This is what I see. No type Option.
User-added image
Amit Chaudhary 8Amit Chaudhary 8
Please checl FLS field level security and and provide access to admin and then try again.
I hope type field you have on card in desk object right
Chris MarzellaChris Marzella
Yes the "Type" field is on "Cards in Deck"