[SalesForce] How to traverse territories in Salesforce using SOQL query

I have a field on contact object called 'Region' (Text formula field) whose end value would be in the range of 1-5. On the other hand, I have a territory model setup in my Salesforce instance. There are 5 territories with the Type Region that come on level 3 in model hierarchy. Here is my territory hierarchy.

Territory Hierarchy

I have a field on contact object called 'Region' (Text formula field) whose end value would be in the range of 1-5. On the other hand, I have a territory model setup in my Salesforce instance. There are 5 territories with the Type Region that come on level 3 in model hierarchy. Here is my territory hierarchy Territory hierarchy

Here are the five region type territories
Territories of Type Region

What I want is that if the value in Region field is 1 on contact page, it should go to the Region 1 territory and check the assigned users in the region 1. Similarly for other regions. I am taking an example where Region value on contact object is 3. In that case, I want the code to go to Region 3, Traverse the assigned users in region 3 and check the "Role in Territory field" in assigned user.After it checks the "Role in Territory" which will either be Regional Counselor L-Z or Regional Counselor A-K (as shown in the screenshot). Then it compares the "last name" field on the contact object. If the user's last name starts with L-Z then in Region 3(in our example) in the assigned users, the one who has the Role "Regional Counselor L-Z" should be assigned to the "Owner" lookup field on the contact object. if last name starts with A-K then the user with the role "Regional Counselor A-K" should be populated in the "Owner" field on contact object.

Screenshot attached for Region 3.
Region 3 Territory

I came up with this so far

// Fetch Region 3 data 
Territory2 Region3 = ([SELECT Id, Name from Territory2 WHERE Name='Region 3']); 
System.debug(Region3); 

// Fetch Assigned Users to Region 3 
List<UserTerritory2Association> utList2 = new List<UserTerritory2Association>([SELECT Id, RoleInTerritory2, Territory2Id, UserId from UserTerritory2Association WHERE Territory2Id= Region3.Id ]);
System.debug(utList2);

it gives me the following error

Compile Error: Unexpected token 'Region3.Id'.

Best Answer

I rewrote your code:

// Fetch Region 3 data 
List<Territory2> Region3 = ([SELECT Id, Name from Territory2 WHERE Name='Region 3']); 
System.debug(Region3[0]); 

// Fetch Assigned Users to Region 3 
List<UserTerritory2Association> utList2 = new List<UserTerritory2Association>([SELECT Id, RoleInTerritory2, Territory2Id, UserId from UserTerritory2Association WHERE Territory2Id =:Region3.Id ]);
System.debug(utList2);

That should do it. You were missing a colon. Plus, even if your query will return only one record, it's a good idea to query to a list anyway and then to take the first item in that list.

Related Topic