+ Start a Discussion
LudivineLudivine 

Trigger Roll up Sum on Costs

Hi,

I need your help to write a trigger on Tasks which calculates the total Costs from all the SubTasks on a Project Stage
and update it in the Main task fields. I have an error when I try to save the trigger:
Compile Error: Loop with query must provide a statement

trigger IMSSumCostsOnTasks on TASKRAY__Project_Task__c (After insert, After Update) {
// TaskRay Project Task is a related Task object from TaskRay project object.
Set<Id> setTaskRayTaskIds=new Set<Id>();
For (TASKRAY__Project_Task__c c : trigger.new)
setTaskRayTaskIds.add(c.id);
//List of sub Tasks : Sub Tasks are those with empty value in Project stage
List<TASKRAY__Project_Task__c>ListTasks1 =[SELECT TASKRAY__Project__c,Type__c 
from TASKRAY__Project_Task__c 
where id in :setTaskRayTaskIds
AND Project_Stage__c = null 
AND TASKRAY__Project__c!=null
and type__c in ( 'Idea', 'Feasibility', 'Development', 'Validation and pre-launch','Feasibility','Launch')];

/*Main Tasks to update : main tasks are created from the project , 
Previously at task creation, the selected value in Type field is copied in project_Stage field to identify 
them as Main tasks.(Type values are  'Idea', 'Feasibility', 'Development', 'Validation and pre-launch','Feasibility','Launch')*/
List<TASKRAY__Project_Task__c>lstMainTasks1 = [SELECT TASKRAY__Project__c,Project_Stage__c from TASKRAY__Project_Task__c 
where id in :setTaskRayTaskIds 
AND TASKRAY__Project__c!=null
AND Project_Stage__c in ( 'Idea', 'Feasibility', 'Development', 'Validation and pre-launch','Feasibility','Launch')];

/*We want to update the costs & resources found in the Sub tasks by Type in the Main Stage Task
For Main Task 1, if Project Stage= idea, Each Type= Idea of subTasks of the same project have to be sum up*/

For( AggregateResult result:[Select TASKRAY__Project__c,Type__c,
//Sum Resources in planned fields on the sub tasks:
sum(FIR_R_D_Planned__c),
sum(FIR_CoE_planned__c),
sum(FIR_Marketing_Sales_planned__c),
sum(FIR_Plant_Technical_Planned__c),
sum(FIR_Field_Technical_Services_Planned__c),
sum(FIR_Other_Resources_Planned__c),
//Sum Costs in Planned fields
sum(FIC_Trials_Planned__c),
sum(FIC_Technical_Outsourcing_Planned__c),
sum(FIC_Market_Intelligence_Planned__c),
Sum(FIC_Marcom_planned__c),
Sum(FIC_Other_Cost_planned__c),
//Sum Resources in Actual fields on the sub tasks:
sum(FIR_R_D_Actual__c),
sum(FIR_CoE_Actual__c),
sum(FIR_Marketing_Sales_Actual__c),
sum(FIR_Plant_Technical_Actual__c),
sum(FIR_Field_Technical_Services_Actual__c),
sum(FIR_Other_Resources_Actual__c),
sum(FIC_Trials_Actual__c),
sum(FIC_Technical_Outsourcing_Actual__c),
sum(FIC_Market_Intelligence_Actual__c),
Sum(FIC_Marcom_Actual__c),
//Sum costs in Actual Fields
Sum(FIC_Other_Cost_Actual__c)
from TASKRAY__Project_Task__c 
WHERE (
Id IN :setTaskRayTaskIds 
AND Project_Stage__c = null 
AND TASKRAY__Project__c != null 
AND Type__c in ( 'Idea', 'Feasibility', 'Development', 'Validation and pre-launch','Feasibility','Launch') 
// Remove from the query all empty resources or costs to avoid too many sql limitation error
AND(
(FIR_R_D_Planned__c > 0) 
OR(FIR_CoE_planned__c > 0) 
OR(FIR_Marketing_Sales_planned__c> 0) 
OR(FIR_Field_Technical_Services_Planned__c > 0) 
OR(FIR_Other_Resources_Planned__c> 0) 
OR(FIC_Trials_Planned__c> 0) 
OR (FIC_Technical_Outsourcing_Planned__c> 0) 
OR (FIC_Market_Intelligence_Planned__c> 0) 
OR(FIR_Plant_Technical_Planned__c>0)
OR (FIC_Marcom_planned__c> 0) 
OR (FIC_Other_Cost_planned__c> 0) 
OR (FIR_R_D_Actual__c> 0) OR (FIR_CoE_Actual__c> 0) 
OR (FIR_Marketing_Sales_Actual__c> 0) 
OR (FIR_Plant_Technical_Actual__c> 0) 
OR (FIR_Field_Technical_Services_Actual__c> 0) 
OR (FIR_Other_Resources_Actual__c> 0) 
OR (FIC_Trials_Actual__c> 0) 
OR (FIC_Technical_Outsourcing_Actual__c> 0) 
OR (FIC_Market_Intelligence_Actual__c> 0) 
OR (FIC_Marcom_Actual__c> 0) 
OR (FIC_Other_Cost_Actual__c> 0)
)
)
GROUP BY TASKRAY__Project__c,Type__c]);

// to start, Let's try to update the  field FIR_R_D_Planned__c               
{
        for(TASKRAY__Project_Task__c p : lstMainTasks1 )
        {
            if(result.get('TASKRAY__Project__c') == p.TASKRAY__Project__c)
            {
            if(result.get('Type__c') == p.project_stage__c){
                if(p.FIR_R_D_Planned__c == null)
                {
                    p.FIR_R_D_Planned__c = 0;
                }
                //p.Amount__c = p.Amount__c + Decimal.ValueOf(String.ValueOf(result.get('expr0')));
                p.FIR_R_D_Planned__c =p.FIR_R_D_Planned__c + Decimal.ValueOf(String.ValueOf(result.get('expr0')));
                ListMainTasks1.add(p);
            }
        }}
    }
update lstMainTasks1 ;
}
LudivineLudivine
I finally managed to save my trigger with no error now, obviously it does not work...
Can you help me to fix it?
Thank you!!
trigger IMSSumCostsOnTasks on TASKRAY__Project_Task__c (After insert, After Update) {
// TaskRay Project Task is a related Task object from TaskRay project object.
Set<Id> setTaskRayTaskIds=new Set<Id>();
For (TASKRAY__Project_Task__c c : trigger.new)
setTaskRayTaskIds.add(c.id);
//List of sub Tasks : Sub Tasks are those with empty value in Project stage
List<TASKRAY__Project_Task__c>ListTasks1 =[SELECT TASKRAY__Project__c,Type__c 
from TASKRAY__Project_Task__c 
where id in :setTaskRayTaskIds
AND Project_Stage__c = null 
AND TASKRAY__Project__c!=null
and type__c in ( 'Idea', 'Feasibility', 'Development', 'Validation and pre-launch','Feasibility','Launch')];

/*Main Tasks to update : main tasks are created from the project , 
Previously at task creation, the selected value in Type field is copied in project_Stage field to identify 
them as Main tasks.(Type values are  'Idea', 'Feasibility', 'Development', 'Validation and pre-launch','Feasibility','Launch')*/
List<TASKRAY__Project_Task__c>lstMainTasks1 = [SELECT TASKRAY__Project__c,Project_Stage__c from TASKRAY__Project_Task__c 
where id in :setTaskRayTaskIds
AND TASKRAY__Project__c!=null
AND Project_Stage__c in ( 'Idea', 'Feasibility', 'Development', 'Validation and pre-launch','Feasibility','Launch')];

/*We want to update the costs & resources found in the Sub tasks by Type in the Main Stage Task
For Main Task 1, if Project Stage= idea, Each Type= Idea of subTasks of the same project have to be sum up*/

For( AggregateResult result:[Select TASKRAY__Project__c,Type__c,
sum(FIR_R_D_Planned__c),
sum(FIR_CoE_planned__c),
sum(FIR_Marketing_Sales_planned__c),
sum(FIR_Plant_Technical_Planned__c),
sum(FIR_Field_Technical_Services_Planned__c),
sum(FIR_Other_Resources_Planned__c),
sum(FIC_Trials_Planned__c),
sum(FIC_Technical_Outsourcing_Planned__c),
sum(FIC_Market_Intelligence_Planned__c),
Sum(FIC_Marcom_planned__c),
Sum(FIC_Other_Cost_planned__c),
sum(FIR_R_D_Actual__c),
sum(FIR_CoE_Actual__c),
sum(FIR_Marketing_Sales_Actual__c),
sum(FIR_Plant_Technical_Actual__c),
sum(FIR_Field_Technical_Services_Actual__c),
sum(FIR_Other_Resources_Actual__c),
sum(FIC_Trials_Actual__c),
sum(FIC_Technical_Outsourcing_Actual__c),
sum(FIC_Market_Intelligence_Actual__c),
Sum(FIC_Marcom_Actual__c),
Sum(FIC_Other_Cost_Actual__c)
from TASKRAY__Project_Task__c 
WHERE (
Id IN :setTaskRayTaskIds 
AND Project_Stage__c = null 
AND TASKRAY__Project__c != null 
AND Type__c in ( 'Idea', 'Feasibility', 'Development', 'Validation and pre-launch','Feasibility','Launch') 
AND(
(FIR_R_D_Planned__c > 0) 
OR(FIR_CoE_planned__c > 0) 
OR(FIR_Marketing_Sales_planned__c> 0) 
OR(FIR_Field_Technical_Services_Planned__c > 0) 
OR(FIR_Other_Resources_Planned__c> 0) 
OR(FIC_Trials_Planned__c>0) 
OR(FIC_Technical_Outsourcing_Planned__c>0) 
OR (FIC_Market_Intelligence_Planned__c> 0) 
OR(FIR_Plant_Technical_Planned__c>0)
OR(FIC_Marcom_planned__c>0) 
OR(FIC_Other_Cost_planned__c>0) 
OR(FIR_R_D_Actual__c>0) 
OR(FIR_CoE_Actual__c>0) 
OR(FIR_Marketing_Sales_Actual__c>0) 
OR(FIR_Plant_Technical_Actual__c>0) 
OR(FIR_Field_Technical_Services_Actual__c>0) 
OR(FIR_Other_Resources_Actual__c>0) 
OR(FIC_Trials_Actual__c>0) 
OR(FIC_Technical_Outsourcing_Actual__c>0) 
OR(FIC_Market_Intelligence_Actual__c>0) 
OR(FIC_Marcom_Actual__c>0) 
OR(FIC_Other_Cost_Actual__c>0)
)
)
GROUP BY TASKRAY__Project__c,Type__c]){
for(TASKRAY__Project_Task__c p : lstMainTasks1){
if(result.get('TASKRAY__Project__c') == p.TASKRAY__Project__c){
if(result.get('Type__c') == p.project_stage__c){
if(p.FIR_R_D_Planned__c == null){
p.FIR_R_D_Planned__c = 0;}
//p.Amount__c = p.Amount__c + Decimal.ValueOf(String.ValueOf(result.get('expr0')));
p.FIR_R_D_Planned__c =p.FIR_R_D_Planned__c + Decimal.ValueOf(String.ValueOf(result.get('expr0')));
lstMainTasks1.add(p);}
}}}
update lstMainTasks1 ;}