-
ChatterFeed
-
0Best Answers
-
1Likes Received
-
0Likes Given
-
1Questions
-
3Replies
Handling dynamic hiearchy trees effectively
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.
- Peter.Baeza
- July 23, 2020
- Like
- 1
Handling dynamic hiearchy trees effectively
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.
- Peter.Baeza
- July 23, 2020
- Like
- 1
Trigger opportunity if it has two open opportunity related stop the user not created that opportunities
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
- Pankaj Yadav 29
- July 23, 2020
- Like
- 0
ISO Week Calculation
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
- pbaeza
- March 15, 2010
- Like
- 3