You need to sign in to do that
Don't have an account?
The Aquinois
Facing To many SOQL queries/Governor Limit
I would like to great everyone there. This is my very first post in this forum. I am facing a real problem. I am responsible for developing on Salesforce for an NGO and begin using Salesforce for 8 months. So I am realy new.
I have learned about the system limitations, such as governor limits. Apex best practice, avoid SOQL queries or DMl code in for/while loop.
So, I try to do my best. but, I am facing a real probleme that I have to explain a litle bit. For an NGO, it is import to report monthly to the donors. It comes to my boss this idea to automatically generate indicators values as the projects progress. as a matter of fact, every single time a user insert/update/delete a record, the related indicator must be update. so far, so good. the problem comes when we consider that the organization produces a very high quantity of informations. so, it's impossible to insert then one by oneusing forms. So we need to implement mass Edit/update or import with CSV file. Which is good. There is alot of indicators, so, when an action trigger on an object, it is very important that the related indicator be created or updated for the precise month. Thus, I use the following code
A) Utility Class I use to handle some functions that I need for all trigger handlers
This is a trigger handler class
But see it for activities:
Trigger:
magine these functions being calling for every row in a CSV file or comming from mass update. even two rows, the governor limit exceipt raised.
If someone can help, I will realy appreciate that. I tried Map, but same reallity.
I have learned about the system limitations, such as governor limits. Apex best practice, avoid SOQL queries or DMl code in for/while loop.
So, I try to do my best. but, I am facing a real probleme that I have to explain a litle bit. For an NGO, it is import to report monthly to the donors. It comes to my boss this idea to automatically generate indicators values as the projects progress. as a matter of fact, every single time a user insert/update/delete a record, the related indicator must be update. so far, so good. the problem comes when we consider that the organization produces a very high quantity of informations. so, it's impossible to insert then one by oneusing forms. So we need to implement mass Edit/update or import with CSV file. Which is good. There is alot of indicators, so, when an action trigger on an object, it is very important that the related indicator be created or updated for the precise month. Thus, I use the following code
A) Utility Class I use to handle some functions that I need for all trigger handlers
public class UtilitiesRecordTrack { Public static double getPercent(integer numerateur, integer denominateur) { return (double) denominateur > 0 ? (( (double) numerateur / (double) denominateur ) * 100) : 0; } public static ID getProjectID(String projectName) { ID projectId = [SELECT Id FROM Project_1__c WHERE Name = : projectName].Id; return projectId; } public static List<Indicator_1__c> getIndicator(String indicatorFullName, String projectName) { Indicator_1__c[] indicator = [SELECT Id, Indicator_Full_Name__c, Related_Project__c FROM Indicator_1__c WHERE Indicator_Full_Name__c =: indicatorFullName AND Related_Project__c =: getProjectID(projectName) ]; return indicator; } public static List<Track_Indicator__c> getTrackIndicatorId(ID indicatorId, Decimal monthNumber, ID operationId) { return [SELECT Id FROM Track_Indicator__c WHERE Indicator_Id__c = :indicatorId AND Month_Number__c = : monthNumber AND Operation_Name__c = : operationId]; } public static List<Track_Indicator__c> getTrackIndicatorId(String indicatorFullName, Decimal monthNumber, ID operationId, String projectName) { return [SELECT Id, Indicator_Id__r.Indicator_Full_Name__c, Indicator_Id__r.Related_Project__c, Indicator_Id__c FROM Track_Indicator__c WHERE Indicator_Id__r.Indicator_Full_Name__c = :indicatorFullName AND Indicator_Id__r.Related_Project__c = :[SELECT Id FROM Project_1__c WHERE Name =:projectName] AND Month_Number__c = : monthNumber AND Operation_Name__c = : operationId]; } public static void recordOrUpdate(List<Track_Indicator__c> trackToUpdate, Decimal actual, Date d, ID i, ID o) { System.debug('Function clalled'); if(trackToUpdate.size() > 0) { trackToUpdate[0].Actual__c = actual; trackToUpdate[0].Date__c = d; update trackToUpdate; System.debug('Updated'); }else{ Track_Indicator__c newTrack = new Track_Indicator__c(); newTrack.Actual__c = actual; newTrack.Date__c = d; newTrack.Indicator_Id__c = i; newTrack.Operation_Name__c = o; insert newTrack; System.debug('Inserted'); } } public static void UpdateTrack(Track_Indicator__c trackToUpdate, Decimal actual, Date d, ID o) { trackToUpdate.Actual__c = actual; trackToUpdate.Date__c = d; trackToUpdate.Operation_Name__c = o; update trackToUpdate; System.debug('Updated'); } public static void TrackCreate(Decimal actual, Date d, ID i, ID o){ Track_Indicator__c t = new Track_Indicator__c(); t.Actual__c = actual; t.Date__c = d; t.Indicator_Id__c = i; t.Operation_Name__c = o; insert t; } }
This is a trigger handler class
public class RecordTrackOnSongHelper { public static double getTotalSongWrintenByschool(Decimal month , ID operation) { return [SELECT Id FROM Song__c WHERE Month_Number__c = :month AND Operation_Name__c = :operation].size(); } public static void recordTrack(List<Song__c> newRecords, String indicatorFullName, String projectName){ if(indicatorFullName == '# of songs written by participating schools' && projectName == 'Music Heals International') { //List<Track_Indicator__c> trackToInsertCaseStudy = new List<Track_Indicator__c>() ; //List<Track_Indicator__c> trackToUpdateCaseStudy = new List<Track_Indicator__c>() ; Indicator_1__c[] indicator = UtilitiesRecordTrack.getIndicator(indicatorFullName, projectName); if(indicator.size() > 0) { Decimal actual; for(Song__c s : newRecords) { actual = (Decimal) getTotalSongWrintenByschool(s.Month_Number__c , s.Operation_Name__c); Track_Indicator__c[] tracked = UtilitiesRecordTrack.getTrackIndicatorId(indicator[0].Id, s.Month_Number__c, s.Operation_Name__c); UtilitiesRecordTrack.recordOrUpdate(tracked, actual, s.Date__c, Indicator[0].Id, s.Operation_Name__c); } } } } }
But see it for activities:
public class RecordTrackOnActivityHelper { public static double getTotalActivityOn(Decimal month, ID operation, String activityType, ID projectId) { return [SELECT Id FROM Activity_1__c WHERE Activity_Type__c = :activityType AND Project_Name__c =:projectId AND Month_Number__c =: month AND Operation_Name__c =: operation].size(); } public static double getTotalCommunityMemberSensitized(Decimal month, ID operation, String activityType, ID project) { return double.valueOf([SELECT SUM(Number_Of_Participant__c) FROM Activity_1__c WHERE Activity_Type__c = :activityType AND Month_Number__c =: month AND Project_Name__c = : project ANd Operation_Name__c =: operation][0].get('expr0')); } public static double getTotalTotalConertHeledBy(Decimal month, ID operation, String activityType, ID pr){ return [SELECT Id FROM Activity_1__c WHERE Activity_Type__c =:activityType AND Month_Number__c = :month AND Project_Name__c = : pr AND Operation_Name__c =: operation ].size(); } public static Double getTotalBoys(Decimal month, ID op, String actType, ID pr){ return double.valueOf([SELECT SUM(Number_Of_Male_Participant__c) FROM Activity_1__c WHERE Activity_Type__c = : actType AND Month_Number__c =: month AND Project_Name__c = : pr ANd Operation_Name__c =: op ][0].get('expr0')); } public static Double getTotalGirls(Decimal month, ID op, String actType, ID pr){ return double.valueOf([SELECT SUM(Number_Of_Female_Participant__c) FROM Activity_1__c WHERE Activity_Type__c = :actType AND Month_Number__c =: month AND Project_Name__c = : pr ANd Operation_Name__c =: op ][0].get('expr0')); } public static void recordTrack(List<Activity_1__c> newRecords, String indicatorFullName, String projectName){ if(indicatorFullName == 'Number extracurricular activities' && projectName == 'School Of Hope') { Indicator_1__c[] indicator = UtilitiesRecordTrack.getIndicator(indicatorFullName, projectName); if(indicator.size() > 0) { Decimal actual; for(Activity_1__c act : newRecords) { if(act.Activity_Type__c == 'Extra Curricula Activity' && act.Project_Name__c == UtilitiesRecordTrack.getProjectID(projectName)) { actual = (Decimal) getTotalActivityOn(act.Month_Number__c, act.Operation_Name__c, act.Activity_Type__c, indicator[0].Related_Project__c); Track_Indicator__c[] tracked = UtilitiesRecordTrack.getTrackIndicatorId(indicator[0].Id, act.Month_Number__c, act.Operation_Name__c); UtilitiesRecordTrack.recordOrUpdate(tracked, actual, act.End_At__c.date(), indicator[0].Id, act.Operation_Name__c); } } } } if(indicatorFullName == 'Number of meetings with parents' && projectName == 'School Of Hope') { Indicator_1__c[] indicator = UtilitiesRecordTrack.getIndicator(indicatorFullName, projectName); if(indicator.size() > 0) { Decimal actual; for(Activity_1__c act : newRecords) { if(act.Activity_Type__c == 'Meeting With Parent' && act.Project_Name__c == UtilitiesRecordTrack.getProjectID(projectName)) { actual = (Decimal) getTotalActivityOn(act.Month_Number__c, act.Operation_Name__c, act.Activity_Type__c, indicator[0].Related_Project__c); Track_Indicator__c[] tracked = UtilitiesRecordTrack.getTrackIndicatorId(indicator[0].Id, act.Month_Number__c, act.Operation_Name__c); UtilitiesRecordTrack.recordOrUpdate(tracked, actual, act.End_At__c.date(), indicator[0].Id, act.Operation_Name__c); } } } } if(indicatorFullName == '# of hygiene club meeting carried out' && projectName == 'WASH') { Indicator_1__c[] indicator = UtilitiesRecordTrack.getIndicator(indicatorFullName, projectName); if(indicator.size() > 0) { Decimal actual; for(Activity_1__c act : newRecords) { if(act.Activity_Type__c == 'hygiene club meeting carried out -(WASH)' && act.Project_Name__c == UtilitiesRecordTrack.getProjectID(projectName)) { actual = (Decimal) getTotalActivityOn(act.Month_Number__c, act.Operation_Name__c, act.Activity_Type__c, indicator[0].Related_Project__c); Track_Indicator__c[] tracked = UtilitiesRecordTrack.getTrackIndicatorId(indicator[0].Id, act.Month_Number__c, act.Operation_Name__c); UtilitiesRecordTrack.recordOrUpdate(tracked, actual, act.End_At__c.date(), indicator[0].Id, act.Operation_Name__c); } } } } if(indicatorFullName == '# of sensitization campaigns carried out' && projectName == 'WASH') { Indicator_1__c[] indicator = UtilitiesRecordTrack.getIndicator(indicatorFullName, projectName); if(indicator.size() > 0) { Decimal actual; for(Activity_1__c act : newRecords) { if(act.Activity_Type__c == 'sensitization campaigns' && act.Project_Name__c == UtilitiesRecordTrack.getProjectID(projectName)) { actual = (Decimal) getTotalActivityOn(act.Month_Number__c, act.Operation_Name__c, act.Activity_Type__c, indicator[0].Related_Project__c); Track_Indicator__c[] tracked = UtilitiesRecordTrack.getTrackIndicatorId(indicator[0].Id, act.Month_Number__c, act.Operation_Name__c); UtilitiesRecordTrack.recordOrUpdate(tracked, actual, act.End_At__c.date(), indicator[0].Id, act.Operation_Name__c); } } } } }
Trigger:
trigger RecordRelatedTrackIndicatorOnActivity on Activity_1__c (after insert, after update) { if(Trigger.isInsert) { RecordTrackOnActivityHelper.recordTrack(Trigger.New, 'Number of meetings with parents', 'School Of Hope'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, 'Numbers of training sessions held', 'School Of Hope'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of training session conducted for volunteers', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of people sensitized on the importance of after-school activities', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of boys participating in after-school activities', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of girls participating in after-school activities', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of girls reading at the library', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of boys reading at the library', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of male adolescents attending training on sexual health and risk behavior linked to their age', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of female adolescents attending training on sexual health and risks behavior linked to their age', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of youth participating in debate sessions', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of community leaders and members participating in Koze Lakay', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of community leaders and members participating in other community gatherings', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of community activities conducted by adolescents', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of community activities conducted by kids', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, 'Number extracurricular activities', 'School Of Hope'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of hygiene club meeting carried out', 'WASH'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of sensitization campaigns carried out', 'WASH'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of parent meetings held on WASH issues', 'WASH'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of Delmas 32 community members sensitized on WASH issues', 'WASH'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of concerts held by Little Kids Band', 'Music Heals International'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of concerts held by Little Kids Band with special guest musicians', 'Music Heals International'); } if(Trigger.isUpdate) { RecordTrackOnActivityHelper.recordTrack(Trigger.New, 'Number of meetings with parents', 'School Of Hope'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, 'Numbers of training sessions held', 'School Of Hope'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of training session conducted for volunteers', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of people sensitized on the importance of after-school activities', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of boys participating in after-school activities', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of girls participating in after-school activities', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of girls reading at the library', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of boys reading at the library', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of male adolescents attending training on sexual health and risk behavior linked to their age', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of female adolescents attending training on sexual health and risks behavior linked to their age', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of youth participating in debate sessions', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of community leaders and members participating in Koze Lakay', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of community leaders and members participating in other community gatherings', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of community activities conducted by adolescents', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of community activities conducted by kids', 'Comunity Development Campus'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, 'Number extracurricular activities', 'School Of Hope'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of hygiene club meeting carried out', 'WASH'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of sensitization campaigns carried out', 'WASH'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of parent meetings held on WASH issues', 'WASH'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of Delmas 32 community members sensitized on WASH issues', 'WASH'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of concerts held by Little Kids Band', 'Music Heals International'); RecordTrackOnActivityHelper.recordTrack(Trigger.New, '# of concerts held by Little Kids Band with special guest musicians', 'Music Heals International'); } }
magine these functions being calling for every row in a CSV file or comming from mass update. even two rows, the governor limit exceipt raised.
If someone can help, I will realy appreciate that. I tried Map, but same reallity.
<pre>
public class UtilitiesRecordTrack
{
public static Double getPercent( Integer numerateur, Integer denominateur )
{
return (Double) denominateur > 0 ? (((Double) numerateur / (Double) denominateur) * 100) : 0;
}
private static Map<String,Id> projectIdsByName
{
get
{
if ( projectIdsByName == null )
{
projectIdsByName = new Map<String,Id>();
for ( Project_1__c project : [SELECT Id, Name FROM Project_1__c] )
{
projectIdsByName.put( project.Name, project.Id );
}
}
return projectIdsByName;
}
private set;
}
public static Id getProjectID( String projectName )
{
return projectIdsByName.get( projectName );
}
private static Map<String,List<Indicator_1__c>> indicatorsByNameAndProject
{
get
{
if ( indicatorsByNameAndProject == null )
{
indicatorsByNameAndProject = new Map<String,List<Indicator_1__c>>();
for ( Indicator_1__c indicator :
[ SELECT Id, Indicator_Full_Name__c, Related_Project__c
FROM Indicator_1__c
]
)
{
String combinedName = indicator.Indicator_Full_Name__c + '|' + indicator.Related_Project__c;
if ( !indicatorsByNameAndProject.containsKey( combinedName ) )
{
indicatorsByNameAndProject.put( combinedName, new List<Indicator_1__c> );
}
indicatorsByNameAndProject.get( combinedName ).add( indicator );
}
}
return indicatorsByNameAndProject;
}
private set;
}
public static List<Indicator_1__c> getIndicator( String indicatorFullName, String projectName )
{
String combinedName = indicatorFullName + '|' + getProjectID( projectName );
List<Indicator_1__c> indicators = indicatorsByNameAndProject( combinedName );
return indicators != null ? indicators : new List<Indicator_1__c>();
}
private static Map<Decimal,Map<Id,Map<Id,List<Track_Indicator__c>>>> trackIndicatorsByMonth = Map<Decimal,Map<Id,Map<Id,List<Track_Indicator__c>>>>();
public static void prefetchTrackIndicators( Decimal month )
{
if ( trackIndicatorsByMonth.containsKey( month ) ) return;
trackIndicatorsByMonth.put( month, new Map<Id,Map<Id,List<Track_Indicator__c>>>() );
Map<Id,Map<Id,List<Track_Indicator__c>>> trackIndicatorsByIndicatorId = resultsByMonth.get( month );
for ( Track_Indicator__c trackIndicator :
[ SELECT Id, Indicator_Id__c, Operation_Name__c
FROM Track_Indicator__c
WHERE Month_Number__c = :month
]
)
{
Id indicatorId = trackIndicator.Indicator_Id__c;
Id operation = trackIndicator.Operation_Name__c;
if ( !trackIndicatorsByIndicatorId.containsKey( indicatorId ) )
{
trackIndicatorsByIndicatorId.put( indicatorId, new Map<Id,List<Track_Indicator__c>>() );
}
Map<Id,List<Track_Indicator__c>> trackIndicatorsByOperation = trackIndicatorsByIndicatorId.get( indicatorId );
if ( !trackIndicatorsByOperation.containsKey( operation ) )
{
trackIndicatorsByOperation.put( operation, new List<Track_Indicator__c>() );
}
List<Track_Indicator__c> trackIndicators = trackIndicatorsByOperation.get( operation );
trackIndicators.add( trackIndicator );
}
}
public static List<Track_Indicator__c> getTrackIndicatorId( Id indicatorId, Decimal monthNumber, Id operationId )
{
prefetchTrackIndicators( monthNumber );
Map<Id,Map<Id,List<Track_Indicator__c>>> trackIndicatorsByIndicatorId = trackIndicatorsByMonth.get( monthNumber );
if ( trackIndicatorsByIndicatorId == null ) return new List<Track_Indicator__c>();
Map<Id,List<Track_Indicator__c>> trackIndicatorsByOperation = trackIndicatorsByIndicatorId.get( indicatorId );
if ( trackIndicatorsByOperation == null ) return new List<Track_Indicator__c>();
List<Track_Indicator__c> trackIndicators = trackIndicatorsByOperation.get( operationId );
if ( trackIndicators == null ) return new List<Track_Indicator__c>();
return trackIndicators;
}
public static List<Track_Indicator__c> getTrackIndicatorId( String indicatorFullName, Decimal monthNumber, Id operationId, String projectName )
{
List<Track_Indicator__c> allTrackIndicators = List<Track_Indicator__c>();
for ( Indicator_1__c indicator : getIndicator( indicatorFullName, projectName ) )
{
allTrackIndicators.addAll( getTrackIndicatorId( indicator.Id, monthNumber, operationId ) );
}
return allTrackIndicators;
}
public static Track_Indicator__c recordOrUpdate( List<Track_Indicator__c> trackToUpdate, Decimal actual, Date d, Id i, Id o )
{
if ( !trackToUpdate.isEmpty() )
{
trackToUpdate[0].Actual__c = actual;
trackToUpdate[0].Date__c = d;
return trackToUpdate[0];
}
return TrackCreate( actual, d, i, o )
}
public static void UpdateTrack( Track_Indicator__c trackToUpdate, Decimal actual, Date d, Id o )
{
trackToUpdate.Actual__c = actual;
trackToUpdate.Date__c = d;
trackToUpdate.Operation_Name__c = o;
update trackToUpdate;
}
public static Track_Indicator__c TrackCreate( Decimal actual, Date d, Id i, Id o )
{
return new Track_Indicator__c
( Actual__c = actual
, Date__c = d
, Indicator_Id__c = i
, Operation_Name__c = o
);
}
}
/**************************************/
public class RecordTrackOnSongHelper
{
private Map<Decimal,Map<Id,AggregateResult>> resultsByMonth = Map<Decimal,Map<Id,AggregateResult>>();
public static void prefetchResults( Decimal month )
{
if ( resultsByMonth.containsKey( month ) ) return;
resultsByMonth.put( month, new Map<Id,AggregateResult>() );
Map<Id,AggregateResult> resultsByOperation = resultsByMonth.get( month );
for ( AggregateResult result :
[ SELECT COUNT(Id) numRecords,
Operation_Name__c
FROM Song__c
WHERE Month_Number__c = :month
GROUP BY Operation_Name__c
]
)
{
Id operation = (Id) result.get( 'Operation_Name__c' );
resultsByOperation.put( operation, result );
}
}
public static double getTotalSongWrintenByschool( Decimal month, Id operation )
{
prefetchResults( month );
Map<Id,AggregateResult> resultsByOperation = resultsByMonth.get( month );
if ( resultsByOperation == null ) return 0;
AggregateResult result = resultsByOperation.get( operation );
if ( result == null ) return 0;
return Double.valueOf( result.get( 'numRecords' ) );
}
public static void recordTrack( List<Song__c> newRecords, String indicatorFullName, String projectName )
{
if ( indicatorFullName != '# of songs written by participating schools'
|| projectName != 'Music Heals International' ) return;
List<Indicator_1__c> indicator = UtilitiesRecordTrack.getIndicator( indicatorFullName, projectName );
if ( indicator.isEmpty() ) return;
List<Track_Indicator__c> trackIndicatorsToUpsert = new List<Track_Indicator__c>();
for ( Song__c s : newRecords )
{
Decimal actual = (Decimal) getTotalSongWrintenByschool( s.Month_Number__c , s.Operation_Name__c );
List<Track_Indicator__c> tracked = UtilitiesRecordTrack.getTrackIndicatorId( indicator[0].Id, s.Month_Number__c, s.Operation_Name__c );
trackIndicatorsToUpsert.add( UtilitiesRecordTrack.recordOrUpdate( tracked, actual, s.Date__c, Indicator[0].Id, s.Operation_Name__c ) );
}
upsert trackIndicatorsToUpsert;
}
}
/**************************************/
public class RecordTrackOnActivityHelper
{
private static Map<Decimal,Map<String,Map<Id,Map<Id,AggregateResult>>>> resultsByMonth = Map<Decimal,Map<String,Map<Id,Map<Id,AggregateResult>>>>();
public static void prefetchResults( Decimal month )
{
if ( resultsByMonth.containsKey( month ) ) return;
resultsByMonth.put( month, new Map<String,Map<Id,Map<Id,AggregateResult>>>() );
Map<String,Map<Id,Map<Id,AggregateResult>>> resultsByActivityType = resultsByMonth.get( month );
for ( AggregateResult result :
[ SELECT COUNT(Id) numRecords,
SUM(Number_Of_Participant__c) numParticipants,
SUM(Number_Of_Male_Participant__c), numMales,
SUM(Number_Of_Female_Participant__c) numFemales,
Activity_Type__c, Project_Name__c, Operation_Name__c
FROM Activity_1__c
WHERE Month_Number__c = :month
GROUP BY Activity_Type__c, Project_Name__c, Operation_Name__c
]
)
{
String activityType = (String) result.get( 'Activity_Type__c' );
Id projectId = (Id) result.get( 'Project_Name__c' );
Id operation = (Id) result.get( 'Operation_Name__c' );
if ( !resultsByActivityType.containsKey( activityType ) )
{
resultsByActivityType.put( activityType, new Map<Id,Map<Id,AggregateResult>>() );
}
Map<Id,Map<Id,AggregateResult>> resultsByProjectId = resultsByActivityType.get( activityType );
if ( !resultsByProjectId.containsKey( projectId ) )
{
resultsByProjectId.put( projectId, new Map<Id,AggregateResult>() );
}
Map<Id,AggregateResult> resultsByOperation = resultsByProjectId.get( projectId );
resultsByOperation.put( operation, result );
}
}
private Double getResult( Decimal month, Id operation, String activityType, Id projectId, String alias )
{
prefetchResults( month );
Map<String,Map<Id,Map<Id,AggregateResult>>> resultsByActivityType = resultsByMonth.get( month );
if ( resultsByActivityType == null ) return 0;
Map<Id,Map<Id,AggregateResult>> resultsByProjectId = resultsByActivityType.get( activityType );
if ( resultsByProjectId == null ) return 0;
Map<Id,AggregateResult> resultsByOperation = resultsByProjectId.get( projectId );
if ( resultsByOperation == null ) return 0;
AggregateResult result = resultsByOperation.get( operation );
if ( result == null ) return 0;
return Double.valueOf( result.get( alias ) );
}
public static Double getTotalActivityOn( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numRecords' );
}
public static Double getTotalCommunityMemberSensitized( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numParticipants' );
}
public static Double getTotalTotalConertHeledBy( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numRecords' );
}
public static Double getTotalBoys( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numMales' );
}
public static Double getTotalGirls( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numFemales' );
}
private static Map<String,String> activityTypesByIndicator = new Map<String,String>
{ 'School Of Hope|Number extracurricular activities' => 'Extra Curricula Activity'
, 'School Of Hope|Number of meetings with parents' => 'Meeting With Parent'
, 'WASH|# of hygiene club meeting carried out' => 'hygiene club meeting carried out -(WASH)'
, 'WASH|# of sensitization campaigns carried out' => 'sensitization campaigns'
};
public static void recordTrack( List<Activity_1__c> newRecords, String indicatorFullName, String projectName )
{
String activityType = activityTypesByIndicator.get( projectName + '|' + indicatorFullName );
if ( activityType == null ) return;
List<Indicator_1__c> indicator = UtilitiesRecordTrack.getIndicator( indicatorFullName, projectName );
if ( indicator.isEmpty() ) return;
List<Track_Indicator__c> trackIndicatorsToUpsert = new List<Track_Indicator__c>();
for ( Activity_1__c act : newRecords )
{
if ( act.Activity_Type__c != activityType
|| act.Project_Name__c != UtilitiesRecordTrack.getProjectID( projectName )
) continue;
Decimal actual = (Decimal) getTotalActivityOn( act.Month_Number__c, act.Operation_Name__c, act.Activity_Type__c, indicator[0].Related_Project__c );
Track_Indicator__c[] tracked = UtilitiesRecordTrack.getTrackIndicatorId( indicator[0].Id, act.Month_Number__c, act.Operation_Name__c );
trackIndicatorsToUpsert.add( UtilitiesRecordTrack.recordOrUpdate( tracked, actual, act.End_At__c.date(), indicator[0].Id, act.Operation_Name__c ) );
}
upsert trackIndicatorsToUpsert;
}
}
/**************************************/
trigger RecordRelatedTrackIndicatorOnActivity on Activity_1__c ( after insert, after update )
{
Map<String,List<String>> indicatorsByProject = new Map<String,List<String>>
{ 'School Of Hope' => new List<String>
{ 'Number of meetings with parents'
, 'Numbers of training sessions held'
}
, 'Comunity Development Campus' => new List<String>
{ '# of training session conducted for volunteers'
, '# of people sensitized on the importance of after-school activities'
, '# of boys participating in after-school activities'
, '# of girls participating in after-school activities
, '# of girls reading at the library'
, '# of boys reading at the library'
, '# of male adolescents attending training on sexual health and risk behavior linked to their age'
, '# of female adolescents attending training on sexual health and risks behavior linked to their age'
, '# of youth participating in debate sessions'
, '# of community leaders and members participating in Koze Lakay'
, '# of community leaders and members participating in other community gatherings'
, '# of community activities conducted by adolescents'
, '# of community activities conducted by kids'
}
, 'School Of Hope' => new List<String>
{ 'Number extracurricular activities'
}
, 'WASH' => new List<String>
{ '# of hygiene club meeting carried out'
, '# of sensitization campaigns carried out'
, '# of parent meetings held on WASH issues'
, '# of Delmas 32 community members sensitized on WASH issues'
}
, 'Music Heals International' => new List<String>
{ '# of concerts held by Little Kids Band'
, '# of concerts held by Little Kids Band with special guest musicians'
}
};
for ( String project : indicatorsByProject.keySet() )
{
for ( String indicator : indicatorsByProject.get( project ) )
{
RecordTrackOnActivityHelper.recordTrack( Trigger.new, indicator, project );
}
}
}
</pre>
All Answers
My syntax and such is likely wrong as I'm not very experienced but I think that shows the general idea.
Thank you for your prompt answer. But il will not help solving the probleme. because, between the criteria for the queries, I have to pass the month and the operation so that the track indicator related be updated. because we must report for each indicator at the end of each month as projectprogress. it's beacuse, I have to pick the good Track if exist and update it, if it not exist, create it.
Your code should be able to handle 200 records at a time without exceeding the 101 SOQL query limit. If you've got 2 queries you won't be able to pass more than 50 records. An insert trigger may also result in the update trigger being called depending on workflow or what else is triggered on that object.
<pre>
public class UtilitiesRecordTrack
{
public static Double getPercent( Integer numerateur, Integer denominateur )
{
return (Double) denominateur > 0 ? (((Double) numerateur / (Double) denominateur) * 100) : 0;
}
private static Map<String,Id> projectIdsByName
{
get
{
if ( projectIdsByName == null )
{
projectIdsByName = new Map<String,Id>();
for ( Project_1__c project : [SELECT Id, Name FROM Project_1__c] )
{
projectIdsByName.put( project.Name, project.Id );
}
}
return projectIdsByName;
}
private set;
}
public static Id getProjectID( String projectName )
{
return projectIdsByName.get( projectName );
}
private static Map<String,List<Indicator_1__c>> indicatorsByNameAndProject
{
get
{
if ( indicatorsByNameAndProject == null )
{
indicatorsByNameAndProject = new Map<String,List<Indicator_1__c>>();
for ( Indicator_1__c indicator :
[ SELECT Id, Indicator_Full_Name__c, Related_Project__c
FROM Indicator_1__c
]
)
{
String combinedName = indicator.Indicator_Full_Name__c + '|' + indicator.Related_Project__c;
if ( !indicatorsByNameAndProject.containsKey( combinedName ) )
{
indicatorsByNameAndProject.put( combinedName, new List<Indicator_1__c> );
}
indicatorsByNameAndProject.get( combinedName ).add( indicator );
}
}
return indicatorsByNameAndProject;
}
private set;
}
public static List<Indicator_1__c> getIndicator( String indicatorFullName, String projectName )
{
String combinedName = indicatorFullName + '|' + getProjectID( projectName );
List<Indicator_1__c> indicators = indicatorsByNameAndProject( combinedName );
return indicators != null ? indicators : new List<Indicator_1__c>();
}
private static Map<Decimal,Map<Id,Map<Id,List<Track_Indicator__c>>>> trackIndicatorsByMonth = Map<Decimal,Map<Id,Map<Id,List<Track_Indicator__c>>>>();
public static void prefetchTrackIndicators( Decimal month )
{
if ( trackIndicatorsByMonth.containsKey( month ) ) return;
trackIndicatorsByMonth.put( month, new Map<Id,Map<Id,List<Track_Indicator__c>>>() );
Map<Id,Map<Id,List<Track_Indicator__c>>> trackIndicatorsByIndicatorId = resultsByMonth.get( month );
for ( Track_Indicator__c trackIndicator :
[ SELECT Id, Indicator_Id__c, Operation_Name__c
FROM Track_Indicator__c
WHERE Month_Number__c = :month
]
)
{
Id indicatorId = trackIndicator.Indicator_Id__c;
Id operation = trackIndicator.Operation_Name__c;
if ( !trackIndicatorsByIndicatorId.containsKey( indicatorId ) )
{
trackIndicatorsByIndicatorId.put( indicatorId, new Map<Id,List<Track_Indicator__c>>() );
}
Map<Id,List<Track_Indicator__c>> trackIndicatorsByOperation = trackIndicatorsByIndicatorId.get( indicatorId );
if ( !trackIndicatorsByOperation.containsKey( operation ) )
{
trackIndicatorsByOperation.put( operation, new List<Track_Indicator__c>() );
}
List<Track_Indicator__c> trackIndicators = trackIndicatorsByOperation.get( operation );
trackIndicators.add( trackIndicator );
}
}
public static List<Track_Indicator__c> getTrackIndicatorId( Id indicatorId, Decimal monthNumber, Id operationId )
{
prefetchTrackIndicators( monthNumber );
Map<Id,Map<Id,List<Track_Indicator__c>>> trackIndicatorsByIndicatorId = trackIndicatorsByMonth.get( monthNumber );
if ( trackIndicatorsByIndicatorId == null ) return new List<Track_Indicator__c>();
Map<Id,List<Track_Indicator__c>> trackIndicatorsByOperation = trackIndicatorsByIndicatorId.get( indicatorId );
if ( trackIndicatorsByOperation == null ) return new List<Track_Indicator__c>();
List<Track_Indicator__c> trackIndicators = trackIndicatorsByOperation.get( operationId );
if ( trackIndicators == null ) return new List<Track_Indicator__c>();
return trackIndicators;
}
public static List<Track_Indicator__c> getTrackIndicatorId( String indicatorFullName, Decimal monthNumber, Id operationId, String projectName )
{
List<Track_Indicator__c> allTrackIndicators = List<Track_Indicator__c>();
for ( Indicator_1__c indicator : getIndicator( indicatorFullName, projectName ) )
{
allTrackIndicators.addAll( getTrackIndicatorId( indicator.Id, monthNumber, operationId ) );
}
return allTrackIndicators;
}
public static Track_Indicator__c recordOrUpdate( List<Track_Indicator__c> trackToUpdate, Decimal actual, Date d, Id i, Id o )
{
if ( !trackToUpdate.isEmpty() )
{
trackToUpdate[0].Actual__c = actual;
trackToUpdate[0].Date__c = d;
return trackToUpdate[0];
}
return TrackCreate( actual, d, i, o )
}
public static void UpdateTrack( Track_Indicator__c trackToUpdate, Decimal actual, Date d, Id o )
{
trackToUpdate.Actual__c = actual;
trackToUpdate.Date__c = d;
trackToUpdate.Operation_Name__c = o;
update trackToUpdate;
}
public static Track_Indicator__c TrackCreate( Decimal actual, Date d, Id i, Id o )
{
return new Track_Indicator__c
( Actual__c = actual
, Date__c = d
, Indicator_Id__c = i
, Operation_Name__c = o
);
}
}
/**************************************/
public class RecordTrackOnSongHelper
{
private Map<Decimal,Map<Id,AggregateResult>> resultsByMonth = Map<Decimal,Map<Id,AggregateResult>>();
public static void prefetchResults( Decimal month )
{
if ( resultsByMonth.containsKey( month ) ) return;
resultsByMonth.put( month, new Map<Id,AggregateResult>() );
Map<Id,AggregateResult> resultsByOperation = resultsByMonth.get( month );
for ( AggregateResult result :
[ SELECT COUNT(Id) numRecords,
Operation_Name__c
FROM Song__c
WHERE Month_Number__c = :month
GROUP BY Operation_Name__c
]
)
{
Id operation = (Id) result.get( 'Operation_Name__c' );
resultsByOperation.put( operation, result );
}
}
public static double getTotalSongWrintenByschool( Decimal month, Id operation )
{
prefetchResults( month );
Map<Id,AggregateResult> resultsByOperation = resultsByMonth.get( month );
if ( resultsByOperation == null ) return 0;
AggregateResult result = resultsByOperation.get( operation );
if ( result == null ) return 0;
return Double.valueOf( result.get( 'numRecords' ) );
}
public static void recordTrack( List<Song__c> newRecords, String indicatorFullName, String projectName )
{
if ( indicatorFullName != '# of songs written by participating schools'
|| projectName != 'Music Heals International' ) return;
List<Indicator_1__c> indicator = UtilitiesRecordTrack.getIndicator( indicatorFullName, projectName );
if ( indicator.isEmpty() ) return;
List<Track_Indicator__c> trackIndicatorsToUpsert = new List<Track_Indicator__c>();
for ( Song__c s : newRecords )
{
Decimal actual = (Decimal) getTotalSongWrintenByschool( s.Month_Number__c , s.Operation_Name__c );
List<Track_Indicator__c> tracked = UtilitiesRecordTrack.getTrackIndicatorId( indicator[0].Id, s.Month_Number__c, s.Operation_Name__c );
trackIndicatorsToUpsert.add( UtilitiesRecordTrack.recordOrUpdate( tracked, actual, s.Date__c, Indicator[0].Id, s.Operation_Name__c ) );
}
upsert trackIndicatorsToUpsert;
}
}
/**************************************/
public class RecordTrackOnActivityHelper
{
private static Map<Decimal,Map<String,Map<Id,Map<Id,AggregateResult>>>> resultsByMonth = Map<Decimal,Map<String,Map<Id,Map<Id,AggregateResult>>>>();
public static void prefetchResults( Decimal month )
{
if ( resultsByMonth.containsKey( month ) ) return;
resultsByMonth.put( month, new Map<String,Map<Id,Map<Id,AggregateResult>>>() );
Map<String,Map<Id,Map<Id,AggregateResult>>> resultsByActivityType = resultsByMonth.get( month );
for ( AggregateResult result :
[ SELECT COUNT(Id) numRecords,
SUM(Number_Of_Participant__c) numParticipants,
SUM(Number_Of_Male_Participant__c), numMales,
SUM(Number_Of_Female_Participant__c) numFemales,
Activity_Type__c, Project_Name__c, Operation_Name__c
FROM Activity_1__c
WHERE Month_Number__c = :month
GROUP BY Activity_Type__c, Project_Name__c, Operation_Name__c
]
)
{
String activityType = (String) result.get( 'Activity_Type__c' );
Id projectId = (Id) result.get( 'Project_Name__c' );
Id operation = (Id) result.get( 'Operation_Name__c' );
if ( !resultsByActivityType.containsKey( activityType ) )
{
resultsByActivityType.put( activityType, new Map<Id,Map<Id,AggregateResult>>() );
}
Map<Id,Map<Id,AggregateResult>> resultsByProjectId = resultsByActivityType.get( activityType );
if ( !resultsByProjectId.containsKey( projectId ) )
{
resultsByProjectId.put( projectId, new Map<Id,AggregateResult>() );
}
Map<Id,AggregateResult> resultsByOperation = resultsByProjectId.get( projectId );
resultsByOperation.put( operation, result );
}
}
private Double getResult( Decimal month, Id operation, String activityType, Id projectId, String alias )
{
prefetchResults( month );
Map<String,Map<Id,Map<Id,AggregateResult>>> resultsByActivityType = resultsByMonth.get( month );
if ( resultsByActivityType == null ) return 0;
Map<Id,Map<Id,AggregateResult>> resultsByProjectId = resultsByActivityType.get( activityType );
if ( resultsByProjectId == null ) return 0;
Map<Id,AggregateResult> resultsByOperation = resultsByProjectId.get( projectId );
if ( resultsByOperation == null ) return 0;
AggregateResult result = resultsByOperation.get( operation );
if ( result == null ) return 0;
return Double.valueOf( result.get( alias ) );
}
public static Double getTotalActivityOn( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numRecords' );
}
public static Double getTotalCommunityMemberSensitized( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numParticipants' );
}
public static Double getTotalTotalConertHeledBy( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numRecords' );
}
public static Double getTotalBoys( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numMales' );
}
public static Double getTotalGirls( Decimal month, Id operation, String activityType, Id projectId )
{
return getResult( month, operation, activityType, projectId, 'numFemales' );
}
private static Map<String,String> activityTypesByIndicator = new Map<String,String>
{ 'School Of Hope|Number extracurricular activities' => 'Extra Curricula Activity'
, 'School Of Hope|Number of meetings with parents' => 'Meeting With Parent'
, 'WASH|# of hygiene club meeting carried out' => 'hygiene club meeting carried out -(WASH)'
, 'WASH|# of sensitization campaigns carried out' => 'sensitization campaigns'
};
public static void recordTrack( List<Activity_1__c> newRecords, String indicatorFullName, String projectName )
{
String activityType = activityTypesByIndicator.get( projectName + '|' + indicatorFullName );
if ( activityType == null ) return;
List<Indicator_1__c> indicator = UtilitiesRecordTrack.getIndicator( indicatorFullName, projectName );
if ( indicator.isEmpty() ) return;
List<Track_Indicator__c> trackIndicatorsToUpsert = new List<Track_Indicator__c>();
for ( Activity_1__c act : newRecords )
{
if ( act.Activity_Type__c != activityType
|| act.Project_Name__c != UtilitiesRecordTrack.getProjectID( projectName )
) continue;
Decimal actual = (Decimal) getTotalActivityOn( act.Month_Number__c, act.Operation_Name__c, act.Activity_Type__c, indicator[0].Related_Project__c );
Track_Indicator__c[] tracked = UtilitiesRecordTrack.getTrackIndicatorId( indicator[0].Id, act.Month_Number__c, act.Operation_Name__c );
trackIndicatorsToUpsert.add( UtilitiesRecordTrack.recordOrUpdate( tracked, actual, act.End_At__c.date(), indicator[0].Id, act.Operation_Name__c ) );
}
upsert trackIndicatorsToUpsert;
}
}
/**************************************/
trigger RecordRelatedTrackIndicatorOnActivity on Activity_1__c ( after insert, after update )
{
Map<String,List<String>> indicatorsByProject = new Map<String,List<String>>
{ 'School Of Hope' => new List<String>
{ 'Number of meetings with parents'
, 'Numbers of training sessions held'
}
, 'Comunity Development Campus' => new List<String>
{ '# of training session conducted for volunteers'
, '# of people sensitized on the importance of after-school activities'
, '# of boys participating in after-school activities'
, '# of girls participating in after-school activities
, '# of girls reading at the library'
, '# of boys reading at the library'
, '# of male adolescents attending training on sexual health and risk behavior linked to their age'
, '# of female adolescents attending training on sexual health and risks behavior linked to their age'
, '# of youth participating in debate sessions'
, '# of community leaders and members participating in Koze Lakay'
, '# of community leaders and members participating in other community gatherings'
, '# of community activities conducted by adolescents'
, '# of community activities conducted by kids'
}
, 'School Of Hope' => new List<String>
{ 'Number extracurricular activities'
}
, 'WASH' => new List<String>
{ '# of hygiene club meeting carried out'
, '# of sensitization campaigns carried out'
, '# of parent meetings held on WASH issues'
, '# of Delmas 32 community members sensitized on WASH issues'
}
, 'Music Heals International' => new List<String>
{ '# of concerts held by Little Kids Band'
, '# of concerts held by Little Kids Band with special guest musicians'
}
};
for ( String project : indicatorsByProject.keySet() )
{
for ( String indicator : indicatorsByProject.get( project ) )
{
RecordTrackOnActivityHelper.recordTrack( Trigger.new, indicator, project );
}
}
}
</pre>
Thank you so much for your answer, well done.