Account Hierchy is now a configurable component in LEX but it only works with Accounts. Since LWC now has 2 components (lightning-tree & lightning-tree-grid ) that visualizes a hierarhy, I'm trying to make a generic component that could be displayed on any ligtning record page and be configured to use custom fields. There was a Inline Account hierarchy by Salesforce Labs a while back but it required users to modify code to work with other objects and was based on VF. I've tried to find other more recent solutions but the samples I find are not as dynamic and effective as my requirements. So I have started to develop my own solution with apex that can deliver ultimate parent Id:s form any object hierarchy and create a top-to-down traversable tree that can be used in JS/LWC. 

I'm now at a stage where I have this working quite well just have some finishing touches on the LWC left before I release this to a few customers that have asked for this functionality for Opp and Asset hierarchies specifically. Working with this meant diving into some areas like Dynamic DML and recursion that I don't have that much experience with (recursion and trees feels liks solving a school assignment and that was many years ago) so I would appreciate feedback and suggestions for improvements.

The first part is for getting the Ulitmate Parent Id:

@AuraEnabled(cacheable=true)
    global static Id getUltimateParent(Id recordId, string parentRelationship) {
        // Get ultimate parent by checking 5 hierarchy levels per SOQL-query recursively
        Id retId = null;
        parentRelationship = String.escapeSingleQuotes(parentRelationship);
        String sobjectType = recordId.getSObjectType().getDescribe().getName();
        String q = 'SELECT Id, ' + parentRelationship + '.Id, ' + parentRelationship + '.' + parentRelationship + '.Id, ' + parentRelationship + '.' + parentRelationship +'.' + parentRelationship + '.Id, ' +
            parentRelationship + '.' + parentRelationship +'.' + parentRelationship + '.' + parentRelationship + '.Id , ' + parentRelationship + '.' + parentRelationship + '.' + parentRelationship + '.' + parentRelationship +'.' + parentRelationship + 
            '.Id  FROM ' + sobjectType + ' WHERE Id = :recordId';
        List<sObject> sobjList = Database.query(q);
        if(sobjList.size() == 1) {
            Id l1, l5  = null;  
            try {
                // try from first to 5th level to get parent Id
                // Exception will happen at first null value 
                l1 = (Id) sObjList[0].getSobject(parentRelationship).get('Id');
                l1 = (Id) sObjList[0].getSobject(parentRelationship).getSobject(parentRelationship).get('Id');
                l1 = (Id) sObjList[0].getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).get('Id');
                l1 = (Id) sObjList[0].getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).get('Id');
                l5 = (Id) sObjList[0].getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).get('Id');
            } catch (NullPointerException ex) {
                // Do nothing, l1 is last Id before exception if no more levels up
                // System.debug('Exception');  
            }
            if( l5 != null ) { retId = getUltimateParent(l5, parentRelationship); }    // top level still have parent, do recursive call to check another five levels      
            else if( l1 != null ) { retId = l1 ; } 
            else { retId = sObjList[0].Id;}
        }
        // system.debug('DEBUG: ' + retId);
        return retId;
    }

 

It takes an Id and a relatonship and traverses the hierarchy upwards 5 levels at a time. This means only one SOQL query per 5-levels so this would work for hierarchies up to 500 levels deep. I've chosen to call this separately from LWC since every call to apex has it's own trnsaction limit. I wanted to avoid solutions like formula fields and triggers but of course the exact same could could be called by a trigger to find and store the ultimate parent Id.

The next step is creating a top-to-down traversable tree. For this I'm using the follwoing class to hold the nodes.

global with sharing class TreeNode {
    @AuraEnabled
    global Sobject nodeObj;

    @AuraEnabled
    global List<TreeNode> children;

    global TreeNode(Sobject obj) {
        // constructor adds empty child list
        nodeObj = obj;
        children = new List<TreeNode>();
    }
}

Just a generic sObject and a Collection of gereneric sObjects as children. This gets populatd by the following method:

