Apex Code Development You need to sign in to do that
Don't have an account?
Benzy 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
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
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
City value have to be selected manually or Is there any Consideration.Could you please tel me Ellaboratedly.
Thanks
Srinivas
I want to query the City of the current user and then filter the results based on this. So something like UserInfo.getCity()
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 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
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?