Query to fetch the users from the selected groups

[Former Member]

Hi Experts,

We have a requirement to fetch the users from the selected group using the query.

I have referred FCI_MAS_PURCHASING_GROUP and FCI_UPP_USER_ACCOUNT tables to fetch the user list for particular group but no luck.

Kindly provide your valuable suggestions to fetch the users from selected group.  Please suggest table name from which will be able to get members of the group.

Thanks in Advance!!

Regards,

Lava

[Former Member · Accepted solution]

Hello Lava,

The tables which you were using are correct. Its just the join you need to modify.

Try with below query:

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_MAS_PURCHASING_GROUP T1 LEFT OUTER JOIN <%SCHEMA%>.FCI_UPP_USER_ACCOUNT T2 ON T1.OBJECTID=T2.DEFAULT_GROUP_OBJECT_ID

This should solve your issue.

Regards,

Vignesh

[Former Member · Accepted solution]

Hi Vignesh,

Thanks for the quick response.

If I use above query string I will get the users list where selected group is user's default group.
In some cases user might be added in more than one group.

My requirement is fetch the users list for the selected group.

Let me know if you need more information.

Regards,

Lava

[Former Member · Accepted solution]

Lava,

You can try with the below query:

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_MAS_PURCHASING_GROUP T1 LEFT OUTER JOIN <%SCHEMA%>.FCI_UPP_GROUP_REF T2 ON T1.OBJECTID=T2.GROUP_OBJECT_ID LEFT OUTER JOIN <%SCHEMA%>.FCI_UPP_USER_ACCOUNT T3 ON T3.OBJECTID=T2.PARENT_OBJECT_ID WHERE T1.DISPLAY_NAME LIKE 'XYZ Group'

This will pull all the users present in the 'XYZ Group'

Regards,

Vignesh

[Former Member · Accepted solution]

Hi Vignesh,

With the above query I am able to fetch the users for selected group.

Thanks you so much vignesh for timely help.

Regards,

Lava