@AuraEnabled(cacheable=true)
    global static List<TreeNode> getTreeNodes(List<Id> recordIds, string parentRelationship, string label, list<String> additionalFields) {
        parentRelationship = String.escapeSingleQuotes(parentRelationship);
        label = String.escapeSingleQuotes(label);
        String additionalStr = additionalFields.size() == 0 ? '' : String.escapeSingleQuotes(String.join(additionalFields, ', ') + ',');
        String sobjectType = recordIds[0].getSObjectType().getDescribe().getName();  // all recordIds need to be same type
        system.debug('DEBUG: ' + sobjectType);
        system.debug('DEBUG recordIds: ' + recordIds);
        String q = 'SELECT Id, ' + label + ', ' + additionalStr + parentRelationship + '.Id FROM ' + sobjectType + ' WHERE Id IN :recordIds OR ' + parentRelationship + '.Id IN :recordIds OR ' + parentRelationship + '.' + parentRelationship + '.Id IN :recordIds OR ' + parentRelationship + '.' + parentRelationship +'.' + parentRelationship + '.Id IN :recordIds OR ' +
            parentRelationship + '.' + parentRelationship +'.' + parentRelationship + '.' + parentRelationship + '.Id IN :recordIds OR ' + parentRelationship + '.' + parentRelationship + '.' + parentRelationship + '.' + parentRelationship +'.' + parentRelationship + 
            '.Id IN :recordIds ORDER BY ' + label;
        system.debug('DEBUG: ' + q);
        List<sObject> sobjList = Database.query(q);
        system.debug('DEBUG: ' + sobjList);
        Map<Id,TreeNode> TreeMap = new Map<Id,TreeNode>();
        List<TreeNode> retNodes = new List<TreeNode>();
        for (Sobject sobj : sobjList) {
            // first create all the nodes and add to map
            TreeNode tn = new TreeNode(sobj);
            TreeMap.put(sobj.Id, tn);
            system.debug('DEBUG Added node: ' + sobj.get('Name')); 
        }
        List<Id> level5Ids = new List<Id>();
        system.debug('DEBUG treemap: ' + TreeMap);
        for (Sobject sobj : sobjList) {
            // then attach all children to their parents
            if(recordIds.contains(sobj.Id) ) {
                // this is a top node add it to list
                system.debug('Top Node Added');
                retNodes.add(TreeMap.get(sobj.Id));
            } else {
                // this is a child node that should be added to parent list
                Id parentId = (Id) TreeMap.get(sobj.Id).nodeObj.getSobject(parentRelationship).get('Id');
                system.debug('DEBUG parentId: ' + parentId);
                system.debug('DEBUG parent: ' + TreeMap.get(parentId));
                TreeNode child =  TreeMap.get(sobj.Id);
                system.debug('DEBUG child: ' +  child );
                TreeMap.get(parentId).children.add(child);
            }
        }    
        for (Sobject sobj : sobjList) {
            if(TreeMap.get(sobj.Id).children.size() == 0  && !recordIds.contains(sobj.Id) )  {
                // no children and not in top Ids. Check if it is level 5 and in that case add to list for recursive call
                try {
                    Id l1, l2, l3, l4, l5;
                    l1 = (Id) TreeMap.get(sobj.Id).nodeObj.getSobject(parentRelationship).get('Id');
                    l2 = (Id) TreeMap.get(l1).nodeObj.getSobject(parentRelationship).get('Id');
                    l3 = (Id) TreeMap.get(l2).nodeObj.getSobject(parentRelationship).get('Id');
                    l4 = (Id) TreeMap.get(l3).nodeObj.getSobject(parentRelationship).get('Id');
                    l5 = (Id) TreeMap.get(l4).nodeObj.getSobject(parentRelationship).get('Id');
                    // no exception for 5 levels means that we have a bottom node that needs to be added to list for recursive call
                    level5Ids.add(sobj.Id);
                } catch (NullPointerException ex) {
                    // exception means that the bottom node was not at level 5 so just continue
                }
            }
        }     
        if(level5Ids.size() > 0) {
            List<TreeNode> nextChildList = new List<TreeNode>();
            // possibly more nodes needed do a recursive call and combine result 
            nextChildList = getTreeNodes(level5Ids, parentRelationship, label, additionalFields) ;
            for(TreeNode t : nextChildList) {
                // top nodes from recursive call is same as bottom node in current context. Just replace the child
                TreeMap.get((Id) t.nodeObj.get('Id')).children = t.children;
            }
        }
        return retNodes;
    }

Again I'm getting 5 levels at a time but this time going downward by using multiple levels of Id:s in the Where clause. So a tree with less than 5 levels will only take 1 SOQL query. 

Of course there are other limits in place. A tree can grow exponentially and quickly pass 50000 records or a complex tree can run into problems with cpu limits. However I think that this solution is enough efficeint and will work for all use cases where the lwc tree components are reasonable to use. The LWC component that gets the nodetree back creates a tree like below.

