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
Josh SchwerdtfegerJosh Schwerdtfeger 

passing custom object id into the SOQL query

I have Opportunity as the parent object to a custom object Course.  I want to build a soql that updates through a trigger a field on the course object, specific to each course.  This is what i've got so far but i am not finding how to pass the course object into the where of the query.  Any help would be great. 
trigger WonNov on Course__c (before update) {
   AggregateResult[] groupedResults = [
    Select SUM (Amount) sum from Opportunity WHERE Courseid__c = :recordId];
        for (Course__c obj : Trigger.new)
        {
        double sum =
double.valueOf(groupedResults[0].get('sum'));
        obj.Won_Nov_Outings__c =
decimal.valueOf(sum);
            }
    }
Best Answer chosen by Josh Schwerdtfeger
Amit Chaudhary 8Amit Chaudhary 8
Try to update your code lik below 
trigger WonNov on Course__c (before update) 
{
	Set<Id> setRecordId = new Set<Id>();
	
	for (Course__c obj : Trigger.new)
	{
		setRecordId.add(obj.id) ;
	}
	
	AggregateResult[] groupedResults = [ Select SUM (Amount) sum ,Courseid__c from Opportunity WHERE Courseid__c in :setRecordId group by Courseid__c ];
	
	Map<String , AggregateResult > mapAG = new Map<String , AggregateResult >();
	for(AggregateResult ar : groupedResults)
	{
		mapAG.put(String.valueOf(ar.get('Courseid__c')) , ar );
	}
	
	for (Course__c obj : Trigger.new)
	{
	
		if( mapAG.containsKey(obj.id) )
		{
			AggregateResult ar = mapAG.get(obj.id);
			double sum =double.valueOf(ar.get('sum'));
			obj.Won_Nov_Outings__c = decimal.valueOf(sum);
			
		}
	}
}

Let us know if this will help you

 

All Answers

Amit Chaudhary 8Amit Chaudhary 8
Try to update your code lik below 
trigger WonNov on Course__c (before update) 
{
	Set<Id> setRecordId = new Set<Id>();
	
	for (Course__c obj : Trigger.new)
	{
		setRecordId.add(obj.id) ;
	}
	
	AggregateResult[] groupedResults = [ Select SUM (Amount) sum ,Courseid__c from Opportunity WHERE Courseid__c in :setRecordId group by Courseid__c ];
	
	Map<String , AggregateResult > mapAG = new Map<String , AggregateResult >();
	for(AggregateResult ar : groupedResults)
	{
		mapAG.put(String.valueOf(ar.get('Courseid__c')) , ar );
	}
	
	for (Course__c obj : Trigger.new)
	{
	
		if( mapAG.containsKey(obj.id) )
		{
			AggregateResult ar = mapAG.get(obj.id);
			double sum =double.valueOf(ar.get('sum'));
			obj.Won_Nov_Outings__c = decimal.valueOf(sum);
			
		}
	}
}

Let us know if this will help you

 
This was selected as the best answer
Josh SchwerdtfegerJosh Schwerdtfeger
Amit,
This is great, but i'm getting an error : "field 'Courseid__c' can not be grouped in a query call"
Amit Chaudhary 8Amit Chaudhary 8
What is the data type of Courseid__c field ?
Josh SchwerdtfegerJosh Schwerdtfeger
It's the ID for the custom object Course  i'm not sure what the data type is.  We have the courseid__c field attached to each opportunity. 
Josh SchwerdtfegerJosh Schwerdtfeger
The data type on the opportunity object  of Courseid__c is a formula.
 
Amit Chaudhary 8Amit Chaudhary 8
Got it that is why formula field can not be group by that is why you are getting error.

Idea is posted for same "Allow SOQL GROUP BY on formula fields"
1) https://success.salesforce.com/ideaView?id=08730000000HlNtAAK

Try to group by some other field.
 
Josh SchwerdtfegerJosh Schwerdtfeger
Amit,
This is great and i pulled and grouped by a different field and it works!  Thank you! 
trigger WonNov on Course__c (before update) {

    Set<Id> setRecordId = new Set<Id>();
    
    for (Course__c obj : Trigger.new)
    {
        setRecordId.add(obj.id) ;
    }
    
    AggregateResult[] groupedResults = [ Select SUM (Amount) sum, Course_Lookup__c from Opportunity WHERE Courseid__c in :setRecordId AND Opportunity_Type__c ='Outing' AND Probability >94.00 AND Event_Date__c = THIS_YEAR AND Event_Month__c = '11_November' GROUP BY Course_Lookup__c ];
    
    Map<String , AggregateResult > mapAG = new Map<String , AggregateResult >();
    for(AggregateResult ar : groupedResults)
    {
        mapAG.put(String.valueOf(ar.get('Course_Lookup__c')) , ar );
    }
    
    for (Course__c obj : Trigger.new)
    {
    
        if( mapAG.containsKey(obj.id) )
        {
            AggregateResult ar = mapAG.get(obj.id);
            double sum =double.valueOf(ar.get('sum'));
            obj.Won_Nov_Outings__c = decimal.valueOf(sum);
            
        }
    }
}