In Kentico 10, I've created a custom table for an employee directory, with columns for name, job title, department, etc.
Now I want to display a list of all departments. So I've added a custom table repeater web part to my page, set its data source to my custom table, and assigned a transformation to render the value in the department column of each row.
This all works as is - but because the repeater iterates over every row in the table, the page ends up displaying multiple duplicate department values. For example, if there are 10 employees in the table whose department is "Accounting," then "Accounting" is output 10 times, whereas I only want to show it once.
How can I modify this so that duplicate department values are filtered out, so I'm left with just a list of unique departments? I imagine I have to use the filter web part somehow, but the Kentico documentation only provides one very specific example that doesn't apply to this scenario.
So I solved this in kind of a roundabout way, but it works.
Rather than try to filter out the duplicates, I created a second custom table called Departments, and entered all the department names as individual items.
Then I changed the data source in my web part to point to the Departments table instead of the Employee Directory table. This gave me a list of departments without any duplicates.
Finally, I changed the data source of the Department field (column) in the Employee Directory table to a SQL query that pulls the data from the Department table:
SELECT Department FROM customtable_Departments
Now when I add or edit an item in the Employee Directory table, I can choose the department from a dropdown menu, which stays in sync with whatever is in the Department table.