2

I can query the child cases for current case by something like below,

list<case> parentids=trigger.new

select id,status from case where parentid=:parentids

What if the children have more children? Does it make sense to have more than one level hierarchical relationship? Should I use lookup filter on parent case to allow only parent cases linked to child case and not child to child relationship? What's the best practice?

Is that possible to query all cases related to parent case (child, grand child etc)? Can somebody give me an idea to retrieve all related records?

5

The basic idea is to create a formula for Ultimate Parent

BLANKVALUE(Parent.Parent.Parent.Parent.ParentId,
    BLANKVALUE(Parent.Parent.Parent.ParentId,
        BLANKVALUE(Parent.Parent.ParentId,
            BLANKVALUE(Parent.ParentId,
                BLANKVALUE(ParentId, Id)
))))

Then, you can get your relevant cases as follows:

Set<Id> parentids = new Set<Id>();
for (Case newCase : trigger.new) parentIds.add(newCase.parentId);
List<Case> relevantCases = [
    SELECT Id FROM Case
    WHERE Ultimate_Parent__c
    IN (SELECT Ultimate_Parent__c FROM Case WHERE Id IN :parentids)
];

You might be able to use recalculateFormulas() to get the values without doing an inner-join-sub-select, but there is a known issue dealing with cross-object logic, so I have my doubts it would work.

Update

The recalculateFormulas method does give you the correct value, but also causes an exception if you execute it on the trigger records:

System.UnexpectedException: Unable to create/update fields: LastModifiedDate, IsDeleted, SystemModstamp, CreatedById, CreatedDate, LastModifiedById.

  • 1
    BLANKVALUE(Parent.Parent.Parent.Parent.ParentId,BLANKVALUE(Parent.Parent.Parent.ParentId,BLANKVALUE(Parent.Parent.ParentId,BLANKVALUE(Parent.ParentId,BLANKVALUE(ParentId, Id)))))? – sfdcfox Mar 7 '16 at 19:19
  • @sfdcfox Looks like a nice alternative. I only saw this in use once and just figured whoever wrote it used the most efficient path. I'll update shortly. I'd actually never seen the BLANKVALUE formula. Pretty slick. – Adrian Larson Mar 7 '16 at 19:24
  • 1
    I just checked... the compiled size using IF statements is about 3x larger than using BLANKVALUE for the name depth. – sfdcfox Mar 7 '16 at 19:27
  • I cannot save the formula, can you please verify – Muthu Samy Mar 7 '16 at 19:56
  • @MuthuSamy You might have trouble if you try to copy-paste. Try typing it out. I may have also somehow gotten hidden characters in there, so I cleaned up my version just in case. – Adrian Larson Mar 7 '16 at 20:05
0

You could do something like this. I recently learned about making custom object classes. I do think that having multiple children can be useful sometimes. I used items like this to build tree structures on visualforce pages. Hopefully this will be useful to you.

//declare your new object class
public class YourObject{
    //what is your object made out of?
    public Story__c theStory{get;set;}
    public list(Task__c) theTasks{get;set;}
}

//create method which returns a list of objects of your new class
public List(YourObject) getYourObjects{
    //the returning list
    public list(YourObject) objectList = new list(YourObject);

    //list of stories you wish to use
    public list(Story__c) storyList = new List(Story__c);
    storyList = [SELECT Name, Id FROM Story__c];

    //list of all Tasks
    list(Task__c) taskList = new List(Task__c);
    taskList = [SELECT Name, Story__c FROM Task__c]

    //now it's time to make some objects
    // for each story in your list
    for (Story__c story : storyList){

        //make a new object
        YourObject thing = new YourObject();
        //add the story to your object
        thing.theStory = story;

        //create the list of tasks related to your story
        list(Task__c) newTaskList = new List(Task__c);
        for (Task__c task : taskList){
            if (task.Story__c == story){
            newTaskList.add(task);
            }             
        }

        //make that list the thing's theTasks
        thing.theTasks = newTaskList;

        //add your thing to your list of objects
        objectList.add(thing);
    }
    //return your object list
    return objectList;
}

Be sure to use lists or sets to avoid using soql queries inside of for loops.

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.