FORMS Custom View

asked on December 13, 2024 • Show version history

In Forms Team Management, we have defined teams that should the Team name (teams.name) in the list.

When I click on the team name, the members of the team are listed. The Display Name (cf_users.displayname) is shown on the screen.

In v10.4 we created a view to use in a form.

v10.4 VIEW SQL
SELECT DISTINCT TOP (100) PERCENT dbo.cf_users.displayname, dbo.cf_users.username, dbo.cf_users.email, dbo.cf_users.user_id, dbo.team_members.user_id AS Expr1, dbo.team_members.team_id, dbo.teams.id, dbo.teams.name
FROM dbo.cf_users INNER JOIN
dbo.team_members ON dbo.cf_users.user_id = dbo.team_members.user_id INNER JOIN
dbo.teams ON dbo.team_members.team_id = dbo.teams.id
WHERE (dbo.teams.is_deleted = 0)
AND (dbo.team_members.leave_date IS NULL)
AND (dbo.team_members.member_rights = 1 OR dbo.team_members.member_rights = 3)
GROUP BY dbo.cf_users.displayname, dbo.cf_users.username, dbo.cf_users.email, dbo.cf_users.user_id, dbo.team_members.user_id, dbo.team_members.team_id, dbo.teams.id, dbo.teams.name
ORDER BY dbo.cf_users.displayname

After upgrading to v11, the team_members.user_id is no longer a column that can be used in a join.

What tables need to be joined in order to create a view that will contain cf_users.displayname, cf_users.username, cf_users.email, cf_users.user_id, teams.id, teams.name

0 0

SELECTED ANSWER

replied on December 15, 2024

Hi Mark,

In Forms 11 and later the column [user_id] in table [team_members] is renamed to [user_group_id] as we supported adding groups as team members.

Also column [type] is added to indicate whether the member is user or group.

You can filter the table by [type] = 0 and use the [user_group_id] column for finding users.

1 0

replied on December 16, 2024

Hello Rui,

Nice simple column update in the view. This is working as we expect.

Thank you for the quick response and knowledge share!

Kind regards,

Mark

0 0

replied on February 11

@████████would you mind sharing your updated query?

0 0

replied on February 12

1. dbo.team_members.user_id was updated to dbo.team_members.user_group_id in the SELECT, FROM, and Group By elements

2. An additional criteria was also added: AND dbo.team_members.type = '0'

SELECT DISTINCT TOP (100) PERCENT dbo.cf_users.displayname, dbo.cf_users.username, dbo.cf_users.email, dbo.cf_users.user_id, dbo.team_members.user_group_id AS user_group_id, dbo.team_members.team_id, dbo.teams.id, dbo.teams.name FROM dbo.cf_users INNER JOIN dbo.team_members ON dbo.cf_users.user_id = dbo.team_members.user_group_id INNER JOIN dbo.teams ON dbo.team_members.team_id = dbo.teams.id WHERE (dbo.teams.is_deleted = 0) AND (dbo.team_members.leave_date IS NULL) AND (dbo.team_members.member_rights = 1 OR dbo.team_members.member_rights = 3) AND dbo.team_members.type = '0' GROUP BY dbo.cf_users.displayname, dbo.cf_users.username, dbo.cf_users.email, dbo.cf_users.user_id, dbo.team_members.user_group_id, dbo.team_members.team_id, dbo.teams.id, dbo.teams.name ORDER BY dbo.cf_users.displayname

1 0

Question

Question

Answer

Replies

Sign in to reply to this post.