2

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!

6
  • It's me again. Could you not use the DESC sorting in SOQL on the date ? crmsalesforcetraining.com/soql-order-by-clause-asc-desc Jan 10, 2015 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, 2015 at 21:02
  • Is the Parent_Share object linked to the Child__c object? Jan 10, 2015 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, 2015 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, 2015 at 21:16

2 Answers 2

2

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'){
       parentIds.add(child.Parent__c);
    }
}

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.

3
  • 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, 2015 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. Jan 10, 2015 at 22:45
  • I see how you did it, you can ignore my previous comment about the trigger. Jan 10, 2015 at 22:52
1

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){
memberIds.add(c.Team_Member__c);
parentIds.add(c.Parent__c);
}

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

Delete sharesToDelete;

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .