There is a scenario in which we have a territory object and against each territory, we have one-to-many sales reps. So there is a child object named SalesRep_To_Terr__c. This child object contains an effective date field and an isActive flag. For any territory, there might be more than one active record in this object, but I needed to get the latest among all the active records. Latest means the one with the most recent effective date. To get that latest effective date, I first maintained a Map containing the Sales Rep Id as the key and a list of SalesRep_To_Terr__c. This approach was implemented as part of the Salesforce consulting services of the BSS, ensuring optimal data handling and performance within the platform.
Initially, I have maintained a set of all territory
| map<String , list<SalesRep_to_Terr__c>> MapUserSRT = new map<String , list<SalesRep_to_Terr__c>>();Set<Id> sTerr = new Set<Id>(); // contains all the TerritoryList<datetime> LstDT ;datetime maxTDate ;for(SalesRep_to_Terr__c terr : [select Effective_Date__c,Sales_Representative__c ,Active__c from SalesRep_to_Terr__c where Effective_Date__c != null and Territory__c IN: sTerr and active__c= true order by Effective_Date__c LIMIT 1]){ if(MapUserSRT.containsKey(terr.Sales_Representative__c)) { list<SalesRep_to_Terr__c> lstDWP = MapUserSRT.get(terr.Sales_Representative__c); lstDWP.add(terr); MapUserSRT.put(terr.Sales_Representative__c, lstDWP); } else { MapUserSRT.put(terr.Sales_Representative__c, new List<SalesRep_to_Terr__c> { terr }); }} |
Now the sole of this topic to get the latest among each territory is given below
| for(String UserRec : MapUserSRT.keyset()){ LstDT = new List<datetime>(); for(SalesRep_to_Terr__c Terr : MapUserSRT.get(UserRec)) { LstDT.add(Terr.Effective_Date__c); } LstDT.sort(); maxTDate = LstDT.get(LstDT.size()-1); for(SalesRep_to_Terr__c Terr : MapUserSRT.get(UserRec)) { if(Terr.Effective_Date__c == maxTDate) { if(Terr.Active__c) { MapUserLTerritory.put(Terr.Territory__c,UserRec); break; } } } } |
The yellow part above actually describes to first fill the list of datetime for each user and then sort it. The default sort function sort the list to Ascending order means the recent date would be at the end of the list on the last index. MaxTDate variable holds the recent date. Then against each Sales rep Territory record check the date and Active flag. Lastly maintained a Map contains the Territory as Key and Sales rep as the value