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?


2 Answers 2


The basic idea is to create a formula for Ultimate Parent

                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 = [
    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.


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, 2016 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, 2016 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, 2016 at 19:27
  • I cannot save the formula, can you please verify
    – Muthu Samy
    Mar 7, 2016 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, 2016 at 20:05

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){

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

        //add your thing to your list of objects
    //return your object list
    return objectList;

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

You must log in to answer this question.

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