traverseNodes (nodes) {
        let retArr = [];
        nodes.forEach(element => {
            let retObj = {};
            retObj.name = element.nodeObj.Id,
            retObj.label = element.nodeObj[this.labelField];
            retObj.metatext = element.nodeObj[this.metaField];
            retObj.href = '/' + element.nodeObj.Id;
            retObj.expanded = (this.view == 'All Levels') || (this.view == 'First Level' && element.nodeObj.Id == this.ultimateParentId);
            if(element.children.length > 0) {
                retObj.items = this.traverseNodes(element.children);
            }
            retArr.push(retObj);
        });
        return retArr;
    }

For treegrid it's a little bit more complicated to handle multiple columns and datatypes needs to be mapped, but still fairly simple. I take the field data as configurable input from Page Builder. I use a wired getObjectInfo call to get the existing fields from the record context and use that for sanity checks and datatype mapping.

The current GUI-draft looks like

Works on all custom objects and most standard and can be configured for columns.  The components can also be included in a Lighting Flow which makes it possible to use a button and a modal to display the tree instead of doing it on the page. 

Sorry for the long post but If you've read all of it I'd appreciate some feedback. And anyone that is interesteed in helping testing out the first version of this just let me know. 

Account Hierchy is now a configurable component in LEX but it only works with Accounts. Since LWC now has 2 components (lightning-tree & lightning-tree-grid ) that visualizes a hierarhy, I'm trying to make a generic component that could be displayed on any ligtning record page and be configured to use custom fields. There was a Inline Account hierarchy by Salesforce Labs a while back but it required users to modify code to work with other objects and was based on VF. I've tried to find other more recent solutions but the samples I find are not as dynamic and effective as my requirements. So I have started to develop my own solution with apex that can deliver ultimate parent Id:s form any object hierarchy and create a top-to-down traversable tree that can be used in JS/LWC. 

I'm now at a stage where I have this working quite well just have some finishing touches on the LWC left before I release this to a few customers that have asked for this functionality for Opp and Asset hierarchies specifically. Working with this meant diving into some areas like Dynamic DML and recursion that I don't have that much experience with (recursion and trees feels liks solving a school assignment and that was many years ago) so I would appreciate feedback and suggestions for improvements.

The first part is for getting the Ulitmate Parent Id:

@AuraEnabled(cacheable=true)
    global static Id getUltimateParent(Id recordId, string parentRelationship) {
        // Get ultimate parent by checking 5 hierarchy levels per SOQL-query recursively
        Id retId = null;
        parentRelationship = String.escapeSingleQuotes(parentRelationship);
        String sobjectType = recordId.getSObjectType().getDescribe().getName();
        String q = 'SELECT Id, ' + parentRelationship + '.Id, ' + parentRelationship + '.' + parentRelationship + '.Id, ' + parentRelationship + '.' + parentRelationship +'.' + parentRelationship + '.Id, ' +
            parentRelationship + '.' + parentRelationship +'.' + parentRelationship + '.' + parentRelationship + '.Id , ' + parentRelationship + '.' + parentRelationship + '.' + parentRelationship + '.' + parentRelationship +'.' + parentRelationship + 
            '.Id  FROM ' + sobjectType + ' WHERE Id = :recordId';
        List<sObject> sobjList = Database.query(q);
        if(sobjList.size() == 1) {
            Id l1, l5  = null;  
            try {
                // try from first to 5th level to get parent Id
                // Exception will happen at first null value 
                l1 = (Id) sObjList[0].getSobject(parentRelationship).get('Id');
                l1 = (Id) sObjList[0].getSobject(parentRelationship).getSobject(parentRelationship).get('Id');
                l1 = (Id) sObjList[0].getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).get('Id');
                l1 = (Id) sObjList[0].getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).get('Id');
                l5 = (Id) sObjList[0].getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).getSobject(parentRelationship).get('Id');
            } catch (NullPointerException ex) {
                // Do nothing, l1 is last Id before exception if no more levels up
                // System.debug('Exception');  
            }
            if( l5 != null ) { retId = getUltimateParent(l5, parentRelationship); }    // top level still have parent, do recursive call to check another five levels      
            else if( l1 != null ) { retId = l1 ; } 
            else { retId = sObjList[0].Id;}
        }
        // system.debug('DEBUG: ' + retId);
        return retId;
    }

 

It takes an Id and a relatonship and traverses the hierarchy upwards 5 levels at a time. This means only one SOQL query per 5-levels so this would work for hierarchies up to 500 levels deep. I've chosen to call this separately from LWC since every call to apex has it's own trnsaction limit. I wanted to avoid solutions like formula fields and triggers but of course the exact same could could be called by a trigger to find and store the ultimate parent Id.

