You are viewing limited content. For full access, please sign in.

Question

Question

How to Populate Users in Forms from a Team Role Lookup

asked on December 19, 2018

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?

 

 

Screen Shot 2018-12-19 at 7.59.56 PM.png
Screen Shot 2018-12-19 at 7.13.46 PM.png
Screen Shot 2018-12-19 at 8.07.48 PM.png
0 0

Answer

SELECTED ANSWER
replied on December 19, 2018

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

3 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.