Filter Employees Datasource (Users Datasource) by Role

Kim Driggers asked on September 7, 2016 20:30

Using 8.2 and wanting to filter the Employees Datsource by Roles (departments) to create individual department contact lists. I have the Employees Datasource displaying all users in a Repeater webpart. I am not sure of how to filter by Role. Can anyone assist?

Recent Answers


Joshua Adams answered on September 7, 2016 20:49

You may want to start with the filter webpart if using portal engine. Not sure your exact requirements, but that could be a good place to begin. What is your employees table? Is it a page type, custom table, module class, etc? This may help figure out what your options are.

0 votesVote for this answer Mark as a Correct answer

Kim Driggers answered on September 7, 2016 21:06

I should have clarified that I am using the Intranet Portal - Employees template. This is not our own Employees database. I know we need to filter but I don't know what to filter it on. There is no detail in the online documentation for the Users database.

0 votesVote for this answer Mark as a Correct answer

Martin Hejtmanek answered on September 8, 2016 12:06

Hi Kim, not sure if the requirement is to have a selector based filter or just static condition based on some context value.

Here is how you can create custom filter if you need a selector based one - https://docs.kentico.com/display/K9/Developing+custom+filters

If you need just static condition based on context, you just add it to data source where condition

The relation in database is

  • CMS_User (Employees) - CMS_UserRole (Role membership) - CMS_Role

So your where condition might look like:

UserID IN (SELECT UserID FROM CMS_UserRole WHERE RoleID IN (SELECT RoleID FROM CMS_Role WHERE RoleName = 'SilverPartners' AND SiteID = (SELECT SiteID FROM CMS_Site WHERE SiteName = 'CorporateSite')))
1 votesVote for this answer Mark as a Correct answer

Kim Driggers answered on September 8, 2016 14:33

HI Martin,

So to confirm what you posted, I place the code you have posted in the Where condition of my web part and would change the RoleName to the desired Role name that I wanted it filtered on. Your example would render only the SilverPartners role.

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on September 8, 2016 15:36

So, Kim, you would need to develop a webpart maybe with a simple dropdown list that brings in the roles so that you could filter based on that. Martin is showing you the where clause that you would need to generate, and you would fill in the blank with your controls value. The custom filter is very easy to create, I would suggest cloning the base kentico filter webpart and customizing that. Then all you need to do is change the textbox to a dropdown or whatever control you want, and filter based on that control. Add that to your controls where clause for the filter and then add the filtercode name to your datasource, so that they datasource uses that filter.

0 votesVote for this answer Mark as a Correct answer

Kim Driggers answered on September 8, 2016 15:48

Where do I find the structure of the User list so I know what to filter on? This is the Kentico User list.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.