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

Discussion

Discussion

Team/Role/User Views That Can Be Set-Up on LFForms Database

Forms Version 10 How To
Updated March 28, 2024
posted on February 24, 2021 • Show version history

I was asked on another post (https://answers.laserfiche.com/questions/184018/Feature-Request-Forms-Teams-and-LFDS-Users-and-Groups-as-a-Data-Source) to share some views that I have set-up in my LFForms database in order to query items about LFForms Teams, Roles, and Users for field population/form routing.

I decided to post these as a separate discussion post in order to keep from crowding that other post.

These were all created in forms 10.2, but I assume they'll work in later versions assuming the structure of teams/roles/users isn't drastically changed.

Feel free to post your own code here as well!

3 0
replied on February 15, 2022

With Forms 11 Update 2 which supports add groups to teams, the column “user_id” has been updated to "user_group_id" to include the groups added to the team as well. You need to update “user_id” to ""user_group_id" to the above queries to get the users added to the team directly. If you want to get the list of all users in teams include the users added to team by adding their group, you can use following query:

SELECT
t.id AS team_id,
t.name AS team_name,
t.description AS team_description,
u.username,
u.displayname,
u.email
FROM team_members AS tm
LEFT JOIN teams AS t ON t.id = tm.team_id
LEFT JOIN cf_users AS u ON u.user_id = tm.user_group_id
WHERE
tm.leave_date IS NULL
AND t.is_deleted = 0
AND tm.member_rights <> 0
AND tm.type = 0
UNION
SELECT
t.id AS team_id,
t.name AS team_name,
t.description AS team_description,
u.username,
u.displayname,
u.email
FROM team_members AS tm
LEFT JOIN teams AS t ON t.id = tm.team_id
LEFT JOIN cf_usergroups_users_mapping AS g ON g.group_id = tm.user_group_id
LEFT JOIN cf_users AS u ON u.user_id = g.user_id
WHERE
tm.leave_date IS NULL
AND t.is_deleted = 0
AND tm.member_rights <> 0
AND tm.type = 1

 

3 0
replied on March 28, 2024

Thank you for this. I'm trying to ensure that the list I use for a lookup only contains users that can have the task assigned via team filters. Meaning, they have to be marked as a Team Member. It looks like the member_rights values I need are:

1: just a team member

3: team member and team manager

And I should not include rights = 2 because that is team manager only (which doesn't allow for task assignment). Is that correct?

Side note: I really wish there was a "people picker" field type. I'm shocked that such a field still doesn't exist in Forms.

0 0
replied on February 24, 2021

Thanks Matt
@SteveKnowlton

1 0
replied on February 24, 2021 • Show version history

Listing All Users by Roles and Teams in LFForms:

SELECT
  t.id AS team_id, 
  r.id AS role_id,
  t.name AS team_name, 
  r.name AS role_name,
  t.description AS team_description, 
  r.description AS role_description,
  u.username,
  u.displayname,
  u.email
FROM team_members AS tm
  LEFT JOIN teams AS t ON t.id = tm.team_id
  LEFT JOIN cf_users AS u ON u.user_id = tm.user_id
  LEFT JOIN team_roles AS r ON r.team_id = t.id
  LEFT JOIN team_member_team_role_mapping AS trm ON trm.team_member_id = tm.id AND trm.team_role_id = r.id
WHERE 
  tm.leave_date IS NULL
  AND t.is_deleted = 0 
  AND tm.member_rights <> 0
  AND trm.team_role_id IS NOT NULL

 

0 0
replied on February 24, 2021 • Show version history
  • You're missing team_member_team_role_mapping on this one.
  • I haven't been checking team_members.member_rights--what does a 0 there mean?
  • I also check for team_roles.is_deleted = 0, but not sure if that's impactful.
1 0
replied on February 24, 2021

Unless I'm mistaken, when team_members .member_rights is 0 it has to do with users who have been removed as a member on the team.  When I search for those, I mostly see myself on teams where I'm the administrator but not an actual team member.

And you are definitely right about the missing reference to the team_member_team_role_mapping table - I realize that I shared an earlier version of the query that was incomplete.  Oops!

I'll edit the query on the post.

0 0
replied on February 24, 2021 • Show version history

Listing All Roles and Teams in LFForms:

SELECT
  t.id AS team_id, 
  r.id AS role_id,
  t.name AS team_name, 
  r.name AS role_name,
  t.description AS team_description, 
  r.description AS role_description
FROM teams AS t
  LEFT JOIN team_roles AS r ON r.team_id = t.id
WHERE 
  t.is_deleted = 0

 

0 0
replied on February 24, 2021

Listing Managers on Teams in LFForms:

SELECT
  t.id AS team_id,
  t.name AS team_name,
  t.description AS team_description,
  u.username,
  u.displayname,
  u.email
FROM team_members AS tm
  LEFT JOIN teams AS t ON t.id = tm.team_id
  LEFT JOIN cf_users AS u ON u.user_id = tm.user_id
WHERE
  tm.member_rights = 3
  AND tm.leave_date IS NULL
  AND t.is_deleted = 0
  AND tm.member_rights <> 0

 

0 0
replied on February 24, 2021

Listing all Users in Teams in LFForms:

SELECT
  t.id AS team_id,
  t.name AS team_name,
  t.description AS team_description,
  u.username,
  u.displayname,
  u.email
FROM team_members AS tm
  LEFT JOIN teams AS t ON t.id = tm.team_id
  LEFT JOIN cf_users AS u ON u.user_id = tm.user_id
WHERE
  tm.leave_date IS NULL
  AND t.is_deleted = 0
  AND tm.member_rights <> 0

 

0 0
replied on February 24, 2021

Listing all Teams in LFForms:

SELECT
  t.name,
  t.description,
  COUNT(m.user_id) AS user_count
FROM teams AS t
  LEFT JOIN team_members AS m ON m.team_id = t.id
  LEFT JOIN cf_users AS u ON u.user_id = m.user_id
WHERE
  t.is_deleted = 0
GROUP BY t.name, t.description

 

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

Sign in to reply to this post.

Details
Related Posts
Loading ...