How to write business roles based on fields between associated tables?

[shiz]

I have the following business roles.

var groupName = "G2U%";

            var typeName = "Organization";

            var f = 1;  

            //現在のキャラクタの下にあるすべてのuserを取得して、に戻ります

            var userGroup = new GlideRecord("sys_user_grmember");

            userGroup.addQuery("user",u);  

            userGroup.addQuery("group.name","NOT LIKE", groupName);        

            userGroup.addQuery("sys_user_group_type.name",typeName);

            //userGroup.addQuery("group.name","<>",exceptGroup_1);

            //userGroup.addQuery("group.name","<>",exceptGroup_2);

            userGroup.query();

            while(userGroup.next()){

                var strGroup = userGroup.group;

                    // get group's user info

                    var g_member = new GlideRecord("sys_user_grmember");

                    g_member.addQuery("group",strGroup);

                    g_member.addQuery("user","<>",u);

                    g_member.query();

                    while(g_member.next()){                    

                        var strUser = g_member.user.sys_id.toString();

                        answer[f] = strUser;

                        f++;

                    }

            }          

But, userGroup.addQuery("sys_user_group_type.name",typeName) ：This code is not effective.

The following are the field definitions for the table:

 How to modify the code to filter data with type 'organization' in the 'sys_user_group' table？

[shiz]

OK,Let me tell you my needs.

I have the following business roles.

var groupName = "G2U%";

            var typeName = "Organization";

            var f = 1;  

            //現在のキャラクタの下にあるすべてのuserを取得して、に戻ります

            var userGroup = new GlideRecord("sys_user_grmember");

            userGroup.addQuery("user",u);  

            userGroup.addQuery("group.name","NOT LIKE", groupName);        

            userGroup.addQuery("sys_user_group_type.name",typeName);

            //userGroup.addQuery("group.name","<>",exceptGroup_1);

            //userGroup.addQuery("group.name","<>",exceptGroup_2);

            userGroup.query();

            while(userGroup.next()){

                var strGroup = userGroup.group;

                    // get group's user info

                    var g_member = new GlideRecord("sys_user_grmember");

                    g_member.addQuery("group",strGroup);

                    g_member.addQuery("user","<>",u);

                    g_member.query();

                    while(g_member.next()){                    

                        var strUser = g_member.user.sys_id.toString();

                        answer[f] = strUser;

                        f++;

                    }

            }          

Now ,  I have a group member table [sys_user_grmember], and now I want to filter out all the personnel information of the group members under the current user.

The filtering condition is that the name of the group table [sys_user_group] does not start with the group name "G2B%".
The 'type' in the group table must be of the 'organization' type
However, the 'type' field in the 'group' table is referenced from the 'group type' table, and 'sys_user_group' is referenced from the 'group' table.

So how can I modify the code to meet my needs

[shiz]

Please refer to the two pictures I posted at the top for specific table reference relationships.

[shiz]

Can you give me a specific solution？

[Shraddha Kadam]

I am checking...

[Shraddha Kadam · Accepted solution]

Hello @shiz ,

Use the below code. This works for me.

var groupName = "G2U%";
var typeName = "Organization";
var f = 1;
var answer = [];
//現在のキャラクタの下にあるすべてのuserを取得して、に戻ります

var userGroup = new GlideRecord("sys_user_grmember");
userGroup.addQuery("user", gs.getUserID());
userGroup.addQuery("group.name", "NOT LIKE", groupName);
userGroup.addQuery("group.type", 'f3dfc27ac32222103e1a76d4e40131c3'); // sys_id of organization

//userGroup.addQuery("group.name","<>",exceptGroup_1);
//userGroup.addQuery("group.name","<>",exceptGroup_2);
userGroup.query();

while (userGroup.next()) {
    var strGroup = userGroup.group;

    // get group's user info
    var g_member = new GlideRecord("sys_user_grmember");
    g_member.addQuery("group", strGroup);
    g_member.addQuery("user", "<>", gs.getUserID());
    g_member.query();
    while (g_member.next()) {
        var strUser = g_member.user.getDisplayValue().toString();
		gs.info("sys_id - "+strUser);
        answer[f] = strUser;
        f++;
    }
}

Output -

Group with members -

Script output