You need to sign in to do that
Don't have an account?

Apex code to update a custom field with the sum of value in a related custom field of a different object
Hi there,
I am working on a code to sum up value in a custom field 'Total_Months_in_this_Position__c' of a custom object
'TargetX_SRMb__Family_Relationship__c' and add the sum to a custom field 'Total_Months_Employed__c' of a related custom object 'TargetX_SRMb__Application__c'.
One 'TargetX_SRMb__Application__c' can have multiple TargetX_SRMb__Family_Relationship__c. The relationship is a lookup.
Below is the code I wrote, I am having a hard time fixing the error on the last line.
Any help will be very much appreciated.
trigger CalcTotalMonths on TargetX_SRMb__Family_Relationship__c (After insert, After Update) {
// gets a set of all application ids and application name with relationship
Set<Id>applicationIds = new Set<Id>();
Map<Id,TargetX_SRMb__Application__c>appToUpdate =new Map<Id,TargetX_SRMb__Application__c>();
Map<decimal,decimal>monthsWorked = new Map<decimal,decimal>();
// collect the application ids of all new relationships with employer name
if(trigger.isInsert){
for (TargetX_SRMb__Family_Relationship__c Rel : Trigger.new)
if(!string.isblank(Rel.TargetX_SRMb__Employer__c) && !string.isBlank(Rel.TargetX_SRMb__Contact__c)
&& (Rel.TargetX_SRMb__Relationship__c == 'Current Employer' || Rel.TargetX_SRMb__Relationship__c == 'Previous Employer'))
{applicationIds.add(Rel.TargetX_SRMb__Application__c);
}
// collect the application ids of all updated relationships with employer name
if(Trigger.isUpdate){
for(TargetX_SRMb__Family_Relationship__c Rel : Trigger.new)
{
if((Trigger.oldMap.get(Rel.id).Total_Months_in_this_Position__c != Trigger.newMap.get(Rel.id).Total_Months_in_this_Position__c)
&& !string.isBlank(Rel.TargetX_SRMb__Contact__c)
&& (Rel.TargetX_SRMb__Relationship__c == 'Current Employer' || Rel.TargetX_SRMb__Relationship__c == 'Previous Employer') )
{
Applicationids.add(Rel.TargetX_SRMb__Application__c);
}
}
// Use aggregate query to get the total months worked for each relationship
for(AggregateResult result :[Select Count(id) Total,SUM(Total_Months_in_this_Position__c) Months , TargetX_SRMb__Application__c FROM TargetX_SRMb__Family_Relationship__c
WHERE TargetX_SRMb__Application__c IN :Applicationids GROUP BY TargetX_SRMb__Application__c
])
{ Decimal Months = (Decimal)result.get('Months');
Decimal Total = (Decimal)result.get('Total');
System.debug('Total Months Worked'+ 'Total_Months_in_this_Position__c' );
Decimal field = monthsWorked.get(Months);
if ( field != null)
{
TargetX_SRMb__Application__c app = appToUpdate.get((id)result.get('TargetX_SRMb__Application__c'));
app.Total_Months_Employed__c = field;
app.put(Total,field);
}
}
}
}
}
Thanks,
Mariam
I am working on a code to sum up value in a custom field 'Total_Months_in_this_Position__c' of a custom object
'TargetX_SRMb__Family_Relationship__c' and add the sum to a custom field 'Total_Months_Employed__c' of a related custom object 'TargetX_SRMb__Application__c'.
One 'TargetX_SRMb__Application__c' can have multiple TargetX_SRMb__Family_Relationship__c. The relationship is a lookup.
Below is the code I wrote, I am having a hard time fixing the error on the last line.
Any help will be very much appreciated.
trigger CalcTotalMonths on TargetX_SRMb__Family_Relationship__c (After insert, After Update) {
// gets a set of all application ids and application name with relationship
Set<Id>applicationIds = new Set<Id>();
Map<Id,TargetX_SRMb__Application__c>appToUpdate =new Map<Id,TargetX_SRMb__Application__c>();
Map<decimal,decimal>monthsWorked = new Map<decimal,decimal>();
// collect the application ids of all new relationships with employer name
if(trigger.isInsert){
for (TargetX_SRMb__Family_Relationship__c Rel : Trigger.new)
if(!string.isblank(Rel.TargetX_SRMb__Employer__c) && !string.isBlank(Rel.TargetX_SRMb__Contact__c)
&& (Rel.TargetX_SRMb__Relationship__c == 'Current Employer' || Rel.TargetX_SRMb__Relationship__c == 'Previous Employer'))
{applicationIds.add(Rel.TargetX_SRMb__Application__c);
}
// collect the application ids of all updated relationships with employer name
if(Trigger.isUpdate){
for(TargetX_SRMb__Family_Relationship__c Rel : Trigger.new)
{
if((Trigger.oldMap.get(Rel.id).Total_Months_in_this_Position__c != Trigger.newMap.get(Rel.id).Total_Months_in_this_Position__c)
&& !string.isBlank(Rel.TargetX_SRMb__Contact__c)
&& (Rel.TargetX_SRMb__Relationship__c == 'Current Employer' || Rel.TargetX_SRMb__Relationship__c == 'Previous Employer') )
{
Applicationids.add(Rel.TargetX_SRMb__Application__c);
}
}
// Use aggregate query to get the total months worked for each relationship
for(AggregateResult result :[Select Count(id) Total,SUM(Total_Months_in_this_Position__c) Months , TargetX_SRMb__Application__c FROM TargetX_SRMb__Family_Relationship__c
WHERE TargetX_SRMb__Application__c IN :Applicationids GROUP BY TargetX_SRMb__Application__c
])
{ Decimal Months = (Decimal)result.get('Months');
Decimal Total = (Decimal)result.get('Total');
System.debug('Total Months Worked'+ 'Total_Months_in_this_Position__c' );
Decimal field = monthsWorked.get(Months);
if ( field != null)
{
TargetX_SRMb__Application__c app = appToUpdate.get((id)result.get('TargetX_SRMb__Application__c'));
app.Total_Months_Employed__c = field;
app.put(Total,field);
}
}
}
}
}
Thanks,
Mariam
There are some issues in your code. There is no DML in your code which will not update the Parent Object.
Can you please refer the below code where i have used Account and Contact Object.
Object: Account, Custom Field: Acc_Cost__c
Object: Contact, Custom Field: Contact_Cost__c
Change the Custom Field and Object name according to your requirement.
Changes for you will be like:
* Contact Object to TargetX_SRMb__Family_Relationship__c Custom Object
* Contact_Cost__c of Contact Object to Total_Months_in_this_Position__c of TargetX_SRMb__Family_Relationship__c Object
* Account Object to TargetX_SRMb__Application__c Custom Object
* Acc_Cost__c of Account Object to Total_Months_Employed__c of TargetX_SRMb__Application__c Object
Please try to implement the same with the changes and let me know if it works.
Please mark this as the solution if it solved your purpose to help the Community grows.
Thanks,
Jainam Contractor,
Salesforce Consultant,
Varasi LLC
www.varasi.com
All Answers
Thanks
There are some issues in your code. There is no DML in your code which will not update the Parent Object.
Can you please refer the below code where i have used Account and Contact Object.
Object: Account, Custom Field: Acc_Cost__c
Object: Contact, Custom Field: Contact_Cost__c
Change the Custom Field and Object name according to your requirement.
Changes for you will be like:
* Contact Object to TargetX_SRMb__Family_Relationship__c Custom Object
* Contact_Cost__c of Contact Object to Total_Months_in_this_Position__c of TargetX_SRMb__Family_Relationship__c Object
* Account Object to TargetX_SRMb__Application__c Custom Object
* Acc_Cost__c of Account Object to Total_Months_Employed__c of TargetX_SRMb__Application__c Object
Please try to implement the same with the changes and let me know if it works.
Please mark this as the solution if it solved your purpose to help the Community grows.
Thanks,
Jainam Contractor,
Salesforce Consultant,
Varasi LLC
www.varasi.com
Thanks for helping out with the code. I followed your instruction and got the following error message: "Variable does not exist: ApplicationID".
Here is the code:
trigger SumTotalMonths on TargetX_SRMb__Family_Relationship__c (after insert,after update,after delete,after undelete)
{
Map<ID,TargetX_SRMb__Application__c> applicationMap = new Map<ID,TargetX_SRMb__Application__c>();
List<ID> appID = new List<ID>();
//check for the DML operation, for Insert,update and undelete
if(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUndelete){
for(TargetX_SRMb__Family_Relationship__c Rel1 : Trigger.new) {
//Add all new AccountID in the list
if(Rel1.TargetX_SRMb__Application__c != NULL)
appID.add(Rel1.TargetX_SRMb__Application__c);
}
}
// check for DML operation for update and delete
if(Trigger.IsDelete) {
for(TargetX_SRMb__Family_Relationship__c Rel2 : Trigger.old)
{
if(Rel2.TargetX_SRMb__Application__c != NULL)
appID.add(Rel2.TargetX_SRMb__Application__c);
}
}
// check if application list is not empty
if(appID != NULL && appID.size()>0){
// Add all the applications in the map to map IDs with total months employed
for(ID ApplicationID : appID) {
applicationMap.put(ApplicationID,
new TargetX_SRMb__Application__c(ID=ApplicationID,Total_Months_Employed__c= 0) ) ;
} }
// Calculate the total months employed based on value in the total months in this position
for(TargetX_SRMb__Family_Relationship__c Rel : [ SELECT ID,TargetX_SRMb__Application__c , Total_Months_in_this_Position__c
FROM TargetX_SRMb__Family_Relationship__c WHERE TargetX_SRMb__Application__c IN :appID ])
{applicationMap.put(Rel.ApplicationID).Total_Months_Employed__c += Rel.Total_Months_in_this_Position__c;
}
//commit to the database
Database.update(applicationMap.values());
}
Thanks,
Mariam
You have made one silly mistake in the code. In place of Map.get you have used Map.put. In the below code line, change the put to get method.
applicationMap.put(Rel.ApplicationID).Total_Months_Employed__c += Rel.Total_Months_in_this_Position__c
Change it to get and then i think you will fine.
Please let me know if you need any help. Mark it as the solution if it solved your purpose.
Thanks,
Jainam Contractor
I changed it to map.get as advised and it gave me the following errors:
1. Method does not exist or incorrect signature: void get(Id, TargetX_SRMb__Application__c) from the type Map<Id,TargetX_SRMb__Application__c>
2. Variable does not exist: ApplicationID
I am thinking the error might be in the block:
if(appID != NULL && appID.size()>0){
// Add all the applications in the map to map IDs with total months employed
for(ID ApplicationID : appID) {
applicationMap.get(ApplicationID,
new TargetX_SRMb__Application__c(ID=ApplicationID,Total_Months_Employed__c= 0) ) ;
} }
I replaced the ApplicationID variable is supposed to hold all the ids of the custom application field-TargetX_SRMb__Application__c, I dont know why the error message is saying variable applicationID does not exist
Sorry to bother you, I am new to Apex code hence the confusion.
Thanks for helping!
No problem resides in that get Method code snippet only.
You are using Rel.ApplicationId instead you should use Rel.TargetX_SRMb__Application__c (Id of the Parent Object).
Please make this changes and let me know if it works.
Thanks,
Jainam Contractor.
Here is the code in line 35 :
for(TargetX_SRMb__Family_Relationship__c Rel : [ SELECT ID,TargetX_SRMb__Application__c , Total_Months_in_this_Position__c
FROM TargetX_SRMb__Family_Relationship__c WHERE TargetX_SRMb__Application__c IN :appID ])
{applicationMap.get(Rel.TargetX_SRMb__Application__c).Total_Months_Employed__c += Rel.Total_Months_in_this_Position__c;
}
I tested for both insert and update operation.
Thanks,
Mariam
Can you please check the debug log for what you are able to get in the Map.
This exception is due to the matter that there might not be something for that ParentId in the Map.
Thanks,
Jainam Contractor
You are right, one of the relationship object had a blank value in the Total_Months_in_this_Position__c field.
I have fixed it and its working fine.
Thank you very much for helping out.
Best,
Mariam
I hope you had a great week!
Please do you mind taking a look at my test class and let me know what I am missing out? The current code coverage is just 50% and based on salesforce standard practice its supposed to be atleast 80%.
Thanks in anticipation for your help!
//All test methods in this class can not access all data
@isTest(seealldata=false)
public class GetTotalMonthsWorked {
static testMethod void CalcMonths(){
//create new Account
Account acc = new Account(Name= 'Unassigned Contacts', Industry = 'Education');
insert acc;
// Create new contact
Contact c = new Contact(FirstName= 'Sophie',LastName= 'Test', Accountid = acc.id, Gender__c = 'F', Email= 'Testset@test.com');
insert c;
// Create new application
TargetX_SRMb__Application__c app = new TargetX_SRMb__Application__c(TargetX_SRMb__Contact__c = c.id,TargetX_SRMb__Stage__c = 'In Progress',
TargetX_SRMb__Status__c= 'Incomplete');
insert app;
// Create list of relationships
List<TargetX_SRMb__Family_Relationship__c> RelList = new List <TargetX_SRMb__Family_Relationship__c>();
for( integer i=0; i<200; i++)
{
RelList.add(new TargetX_SRMb__Family_Relationship__c (Name = 'TestRel' + i, TargetX_SRMb__Contact__c = c.id,
End_Date__c = date.ValueOf('2018-03-02'), Start_Date__c = date.ValueOf('2016-03-02')));
}
insert RelList;
//confirm if the total months worked is updated.
List<TargetX_SRMb__Family_Relationship__c> RelMonths =new List<TargetX_SRMb__Family_Relationship__c>
([SELECT Total_Months_in_this_Position__c FROM TargetX_SRMb__Family_Relationship__c
WHERE Id= :app.id limit 1]);
system.assertEquals(200, RelMonths[0].Total_Months_in_this_Position__c);
//list all the application ids
List<TargetX_SRMb__Application__c> IDs= new List<TargetX_SRMb__Application__c>
([SELECT Total_Months_Employed__c FROM TargetX_SRMb__Application__c
WHERE id = :app.id limit 1]);
// update relationship record
List<TargetX_SRMb__Family_Relationship__c> RelList2 = new List<TargetX_SRMb__Family_Relationship__c>();
for ( integer i=0; i<10; i++)
{
TargetX_SRMb__Family_Relationship__c RelList3 = new TargetX_SRMb__Family_Relationship__c();
RelList3.End_Date__c= date.ValueOf('2019-03-02');
RelList3.Start_Date__c= date.ValueOf('2015-03-02');
RelList3.id = RelList[i].id;
RelList2.add(RelList3);
}
Update RelList2;
//delete relationship from a list of relationships
// TargetX_SRMb__Family_Relationship__c deletedRel = [ SELECT Name, IsDeleted FROM TargetX_SRMb__Family_Relationship__c
// WHERE Name = :RelList ALL ROWS];
// System.assertEquals(deletedTargetX_SRMb__Family_Relationship__c.IsDeleted,true);
}
}
Best,
Mariam
Please find the below code that i used for Account-Contact use case which i explained earlier. It is giving 100% coverage for my trigger on Contact Object. Can you please try to edit the fields used as per your requirement and let me know if it works.
Also, if you don't want to use the existing data, you need not put (seeAllData = false). It is considered default for the Test Class.
Change the Account and Contact field as per your needs and you will be all good.
Please let me know if it solves your purpose.
Thanks,
Jainam Contractor
I used your logic and I still got the same 50% code coverrage.
Below is the code and the class, the uncovered code is highlighted:
Apex code with block separator to highlight the uncovered code:
trigger SumTotalMonths on TargetX_SRMb__Family_Relationship__c (after insert,after update,after delete,after undelete)
{
Map<ID,TargetX_SRMb__Application__c> applicationMap = new Map<ID,TargetX_SRMb__Application__c>();
List<ID> appID = new List<ID>();
//check for the DML operation, for Insert,update and undelete
if(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUndelete){
for(TargetX_SRMb__Family_Relationship__c Rel1 : Trigger.new) {
//Add all new AccountID in the list
if(Rel1.TargetX_SRMb__Application__c != NULL && Rel1.Total_Months_in_this_Position__c != NULL)
appID.add(Rel1.TargetX_SRMb__Application__c);
}
}
// check for DML operation for update and delete
if(Trigger.IsDelete) {
for(TargetX_SRMb__Family_Relationship__c Rel2 : Trigger.old)
{
if(Rel2.TargetX_SRMb__Application__c != NULL && Rel2.Total_Months_in_this_Position__c != NULL)
appID.add(Rel2.TargetX_SRMb__Application__c);
}
}
// check if application list is not empty
if(appID != NULL && appID.size()>0){
// Add all the applications in the map to map IDs with total months employed
for(ID ApplicationID : appID) {
applicationMap.put(ApplicationID,
new TargetX_SRMb__Application__c(ID=ApplicationID,Total_Months_Employed__c= 0) ) ;
} }
// Calculate the total months employed based on value in the total months in this position
for(TargetX_SRMb__Family_Relationship__c Rel : [ SELECT ID,TargetX_SRMb__Application__c , Total_Months_in_this_Position__c
FROM TargetX_SRMb__Family_Relationship__c WHERE TargetX_SRMb__Application__c IN :appID ])
{applicationMap.get(Rel.TargetX_SRMb__Application__c).Total_Months_Employed__c += Rel.Total_Months_in_this_Position__c;
}
//commit to the database
Database.update(applicationMap.values());
}
Here is the test class:
public class GetTotalMonthsWorked {
static testMethod void CalcMonths(){
//create new Account
Account acc = new Account(Name= 'Unassigned Contacts', Industry = 'Education');
insert acc;
// Create new contact
Contact c = new Contact(FirstName= 'Sophie',LastName= 'Test', Accountid = acc.id, Gender__c = 'F', Email= 'Testset@test.com');
insert c;
// Create new application
TargetX_SRMb__Application__c app = new TargetX_SRMb__Application__c(TargetX_SRMb__Contact__c = c.id,TargetX_SRMb__Stage__c = 'In Progress',
TargetX_SRMb__Status__c= 'Incomplete');
insert app;
// Create list of relationships
List<TargetX_SRMb__Family_Relationship__c> RelList = new List <TargetX_SRMb__Family_Relationship__c>();
for( integer i=0; i<200; i++)
{
RelList.add(new TargetX_SRMb__Family_Relationship__c (Name = 'TestRel' + i, TargetX_SRMb__Contact__c = c.id,
End_Date__c = date.ValueOf('2018-03-02'), Start_Date__c = date.ValueOf('2016-03-02')));
}
insert RelList;
//confirm if the total months worked is updated.
TargetX_SRMb__Application__c App1 = [SELECT ID,Total_Months_Employed__c FROM TargetX_SRMb__Application__c
WHERE id = :app.id Limit 1];
System.assertNotEquals(1000,App1.Total_Months_Employed__c );
System.assertEquals(500,App1.Total_Months_Employed__c );
//Delete relationship record from application
TargetX_SRMb__Family_Relationship__c deletedRel = [SELECT ID, Name,TargetX_SRMb__Application__c
FROM TargetX_SRMb__Family_Relationship__c
WHERE TargetX_SRMb__Application__c = :App1.Name LIMIT 1];
delete deletedRel;
TargetX_SRMb__Application__c App2 = [SELECT ID,Total_Months_Employed__c FROM TargetX_SRMb__Application__c
WHERE id = :app.id Limit 1];
System.assertNotEquals(1000,App2.Total_Months_Employed__c );
System.assertEquals(500,App2.Total_Months_Employed__c );
}
}
Thanks for your help!
Mariam
Is the test executed successfully..?? I guess it is not as it might fail the assert statement. Because after inserting and after delete assert statements are same which can't be true. So rectify them. Also, if you put End_Date__c = date.ValueOf('2018-03-02'), Start_Date__c = date.ValueOf('2016-03-02') dates, what do you expect in this Total_Months_in_this_Position__c field ??? i guess 24... So your assert statement after insertion should be 24*200 = 4800 and after deletion it should be 24*199 = 4776.
Also you have not related the TargetX_SRMb__Family_Relationship__c records with the TargetX_SRMb__Application__c record.
Please rectify all this errors and then check, it might solve your purpose.
Also i would suggest you to create a new thread/ question so that other community members can also help you. And share that question link over here so that i can jump in directly.
Thanks,
Jainam Contractor
I have resolved the issues, thanks for your help.
My apologies for directing all the questions to you instead of everyone in the community, I was working against time and I thought you could help since we pretty much have the same code logic.
I appreciate your kind assistance!
Best,
Mariam
I have resolved the issues, thanks for your help.
My apologies for directing all the questions to you instead of everyone in the community, I was working against time and I thought you could help since we pretty much have the same code logic.
I appreciate your kind assistance!
Best,
Mariam
Happy to help anytime... Cheers..:)
Best Regards,
Jainam