Question

Question

Find the Role in a Team for A Previous Submitter

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?

Replies

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.