I'm trying to do a query of child objects that looks like this

List<Child__c> childList = new List<Child__c>();
 childList = [SELECT Id, Status__c, Team_Member__c, Deactivation_Date__c FROM Child__c WHERE Status__c = 'Inactive'];

An issue arises in my code when this query returns 2 (or more) child records with the the same value in the Team_Member__c field. In that case, I need to get only the record with the most recent Deactivation_Date__c.

EX: If I had a set of records like this:

ID = 001; Team Member = Bob; Deactivation Date = Today(); Status=Inactive

ID = 002; Team Member = Sally; Deactivation Date = Yesterday(); Status=Inactive

ID = 003; Team Member = Jill; Deactivation Date = Today(); Status=Inactive

ID = 004; Team Member = Bob; Deactivation Date = Yesterday(); Status=Inactive

Then I would only want to return IDs 001, 002, and 003 because ID 001 Deactivation date > ID 004 Deactivation date

Has anyone had any experience with this? Is it possible to do a GROUP BY and MAX to return the desired results? Any help is greatly appreciated. Thank you!

  • It's me again. Could you not use the DESC sorting in SOQL on the date ? crmsalesforcetraining.com/soql-order-by-clause-asc-desc – Mihai Neagoe Jan 10 '15 at 20:51
  • Hi Mihai, thanks for your reply. I think the ORDER BY and DESC would only return values as follows: 001, 003, 004, 002. What I want is just 001, 002, 003, not 004. How can I compare records with the same Team Member and only select the one with the most recent deactivation date? – rwegner7 Jan 10 '15 at 21:02
  • Is the Parent_Share object linked to the Child__c object? – Mihai Neagoe Jan 10 '15 at 21:05
  • No, they aren't linked. They have many of the same values though, i.e. ParentID on Parent__Share = Parent__c on the child object, UserOrGroupId on Parent__Share = Team_Member__c on the child object – rwegner7 Jan 10 '15 at 21:10
  • I don't have enough experience in code to know, but another thing I tried was using Map to store the values...I couldn't get it to work though – rwegner7 Jan 10 '15 at 21:16

This is the idea I was telling you about in the comment and you can call this code from an afterUpdate trigger on the Child__c object. You don't really want to know the Team_Member__c or anything else. All you care is that when the flag on the Child__c object is set to Inactive, delete all the records in the Parent_Share object

Set<Id> parentIds new Set<Id>();
List<Child__c> childs = new List<Child__c>();
childs = trigger.old;

for(Child__c child : childs){
    if(Status__c == 'Inactive'){

List<Parent_Share> sharesToDelete = [Select Id From Parent_Share Where ParentId In :parentIds];

delete sharesToDelete;

Just a suggestion on how I see the problem from the outside. Maybe you know a bit more about the problem. Let me know if it was useful.

|improve this answer|||||
  • Thank you Mihai! Your suggestion was very helpful in getting past that bug. I ended up using two Sets to get it to where I needed it (see below) – rwegner7 Jan 10 '15 at 22:39
  • Nice to hear that. Also I don't think you will need the batch class anymore. If you've created a trigger make sure it as dumb as possible and the logic of the code should be moved to a class. – Mihai Neagoe Jan 10 '15 at 22:45
  • I see how you did it, you can ignore my previous comment about the trigger. – Mihai Neagoe Jan 10 '15 at 22:52

Following Mihai's suggestion, here's how I got what I needed

Set<Id> memberIds = new Set<Id>();
Set<Id> parentIds = new Set<Id>();

List<Child__c> childList = [SELECT Id, Parent__c, Status__c, Team_Member__c FROM Child__c WHERE Status = 'Inactive'];

for(Child c: childList){

List<Parent__Share> sharesToDelete = [SELECT Id FROM Parent__Share WHERE UserOrGroupId IN :memberIds AND ParentId IN :parentIds];

Delete sharesToDelete;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.