function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Tyler Whitesides SDTyler Whitesides SD 

How can I bulkify enterprise territory management class that inherently is SOQL intensive?

I am having some trouble bulkifying a class that inherently hits the database quite a bit.  This works fine for a single record, but bulk operations fail due to the high number of SOQL queries.

The goal of this class is to provide a UserId given an AccountId and a "Role" (which is actually the MasterLabel on the assigned Territory Type). 

I will be using this in a trigger when the Account updates.  Essentially I am trying to create a trigger that will update 3  Lookup(User) fields to populate the "Territory Team" depending on what function a particular person serves for that Account, as determined by Enterprise Territory Management.  Each Account record has several assigned territories, and each one represents a role, plus the person serving that role.

I know that this is available on the "Assigned Territories" related list, but unfortunately that is not reportable and you cannot automate anything off of it (i.e. you cannot change the Owner of an Event on creation depending on Territory role).  I need this sort of automation, hence having the fields populate with userId, pulled from territories.
 
public class UserbyAccountandTerritoryRole {
    public static Id getUserbyAccountandRole(Id acctId,String role){
        Id SCUser;
            try {
        //With AccountId, get ObjectTerritory2Association records to get TerritoryIds
        //With TerritoryIds get Territory2 records to get Territory2TypeIds
        List<Territory2> territories = [SELECT Id,Territory2TypeId FROM Territory2 WHERE Id IN (SELECT Territory2Id FROM ObjectTerritory2Association WHERE ObjectId = :acctId)];
        //With Territory2TypeIds get Territory2Type records MasterLabels to filter down to 1 TerritoryId
          	List<Id> territorytypeIds;
            for(Territory2 t : territories){
                territorytypeIds.add(t.Territory2TypeId);
            }
            List<Territory2Type> types = [SELECT Id,MasterLabel FROM Territory2Type WHERE Id IN :territorytypeIds];

        //Find Territory Type matching the Role
        Id SCterritoryTypeId;
                for(Territory2Type i : types){
                    if(i.MasterLabel == role){
                        SCterritoryTypeId = i.Id;
                    }
            }
        //Match the Territory2Type with the original Territory to find the User
        Id finalTerritory;
            for(Territory2 x : territories){
                if(x.Territory2TypeId==SCterritoryTypeId){
                    finalTerritory = x.Id;
                }
            }    
        //With last TerritoryId get UserTerritory2Association records to get UserId
        SCUser = [SELECT UserId FROM UserTerritory2Association WHERE Territory2Id = :finalTerritory].UserId;
          	   }
    catch (Exception ex) {
        system.debug('Uh oh an error occurred!');
    }
                return SCUser;
    }
}


I would also love to hear if I am just going about this the wrong way.
Narender Singh(Nads)Narender Singh(Nads)
Hi Tyler,
I may be able to help you if could share your trigger code as well.
Tyler Whitesides SDTyler Whitesides SD
Thanks!  Here it is: 
 
trigger AccountRepresentativesfromTerritory on Account (before insert, before update) {
    for(Account a : Trigger.new){
        try{
            if(UserbyAccountandTerritoryRole.getUserbyAccountandRole(a.Id, 'Enterprise Account') != NULL){
                Id enterpriseAcct = UserbyAccountandTerritoryRole.getUserbyAccountandRole(a.Id, 'Enterprise Account');
                a.Solutions_Consultant__c = enterpriseAcct;
            }
            else{
                a.Solutions_Consultant__c = UserbyAccountandTerritoryRole.getUserbyAccountandRole(a.Id,'Solutions Consultant');
            }
            
            a.Gov_Solutions_Specialist__c = UserbyAccountandTerritoryRole.getUserbyAccountandRole(a.Id,'Government Solutions Specialist');
            a.Success_Manager__c = UserbyAccountandTerritoryRole.getUserbyAccountandRole(a.Id,'Success Manager');
        }
        catch(Exception error) {
            System.debug(error);
        }
    }
}