function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
F SmoakF Smoak 

cannot retrieve user ids having specific territory names in dev console query editor

I am trying to retrieve user IDs which do not belong to those territories whose names starts with IT_CH, but getting error "Unknown error parsing query" in dev console query editor.
Please note I need to use this as a single query and not via executing a class having for loops traversing from Territory to UserTerritory to User:

Select Id from User where Id in (Select UserID from UserTerritory where NOT TerritoryID.name LIKE 'IT_CH%'))

Please help.
Boss CoffeeBoss Coffee
Correct me if I'm mistaken, but should the following be changed?
UserTerritory -> UserTerritory2Association
TerritoryId.name -> Territory2.Name

Also, that "Unknown error parsing query" is coming from that extra ) at the very end of your query. 
Try the following: 
Select Id from User where Id in (Select UserID from UserTerritory2Association where NOT Territory2.name LIKE 'IT_CH%')
F SmoakF Smoak
silly mistake!! but when I remove extra ) it shows below error:

UserID from UserTerritory where NOT TerritoryID.name LIKE 'IT_CH%')
                                    ^
ERROR at Row:1:Column:77
Didn't understand relationship 'TerritoryID' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.


I did try with User2Territoryassociation earlier but I got below error:
where Id in (Select UserID from UserTerritory2Association where
                                ^
ERROR at Row:1:Column:53
sObject type 'UserTerritory2Association' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. Please reference your WSDL or the describe call for the appropriate names.

Could you plese help solve this?
Boss CoffeeBoss Coffee
Try Territory2.Name instead of using TerritoryId.name.
F SmoakF Smoak
Still giving error  :(
Boss CoffeeBoss Coffee
What is the error? Can you give a screenshot of the query and the error displayed?
F SmoakF Smoak
User-added image
F SmoakF Smoak
User-added image
Boss CoffeeBoss Coffee
Try Territory.Name.
F SmoakF Smoak
ried this earlier too, not working giving same error
Boss CoffeeBoss Coffee
I am unable to test this in my own org because UserTerritory is being retired and its been replaced by UserTerritory2Association, so please excuse my shots in the dark.

In Developer Console, try File > Open Resource > UserTerritory and screenshot it to see what fields there are.
F SmoakF Smoak
there are only two fields which are of use territoryid and userid which I used in my query but it didn't help. Cannot understand why this would not traverse to give results.
Boss CoffeeBoss Coffee
Are you able to make the following queries by themselves? 
Select UserID, TerritoryId from UserTerritory
Or: 
Select UserID, TerritoryId from UserTerritory where NOT Territory.Name LIKE 'IT_CH%'
F SmoakF Smoak
1st query is simple one so thats possible and returning results, but in the second one, Query Editor cannot understand Territory relationship in where clause 
Boss CoffeeBoss Coffee
After a bit of searching, it doesn't seem like UserTerritory supports traversing relationships. The newer UserTerritory2Association does support such queries, but that won't be much help in this scenario. Is there a reason you cannot use Apex for your use case?
F SmoakF Smoak
I did search and got those results too that "UserTerritory" is a special object which cannot be joined with other objects to traverse and retrive information.
This Query will actually be a part one of where clause of my original lengthy query hence I will require this as soql query :(
Boss CoffeeBoss Coffee
Could I get more clarification on what you mean by needing it as a SOQL query? Where and how are you using it?
F SmoakF Smoak
I am using it in custom object to store this query as a field and every week the batch runs to execute this query and removes and non IT_CH users from a permission setassignment
Boss CoffeeBoss Coffee
May I ask what the batch is? Could it not contain the logic itself instead of needing to check the field of a custom object for the query?