+ Start a Discussion
BenzyBenzy 

SOQL to get list of users that have the same City as current user

Hi,

I currently have an SOQL query that gets a list of users who are of a specific profile and are active, which looks like this:
[SELECT Id, Name FROM User WHERE Profile.Name = 'Pollinators' AND IsActive = TRUE ORDER BY Name ASC]

This list is used to populate a dynamic picklist that managers user to access filtered reports. However, my business is expanding into new cities and so I want to also filter this list by showing only users who are in the same city as the manager.

I figured the best way to do this is to match the City field value on the User's record page. Upon doing some reading, I found a list of UserInfo Methods, and I can't see anything in there for City.

Can anyone help with how I might acheive this?
Thanks
 
Best Answer chosen by Benzy
Shaijan ThomasShaijan Thomas
Try this
Id Mgrid = [select Id,ManagerId from User where id =: userinfo.getuserid()].ManagerId;
string MgrCity = [select City from User where id =: Mgrid].city;

list<user> u = new list<user>();
u = [SELECT Id, Name FROM User WHERE City =: MgrCity AND Profile.Name = 'Pollinators' AND IsActive = TRUE ORDER BY Name ASC];
Thanks
Shaijan

All Answers

Sri549Sri549
Hello Benzy,
   City value have to be selected manually or Is there any Consideration.Could you please tel me Ellaboratedly.
  
Thanks
Srinivas

 
BenzyBenzy
Hi Srinivas

I want to query the City of the current user and then filter the results based on this. So something like UserInfo.getCity()
Shaijan ThomasShaijan Thomas
Try this
Id Mgrid = [select Id,ManagerId from User where id =: userinfo.getuserid()].ManagerId;
string MgrCity = [select City from User where id =: Mgrid].city;

list<user> u = new list<user>();
u = [SELECT Id, Name FROM User WHERE City =: MgrCity AND Profile.Name = 'Pollinators' AND IsActive = TRUE ORDER BY Name ASC];
Thanks
Shaijan
This was selected as the best answer
Iqrar AhmedIqrar Ahmed

This will solve your problem

string CityName=' ';
List<User> user =[select city from user where id=:userinfo.getuserid() limit 1];

if(user.size()>0){
CityName=user[0].City;
}

List<Users> userList=[SELECT Id, Name FROM User WHERE city=:CityName AND IsActive = TRUE ORDER BY Name ASC]



Another way if you want to Fetch User By city Name Using Map

Map<String,List<user>> Cityusers=new Map<String,List<user>>();
List<String> cityList=[select city from user];
for(List l:cityList){
List<Users> userList=[SELECT Id, Name FROM User WHERE city=:l AND IsActive = TRUE ORDER BY Name ASC]
Cityusers.put(l,userList);
}

This will be result in

Karachi =>User....
                User....
         User....
                User....

Islamabad=>User....
                   User....
            User....
                   User....


Lahore=>User....
              User....
       User....
              User....


Regards 
IQRAR AHMED
BenzyBenzy
Hi Shaijan,

Thanks that worked perfectly. I want to extend this exclude certain profiles from the City filter. Eg, I want an Administrator to see the list as if the City filter was not in there. But for every other profile, they should be subject to the City filter.

Again from reading I think I need to use a TYPEOF clasue in the statement, which would include an if/else type logic? Am I on the right path?
Shaijan ThomasShaijan Thomas
Can you make it as best answer, if it usful