Get list of users within Team - advanced

[paulfisk]

I have the need to get the list of users and teams within a given team.

There is a lot of examples that will show the total list of users within a team, but I want to be able to visually show something like this:

"Main Team Name"

- Top level User1

- Top level User2

- Top level User 3

- VP Team

- VP Name 1

- VP Name 2

- Managers Team

- Manager 1

- Manager 2

Any ideas how to identify the teams within teams?

Paul Fisk

Avante Solutions Inc.

[rchavez]

You will need to look into the SECCODEJOINS table instead of the SECRIGHTS table.

  The Group you are querying for would be the PARENTSECCODEID, and the IDs (both of Teams and Users) will be listed under the CHILDSECCODEID colum.

  I don't recall if these table will list all Users or only the ones directly added to the Team, so you may need to Experiement.

  Also, as per the Field Names, the ChildSeccodeID columns will include the Users's SECCODEID (FROM SECCODE WHERE SECCODETYPE = 'G').

  I would recommend you setup a few test scenarios and Reverse Engineer it from there.

[paulfisk]

Perfect. That was the missing link for me.
The query below will show all members of a given team:
The records with SECCODETYPE = G will be the teams belonging to the parent team. The records with SECCODETYPE = U will be users in the parent team.

SELECT TeamMembers.*
From sysdba.SECCODEJOINS sj
inner join sysdba.SECCODE s on s.SECCODEID = sj.PARENTSECCODEID
inner join sysdba.SECCODE TeamMembers on TeamMembers.SECCODEID = sj.CHILDSECCODEID
where s.Seccodedesc = 'BDEV'

[rchavez]

Glad I was able to help!!

[RJLedger]

Only one problem w/this.. it will not work on any remote because the seccodejoins table is empty on all remotes - by design. It has always been this way as the only place SalesLogix uses it in the admin app to display/manage security. It's not used in any normal client app.