Find the Role in a Team for A Previous Submitter

asked on February 13, 2018 • Show version history

I have a Forms process where it is approved in step A and then again in step B but the person assigned the task in B is dependent on who approved it in A. Thus I've setup:

Team A (assigned to Step A)
- Role 1
  - Mary
- Role 2
  - Mike
- Role 3
  - John
Team B (assigned to Step B)
- Role 1
  - Tom
- Role 2
  - Susan
- Role 3
  - April

Thus if the person for Step A in Team A/Role 1 approves the form then it needs to be assigned in Step B to Team B/Role 1 and likewise for each of the other Teams/Roles. In creating filters I see where I can determine who the previous submitter was but is there a way to see what role that previous submitter has within a specific Team? I understand I could create this logic via a SQL table & look-ups but the end-users prefer to stay out of SQL and maintain everything in Forms. If what I am doing is not possible, any other suggestions?

0 0

replied on February 16, 2018

It appears there is currently no way. What I have found is to query the Forms tables directly but I am unsure this will stay working over time due to changes in the table/database structure. For anyone looking for this query as it works in 10.3.

SELECT        B.DisplayName, B.UserName, B.Email AS UserEmail, 
     C.name AS TeamName, C.description AS TeamDescription,E.name AS RoleName
FROM            dbo.team_members AS A INNER JOIN
                dbo.cf_users AS B ON A.user_id = B.user_id INNER JOIN
                dbo.teams AS C ON A.team_id = C.id INNER JOIN
		dbo.team_member_team_role_mapping AS D ON A.id=D.Team_member_ID INNER JOIN
		dbo.team_roles AS E ON D.team_role_id=E.id
WHERE        (B.is_activated = 1) AND (C.is_deleted = 0) AND (A.leave_date IS NULL) AND C.Name='Team Name'

For this just put in your Team Name at the end and it should pull back the the user's display name, user name, user's email, Team Name, Team Description, and the user's corresponding Role Names (multiple rows if they are in more than 1). Additionally it will only include active non-deleted members who are currently in that team.

Please let me know if anyone has found a better way to determine what roles a user is a member of.

1 0

Question

Question

Find the Role in a Team for A Previous Submitter

Replies

Sign in to reply to this post.