function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
CodeHeartsSFDCCodeHeartsSFDC 

Update custom object information on Account based on most recent date on custom field

Hello,

Can anyone help me with the requirement below? Thanks in adv

Requirement: I have 2 objects "Account" and custom object "Order__Api__Subscription__c". The custom object has a OrderApi__Term_start_date__c field. When the custom object record is inserted/updated, I want to update certain information on Account from custom object record having the latest/most recent OrderApi_Term_start_date__c field value.

I also have another requirement to populate the count of active records from the custom object on account which is working fine. The first part of the requirement is not working.

I wrote the below code. If I use Trigger.new in the for loop for(OrderApi__Subscription__c sub: Trigger.new) , it is updating the information from whichever record was updated recently. If I use the below code, it does not update anything.
  1. trigger updateAccount on OrderApi__Subscription__c (after insert, after update) {
  2.  
  3.     Map<ID, Account> parentAcc;
  4.     List<Id> listIds = new List<Id>();
  5.     List<Account> accountList = new List<Account>();
  6.     List<Account> accountList1 = new List<Account>();
  7.         
  8.     for (OrderApi__Subscription__c childObj : Trigger.new) {
  9.     listIds.add(childObj.OrderApi__Account__c);
  10.     }
  11.     
  12.     Integer soql = [Select count() FROM OrderApi__Subscription__c WHERE OrderApi__Account__r.Id =:listIds AND OrderApi__Is_Active__c != FALSE];
  13.     
  14.     parentAcc = new Map<Id, Account>([SELECT id, Collegiate_Current_Term_Start_Date__c, Member_Count__c, Collegiate_Paid_Through_Date__c, Collegiate_Current_Term_End_Date__c, Collegiate_Chapter_Status__c, Collegiate_Chapter_Activated_Date__c  FROM Account WHERE ID IN :listIds]);
  15.     
  16.     List<OrderApi__Subscription__c> subscription = [SELECT ID, OrderApi__Term_End_Date__c FROM OrderApi__Subscription__c WHERE id IN: listIds AND OrderApi__Term_End_Date__c != NULL Order By OrderApi__Term_End_Date__c desc LIMIT 1];
  17.     
  18.     Subscription__c mhc = Subscription__c.getInstance();
  19.     string ItemCollID = mhc.Collegiate_Item_Id__c;
  20.     
  21.     for(OrderApi__Subscription__c sub:subscription){
  22.     
  23.     if(sub.OrderApi__Item__c != NULL && sub.OrderApi__Item__c == ItemCollID) {
  24.  
  25.             Account newAcc = parentAcc.get(sub.OrderApi__Account__c);
  26.  
  27.             newAcc.Collegiate_Chapter_Status__c=sub.OrderApi__Status__c;
  28.  
  29.             newAcc.Collegiate_Chapter_Activated_Date__c=sub.OrderApi__Activated_Date__c;
  30.  
  31.             newAcc.Collegiate_Paid_Through_Date__c =sub.OrderApi__Paid_Through_Date__c;
  32.  
  33.             newAcc.Collegiate_Current_Term_End_Date__c=sub.OrderApi__Current_Term_End_Date__c;
  34.  
  35.             newAcc.Collegiate_Current_Term_Start_Date__c=sub.OrderApi__Current_Term_Start_Date__c;
  36.  
  37.             accountList.add(newAcc);
  38.  
  39.         }
  40.         
  41.     }
  42.     
  43.     for(OrderApi__Subscription__c subs:Trigger.new){
  44.     
  45.         Account newAcc1 = parentAcc.get(subs.OrderApi__Account__c);
  46.         
  47.         newAcc1.Member_Count__c = soql;
  48.         
  49.         accountList1.add(newAcc1);
  50.         
  51.     }
  52.     
  53.     if(accountList.size()>0) {
  54.  
  55.     update accountList;
  56.  
  57.     }
  58.        
  59.     if(accountList1.size()>0) {
  60.  
  61.     update accountList1;
  62.  
  63.     } 
  64.         
  65. }

 
Andrew GAndrew G
Ok,
so lets talk through the troubleshooting to help 

