Apex Code Development You need to sign in to do that
Don't have an account?
Nagarjuna Reddy.P Trigger to update parent picklist field
Hi All,
I'm little confused to choose trigger/process builder with my scenario could you please help me with this.
I have parent and child objects with Lookup relationship. I need to auto update parent picklist field with conditions as follows
1.If parent has child records created in last 2 months from todays date update to valueA
2.If parent has child records in the past and there have not been any childs in last 2 months from todays date then ValueB
3.If parent never have any childs update to valueC
I hope we cant use process builder for this and trigger is preferred to write on child object but which trigger context i should use.
Could any one please give me an idea how to do this.
thank you.
I'm little confused to choose trigger/process builder with my scenario could you please help me with this.
I have parent and child objects with Lookup relationship. I need to auto update parent picklist field with conditions as follows
1.If parent has child records created in last 2 months from todays date update to valueA
2.If parent has child records in the past and there have not been any childs in last 2 months from todays date then ValueB
3.If parent never have any childs update to valueC
I hope we cant use process builder for this and trigger is preferred to write on child object but which trigger context i should use.
Could any one please give me an idea how to do this.
thank you.
PFB the overview you need to follow:
Inside batch start() method:
--> String sQuery = 'SELECT Id, picklistField__c, (SELECT Id, createdDate FROM child__r ORDER BY CreatedDate DESC) FROM ParentObj__c';
--> Database.query(squery);
Inside execute() method:
--> Date today = Date.today();
--> Date twoMonthsBefore = today.addDays(-60);
--> List<ParentObj__c> updateParentObjs = new List<ParentObj__c>();
--> for(ParentObj__c pObj : scope){ //scope is the list returned from the start method
if(!pObj.child__r.isEmpty()){
if(pObj.child__r[0].createdDate > twoMonthsBefore){
pObj.picklistField__c = 'ValueA';
updateParentObjs.add(pObj);
}else if(pObj.child__r[0].createdDate < twoMonthsBefore){
pObjj.picklistField__c = 'ValueB';
updateParentObjs.add(pObj);
}
}else{
pObj.picklistField__c = 'ValueC';
updateParentObjs.add(pObj);
}
}
if(!updateParentObjs.isEmpty()){
update updateParentObjs;
}
Leave finish() method empty.
All Answers
Your requirement can neither be achieved using PB nor with Trigger.
You need to write Batch class to achieve this.
thanks for your reply, I'm really surprised hearing to use batch class. Could you please explain how i can achieve this through batch class.
the important thing i need to say you is I'm upserting 100's of child records through data loader and then mapping parent with newly upserted child records.
PFB the overview you need to follow:
Inside batch start() method:
--> String sQuery = 'SELECT Id, picklistField__c, (SELECT Id, createdDate FROM child__r ORDER BY CreatedDate DESC) FROM ParentObj__c';
--> Database.query(squery);
Inside execute() method:
--> Date today = Date.today();
--> Date twoMonthsBefore = today.addDays(-60);
--> List<ParentObj__c> updateParentObjs = new List<ParentObj__c>();
--> for(ParentObj__c pObj : scope){ //scope is the list returned from the start method
if(!pObj.child__r.isEmpty()){
if(pObj.child__r[0].createdDate > twoMonthsBefore){
pObj.picklistField__c = 'ValueA';
updateParentObjs.add(pObj);
}else if(pObj.child__r[0].createdDate < twoMonthsBefore){
pObjj.picklistField__c = 'ValueB';
updateParentObjs.add(pObj);
}
}else{
pObj.picklistField__c = 'ValueC';
updateParentObjs.add(pObj);
}
}
if(!updateParentObjs.isEmpty()){
update updateParentObjs;
}
Leave finish() method empty.
Do I need to write separate batch for each relation.
First of all, I am really happy that my solution helped you achieve your requirement.
For your second requirement, you can simply write after insert trigger to update the parent record.
I have custom child object with 4 lookup relations(lookup1,lookup2--lookup4) to parent. If any one of these relational records are created(either lookup1 record or lookup2 record or ---- lookup4 record) I need to update parent field which is only field to update if any child records is created with date conditions explained above.
I dont think trigger will satisfy my requirement bcz i dont need to update parent field based on events.
The first requirement you have checks and updates the field based on the child record w.r.t today. You cannot achieve this by using trigger as trigger fires only when a record is modified (inserted/updated).
On the contrary, your Batch class will run daily and check the data for the 2 last months and update the field accordingly.
1.If parent has child records created in last 2 months from todays date update to valueA
2.If parent has child records in the past and there have not been any childs in last 2 months from todays date then ValueB
3.If parent never have any childs update to valueC
these conditions applies for all the lookup fields. there are no two requirements here only one requirement.each lookup has individual child relationship name we queried only for one child relationship in batch apex. If record of any relationship either 1,2 ,3 or 4 is created then parent field is to be updated.I hope Now u can have clear idea about my requirement.
You need to update the query string in start method to accomodate all the four relationships like: The reason I am saying it to be done by Batch apex :
Used your updated query string and its worked well.but while I'm testing it in Partial copy sandbox where there are 10k plus account records to process through batch apex but 4 to 5 batches are failed and throwing error as First error: Aggregate query has too many rows for direct assignment, use FOR loop.
actually no account has 200 child records in my sandbox.
so I used for loop but even its showing the same error, I dont no why this occurs eventhough iterating through for loop and even i tested by using limit 200 in sub query but the same error is throwing. you can see my code here
global with sharing class batchHandler implements Database.Batchable<SObject> {
global Database.QueryLocator start(Database.BatchableContext bc){
String squery = 'SELECT Id,Referral_Stage__c,Account_Type__c,(SELECT Id,createdDate from child1__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child2__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child3__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child4__r ORDER BY CreatedDate DESC) from Account';
return Database.getQueryLocator(squery);
}
global void execute(Database.BatchableContext bc,List<Account> scope){
Date today = Date.today();
Date twoMonthsBefore = today.addDays(-10);
List<Account> accounts = new List<Account>();
//if(scope.size()>0){
for(Account acc:scope){
if(!acc.child1__r .isEmpty()){
for(List<Referral__c> insurance:acc.child1__r ){
if(insurance[0].createdDate > twoMonthsBefore){
acc.Referral_Stage__c='Currently Referring';
accounts.add(acc);
} else if(insurance[0].createdDate < twoMonthsBefore){
acc.Referral_Stage__c='Referred Inactive';
accounts.add(acc);
}
}
}else if(!acc.child2__r .isEmpty()){
for(List<Referral__c> mso:acc.child2__r ){
if(mso[0].createdDate > twoMonthsBefore){
acc.Referral_Stage__c='Currently Referring';
accounts.add(acc);
}else if(mso[0].createdDate < twoMonthsBefore){
acc.Referral_Stage__c='Referred Inactive';
accounts.add(acc);
}
}
}else if(!acc.child3__r .isEmpty()){
for(List<Referral__c> ipa:acc.child3__r ){
if(ipa[0].createdDate > twoMonthsBefore){
acc.Referral_Stage__c='Currently Referring';
accounts.add(acc);
}else if(ipa[0].createdDate < twoMonthsBefore){
acc.Referral_Stage__c='Referred Inactive';
accounts.add(acc);
}
}
}else if(!acc.PCPs_Referred_Patients__r.isEmpty()){
for(List<Referral__c> pcp:acc.child4__r ){
if(pcp[0].createdDate > twoMonthsBefore){
acc.Referral_Stage__c='Currently Referring';
accounts.add(acc);
}else if(pcp[0].createdDate < twoMonthsBefore){
acc.Referral_Stage__c='Referred Inactive';
accounts.add(acc);
}
}
}
else{
acc.Referral_Stage__c='Never Referred';
accounts.add(acc);
}
}
// }
if(!accounts.isEmpty()){
update accounts;
}
}
global void finish(Database.BatchableContext bc){
}
}
This working with no issues in developer sandbox but in partial copy throwing error.. do you have any idea about this type of error and pls let me know if anything wrong with my query and code.
thank you.
Plz check the for-loop you are using :
for(List<Referral__c> insurance:acc.child1__r ){ Rather this should be : for(Referral__c insurance:acc.child1__r ){Hi Danish,
Thank you for your reply and suggestion, I tried your suggestion replacing List<Referral__c> with Referral__c in for loop and faced error in the immediate if condition if(insurance[0].createdDate > twoMonthsBefore){ stating Expression must be a list type : Referral__c, so i used separe list to store those child records and referred in if condition and error is resolved, but still batch is failed and infact the number of failed batches are increased than previous. Its giving the same error First error: Aggregate query has too many rows for direct assignment, use FOR loop.
Didn't get your comment above, plz check if you have modfied your code as below and still getting the error:
if(!acc.child1__r.isEmpty()){ for(Referral__c insurance:acc.child1__r ){ if(insurance.createdDate > twoMonthsBefore){ acc.Referral_Stage__c='Currently Referring'; accounts.add(acc); } else if(insurance.createdDate < twoMonthsBefore){ acc.Referral_Stage__c='Referred Inactive'; accounts.add(acc); } }do the same to other if conditions as well
Plz ping me <danish.hoda>
Apex script unhandled exception caused by: System.ListException: Duplicate id in list: 001f400000V3oixAAB.
Please update your code as below:
global with sharing class batchHandler implements Database.Batchable<SObject> { global Database.QueryLocator start(Database.BatchableContext bc){ String squery = 'SELECT Id,Referral_Stage__c,Account_Type__c,(SELECT Id,createdDate from child1__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child2__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child3__r ORDER BY CreatedDate DESC),(SELECT Id,createdDate from child4__r ORDER BY CreatedDate DESC) from Account'; return Database.getQueryLocator(squery); } global void execute(Database.BatchableContext bc,List<Account> scope){ Date today = Date.today(); Date twoMonthsBefore = today.addDays(-10); Set<Account> accounts = new Set<Account>(); List<Account> accToUpdate = new List<Account>(); //if(scope.size()>0){ for(Account acc:scope){ if(!acc.child1__r .isEmpty()){ for(Referral__c insurance:acc.child1__r ){ if(insurance.createdDate > twoMonthsBefore){ acc.Referral_Stage__c='Currently Referring'; accounts.add(acc); } else if(insurance.createdDate < twoMonthsBefore){ acc.Referral_Stage__c='Referred Inactive'; accounts.add(acc); } } }else if(!acc.child2__r .isEmpty()){ for(Referral__c mso:acc.child2__r ){ if(mso.createdDate > twoMonthsBefore){ acc.Referral_Stage__c='Currently Referring'; accounts.add(acc); }else if(mso.createdDate < twoMonthsBefore){ acc.Referral_Stage__c='Referred Inactive'; accounts.add(acc); } } }else if(!acc.child3__r .isEmpty()){ for(Referral__c ipa:acc.child3__r ){ if(ipa.createdDate > twoMonthsBefore){ acc.Referral_Stage__c='Currently Referring'; accounts.add(acc); }else if(ipa.createdDate < twoMonthsBefore){ acc.Referral_Stage__c='Referred Inactive'; accounts.add(acc); } } }else if(!acc.PCPs_Referred_Patients__r.isEmpty()){ for(Referral__c pcp:acc.child4__r ){ if(pcp.createdDate > twoMonthsBefore){ acc.Referral_Stage__c='Currently Referring'; accounts.add(acc); }else if(pcp.createdDate < twoMonthsBefore){ acc.Referral_Stage__c='Referred Inactive'; accounts.add(acc); } } } else{ acc.Referral_Stage__c='Never Referred'; accounts.add(acc); } } // } if(!accounts.isEmpty()){ //update accounts; accToUpdate.addAll(accounts); } if(!accToUpdate.isEmpty()){ update accToUpdate; } } global void finish(Database.BatchableContext bc){ } }