The next step is creating a top-to-down traversable tree. For this I'm using the follwoing class to hold the nodes.

global with sharing class TreeNode {
    @AuraEnabled
    global Sobject nodeObj;

    @AuraEnabled
    global List<TreeNode> children;

    global TreeNode(Sobject obj) {
        // constructor adds empty child list
        nodeObj = obj;
        children = new List<TreeNode>();
    }
}

Just a generic sObject and a Collection of gereneric sObjects as children. This gets populatd by the following method:

@AuraEnabled(cacheable=true)
    global static List<TreeNode> getTreeNodes(List<Id> recordIds, string parentRelationship, string label, list<String> additionalFields) {
        parentRelationship = String.escapeSingleQuotes(parentRelationship);
        label = String.escapeSingleQuotes(label);
        String additionalStr = additionalFields.size() == 0 ? '' : String.escapeSingleQuotes(String.join(additionalFields, ', ') + ',');
        String sobjectType = recordIds[0].getSObjectType().getDescribe().getName();  // all recordIds need to be same type
        system.debug('DEBUG: ' + sobjectType);
        system.debug('DEBUG recordIds: ' + recordIds);
        String q = 'SELECT Id, ' + label + ', ' + additionalStr + parentRelationship + '.Id FROM ' + sobjectType + ' WHERE Id IN :recordIds OR ' + parentRelationship + '.Id IN :recordIds OR ' + parentRelationship + '.' + parentRelationship + '.Id IN :recordIds OR ' + parentRelationship + '.' + parentRelationship +'.' + parentRelationship + '.Id IN :recordIds OR ' +
            parentRelationship + '.' + parentRelationship +'.' + parentRelationship + '.' + parentRelationship + '.Id IN :recordIds OR ' + parentRelationship + '.' + parentRelationship + '.' + parentRelationship + '.' + parentRelationship +'.' + parentRelationship + 
            '.Id IN :recordIds ORDER BY ' + label;
        system.debug('DEBUG: ' + q);
        List<sObject> sobjList = Database.query(q);
        system.debug('DEBUG: ' + sobjList);
        Map<Id,TreeNode> TreeMap = new Map<Id,TreeNode>();
        List<TreeNode> retNodes = new List<TreeNode>();
        for (Sobject sobj : sobjList) {
            // first create all the nodes and add to map
            TreeNode tn = new TreeNode(sobj);
            TreeMap.put(sobj.Id, tn);
            system.debug('DEBUG Added node: ' + sobj.get('Name')); 
        }
        List<Id> level5Ids = new List<Id>();
        system.debug('DEBUG treemap: ' + TreeMap);
        for (Sobject sobj : sobjList) {
            // then attach all children to their parents
            if(recordIds.contains(sobj.Id) ) {
                // this is a top node add it to list
                system.debug('Top Node Added');
                retNodes.add(TreeMap.get(sobj.Id));
            } else {
                // this is a child node that should be added to parent list
                Id parentId = (Id) TreeMap.get(sobj.Id).nodeObj.getSobject(parentRelationship).get('Id');
                system.debug('DEBUG parentId: ' + parentId);
                system.debug('DEBUG parent: ' + TreeMap.get(parentId));
                TreeNode child =  TreeMap.get(sobj.Id);
                system.debug('DEBUG child: ' +  child );
                TreeMap.get(parentId).children.add(child);
            }
        }    
        for (Sobject sobj : sobjList) {
            if(TreeMap.get(sobj.Id).children.size() == 0  && !recordIds.contains(sobj.Id) )  {
                // no children and not in top Ids. Check if it is level 5 and in that case add to list for recursive call
                try {
                    Id l1, l2, l3, l4, l5;
                    l1 = (Id) TreeMap.get(sobj.Id).nodeObj.getSobject(parentRelationship).get('Id');
                    l2 = (Id) TreeMap.get(l1).nodeObj.getSobject(parentRelationship).get('Id');
                    l3 = (Id) TreeMap.get(l2).nodeObj.getSobject(parentRelationship).get('Id');
                    l4 = (Id) TreeMap.get(l3).nodeObj.getSobject(parentRelationship).get('Id');
                    l5 = (Id) TreeMap.get(l4).nodeObj.getSobject(parentRelationship).get('Id');
                    // no exception for 5 levels means that we have a bottom node that needs to be added to list for recursive call
                    level5Ids.add(sobj.Id);
                } catch (NullPointerException ex) {
                    // exception means that the bottom node was not at level 5 so just continue
                }
            }
        }     
        if(level5Ids.size() > 0) {
            List<TreeNode> nextChildList = new List<TreeNode>();
            // possibly more nodes needed do a recursive call and combine result 
            nextChildList = getTreeNodes(level5Ids, parentRelationship, label, additionalFields) ;
            for(TreeNode t : nextChildList) {
                // top nodes from recursive call is same as bottom node in current context. Just replace the child
                TreeMap.get((Id) t.nodeObj.get('Id')).children = t.children;
            }
        }
        return retNodes;
    }

