You need to sign in to do that
Don't have an account?
Ludivine
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 ;
}
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 ;
}
Can you help me to fix it?
Thank you!!