Error when trying to display the configurable field properties in System Manager
Error when trying to display the configurable field properties in System Manager
Error messages/Warnings
The column does not have unique values
Reason
The SQL query used in the formula returns non-unique results.
Resolution
The SQL query must return unique values, as these values will be listed in the UI for the user to pick a value.
Replace the SQL below:
SELECT Resources.ResourceId, Resources.Name FROM Resources WITH (NOLOCK) , ResourceRole, Role where ResourceRole.ResourceId = Resources.ResourceId and ResourceRole.RoleId = Role.RoleId and Role.Name in ('Role 1', 'Role 2', 'Role 3')
... by the following which eliminates duplicate values:
SELECT Resources.ResourceId, Resources.Name FROM Resources WITH (NOLOCK) where Resources.ResourceId in
(
select distinct rr.resourceid from ResourceRole rr(nolock)
inner join Role r (nolock) on r.RoleId=rr.RoleId
where r.Name in ('Role 1', 'Role 2', 'Role 3')