Again I'm getting 5 levels at a time but this time going downward by using multiple levels of Id:s in the Where clause. So a tree with less than 5 levels will only take 1 SOQL query. 

Of course there are other limits in place. A tree can grow exponentially and quickly pass 50000 records or a complex tree can run into problems with cpu limits. However I think that this solution is enough efficeint and will work for all use cases where the lwc tree components are reasonable to use. The LWC component that gets the nodetree back creates a tree like below.

traverseNodes (nodes) {
        let retArr = [];
        nodes.forEach(element => {
            let retObj = {};
            retObj.name = element.nodeObj.Id,
            retObj.label = element.nodeObj[this.labelField];
            retObj.metatext = element.nodeObj[this.metaField];
            retObj.href = '/' + element.nodeObj.Id;
            retObj.expanded = (this.view == 'All Levels') || (this.view == 'First Level' && element.nodeObj.Id == this.ultimateParentId);
            if(element.children.length > 0) {
                retObj.items = this.traverseNodes(element.children);
            }
            retArr.push(retObj);
        });
        return retArr;
    }

For treegrid it's a little bit more complicated to handle multiple columns and datatypes needs to be mapped, but still fairly simple. I take the field data as configurable input from Page Builder. I use a wired getObjectInfo call to get the existing fields from the record context and use that for sanity checks and datatype mapping.

The current GUI-draft looks like

Works on all custom objects and most standard and can be configured for columns.  The components can also be included in a Lighting Flow which makes it possible to use a button and a modal to display the tree instead of doing it on the page. 

Sorry for the long post but If you've read all of it I'd appreciate some feedback. And anyone that is interesteed in helping testing out the first version of this just let me know. 

Set<String> setname = new Set<String>();
     for(Opportunity opp : Trigger.New)
     {
         setname.add(opp.Name);
     }
    List<Opportunity> opplist = [SELECT ID, Name, StageName From Opportunity Where Name In:setname And (stageName='closed won' OR stageName='closed lost')];
    Map<String, Opportunity> mapopp = new Map<String, Opportunity>();
    Map<String, Opportunity> mapopen = new Map<String, Opportunity>();
    for(Opportunity opploop : opplist)
    {
       mapopp.put(opploop.Name, opploop);
       mapopen.put(opploop.StageName, opploop);
    }
    for(Opportunity oppnew : Trigger.New)
    {
        if(mapopp.containsKey(oppnew.Name) && mapopen.containsKey(oppnew.StageName))
        {
            oppnew.addError('same name with two opportunity');
        }
    }
This trigger is working but my doubt another way doing that type of question 

In Sweden and some other parts of Europe the week numbering is often using the ISO 8601 standard that says that the first week of the year is the first week with at least 4 days in the new year. Some consequences of this is that the final dates of one year can be week 52, 53 or even week 1( in the following year). The same goes for the first dates of a year.

 

To be able to do weekly comparison report Year-to-Year with this weeknumbering it is necessary to create custom fields for the opportunity that calculates the Year and Week number for a given close date. Here is one solution using three custom formula fields.

 

Field: Global Sales Report Weekday

Description:  Day 1 = Sunday, 2 = Monday....., 7=Saturday

Formula:  MOD( CloseDate  - DATE(1900, 1, 7), 7)+1

 

Field: Global Sales Report Week Year

Formula: YEAR(CloseDate + (MOD(8- Global_Sales_Report_Weekday__c ,7)-3))

 

Field: Global Sales Report Week

Formula:

FLOOR((CloseDate - DATE( Global_Sales_Report_Week_Year__c ,1,1) +
MOD(
(MOD( DATE(Global_Sales_Report_Week_Year__c,1,1) - DATE(1900, 1, 7), 7)+1)
+1,7)-3) / 7 + 1)

 

Hope this can be of use for anyone else

 

Peter Baeza

InfoAction AB, Sweden