So, the loop  for(OrderApi__Subscription__c sub: Trigger.new) works, but not  for(OrderApi__Subscription__c sub:subscription){

so subscription is the change
where do we get subscription:

List<OrderApi__Subscription__c> subscription = [SELECT ID, OrderApi__Term_End_Date__c FROM OrderApi__Subscription__c WHERE id IN: listIds AND OrderApi__Term_End_Date__c != NULL Order By OrderApi__Term_End_Date__c desc LIMIT 1];

As we look at the query we see WHERE id IN: listIds

where does listIds come from:

for (OrderApi__Subscription__c childObj : Trigger.new) {
    listIds.add(childObj.OrderApi__Account__c);
    }

SO listIds is a list of (i presume) account Ids.

lets look at the query - FROM OrderApi__Subscription__c WHERE id IN: listIds - so we are trying to pull OrderApi__Subscription__c records where their ID matches an account Id.

so lets fix some lines:
trigger updateAccount on OrderApi__Subscription__c (after insert, after update) {
 
    Map<ID, Account> parentAcc;
//lets be clear about what list is what
//    List<Id> listIds = new List<Id>();
List<Id> accIds = new List<Id>();
List<Id> oasIds = new List<Id>();
    List<Account> accountList = new List<Account>();
    List<Account> accountList1 = new List<Account>();
        
for (OrderApi__Subscription__c childObj : Trigger.new) {
//  listIds.add(childObj.OrderApi__Account__c);
    accIds.add(childObj.OrderApi__Account__c);
    oasIds.add(childObj.Id);
    }
    
 //   Integer soql = [Select count() FROM OrderApi__Subscription__c WHERE OrderApi__Account__r.Id =:listIds AND OrderApi__Is_Active__c != FALSE];

    Integer soql = [Select count() FROM OrderApi__Subscription__c WHERE OrderApi__Account__c =:accIds AND OrderApi__Is_Active__c != FALSE];

    parentAcc = new Map<Id, Account>([SELECT id, Collegiate_Current_Term_Start_Date__c, Member_Count__c, Collegiate_Paid_Through_Date__c, Collegiate_Current_Term_End_Date__c, Collegiate_Chapter_Status__c, Collegiate_Chapter_Activated_Date__c  FROM Account WHERE ID IN :listIds]);
    
//    List<OrderApi__Subscription__c> subscription = [SELECT ID, OrderApi__Term_End_Date__c FROM OrderApi__Subscription__c WHERE id IN: listIds AND OrderApi__Term_End_Date__c != NULL Order By OrderApi__Term_End_Date__c desc LIMIT 1];

    List<OrderApi__Subscription__c> subscription = [SELECT ID, OrderApi__Term_End_Date__c FROM OrderApi__Subscription__c WHERE id IN: oasIds AND OrderApi__Term_End_Date__c != NULL Order By OrderApi__Term_End_Date__c desc LIMIT 1];

    Subscription__c mhc = Subscription__c.getInstance();
    string ItemCollID = mhc.Collegiate_Item_Id__c;
    
    for(OrderApi__Subscription__c sub:subscription){
       if(sub.OrderApi__Item__c != NULL && sub.OrderApi__Item__c == ItemCollID) {
            Account newAcc = parentAcc.get(sub.OrderApi__Account__c);
            newAcc.Collegiate_Chapter_Status__c=sub.OrderApi__Status__c;
            newAcc.Collegiate_Chapter_Activated_Date__c=sub.OrderApi__Activated_Date__c;
            newAcc.Collegiate_Paid_Through_Date__c =sub.OrderApi__Paid_Through_Date__c;
            newAcc.Collegiate_Current_Term_End_Date__c=sub.OrderApi__Current_Term_End_Date__c;
            newAcc.Collegiate_Current_Term_Start_Date__c=sub.OrderApi__Current_Term_Start_Date__c;
            accountList.add(newAcc);
        }
    }
    
    for(OrderApi__Subscription__c subs:Trigger.new){
        Account newAcc1 = parentAcc.get(subs.OrderApi__Account__c);
        newAcc1.Member_Count__c = soql;
        accountList1.add(newAcc1);
    }
    if(accountList.size()>0) {
       update accountList;
    }
    if(accountList1.size()>0) {
       update accountList1;
    }     
}

Thing to take away from this - use clear variable names - listIds as a variable name is not clear enough (in my mind) but accIds or oasIds is clearer.
You could go accountIds or AccountIdsList or similar, but they do start to become unweildy - example - listOrderApiSubscriptionIds

HTH

Regards
Andrew
CodeHeartsSFDCCodeHeartsSFDC
@Andrew

I tried your solution, but it is not working as expected. I did not mention that it was working when I use trigger.new. I'm saying Trigger.new updates the values but they are wrong values. The need is, I want to get the subscription record having the most recent Term end date and then update the related account fields with information from that most recent term end date subscription record. 
Andrew GAndrew G
The joy of writing untested code late on a Friday

Check line 21 and adjust: - we hadn't changed the ListIds variable to AccIds.
parentAcc = new Map<Id, Account>([SELECT id, Collegiate_Current_Term_Start_Date__c, Member_Count__c, Collegiate_Paid_Through_Date__c, Collegiate_Current_Term_End_Date__c, Collegiate_Chapter_Status__c, Collegiate_Chapter_Activated_Date__c  FROM Account WHERE ID IN :accIds]);

Regards
Andrew