Hello,
I am trying to create a lookup for team users filtered by team and team role however I am having difficulties as the member_role_mapping table does not contain usernames and so I can't create a lookup back to CF_Users.
Any suggestions?
Hello,
I am trying to create a lookup for team users filtered by team and team role however I am having difficulties as the member_role_mapping table does not contain usernames and so I can't create a lookup back to CF_Users.
Any suggestions?
Hello, we were able to solve this using a SQL View to the different Laserfiche Tables Governing Team Roles, User Role Mapping, Teams and CF_Users. The script is below:
SELECT T.[id] TeamId
,T.[name] TeamName
, TR.name TeamRoleName
, TR.id TeamRoleId
, TM.user_id TeamMemberUserId
, U.displayname
FROM [NSDForms].[dbo].[teams] T
LEFT OUTER JOIN [dbo].[team_roles] TR on T.id = TR.team_id
LEFT OUTER JOIN [dbo].[team_member_team_role_mapping] TRM on TRM.team_role_id = TR.id
LEFT OUTER JOIN [dbo].[team_members] TM ON TM.team_id = T.id AND TM.id = TRM.team_member_id
LEFT OUTER JOIN [dbo].[cf_users] U ON U.user_id = TM.user_id