Unable to fetch roles through cart API

[Nikhitha Mohan]

We have created a custom table for counting departments and roles that a user can access through a request. Additionally, we have written a scheduled script to check the user's department in both the user table and the custom table. If any mismatches are found, a request should automatically be generated on behalf of that user. In the task, variables like 'department to be revoked' and 'role to be revoked' should be displayed, and these values should come from the custom table. If a user has two roles under the same department, both roles should be displayed in the 'role to be revoked' field. However, we are currently unable to fetch these roles from the custom table and display them in that particular field.

var gr = new GlideRecord('u_role_count');// u_role_count is custom table

gr.addQuery('u_status', 'Active');

//gr.addEncodedQuery('u_department_nameINDepartment A,Department B,Department C');

gr.query();

while (gr.next()) {

    var gr1 = new GlideRecord('sys_user');

    gr1.addQuery('name', gr.u_user_name);

    gr1.query();

    while (gr1.next()) {

        var sys_user_id = gr1.sys_id;

        var dept_user = gr1.department.name.toString();

        var dept_role = gr.u_department_name.toString();

        //var rolesID=gr.u_user_name.u_department_name.u_role_name.toString();

        var req_for = gr.u_user_name.toString();

        //roleList(dept_role, req_for);

        if (dept_user != dept_role)

        {

            var cartId = GlideGuid.generate(null);

            var cart = new Cart(cartId);

            //give sys_id of catalog item

            var item = cart.addItem('6877bf7697332110a000b6cfe153af05');// sys id of catalog

            cart.setVariable(item, "requester_name", sys_user_id);//requester_name is variable name in catalog

            cart.setVariable(item, "department_to_be_revoked", dept_role);// department to be revoked is variable in catalog

            //cart.setVariable(item, "role_to_be_revoked", roleList(dept_role, sys_user_id));

            var rc = cart.placeOrder();

            var reqRecord = new GlideRecord('sc_request');

            reqRecord.get(rc.sys_id);

            reqRecord.requested_for = req_for;

            reqRecord.update();

        }

    }

}

updateRITM(rc.sys_id); //call a function immediately to update the ritm.   This must be a nested function, otherwise inbound actions get weird.

// var dept_name_req;

// function roleList(dept, name) {

//     gr.addQuery('u_department_name', dept);// u_department_name is the column name in custom table

//     gr.addQuery('u_user_name', name);//u_user_name is the column name in custom table

//     gr.query();

//     while (gr.next()) {

//         return gr.u_role_name;

//        // gs.log(dept_name_req);

//     }

//     return dept_name_req;

// }

// //     //also, we're passing the sys_id of the request so we know what RITM to grab.

// //  }

function updateRITM(req) {

    var ritm = new GlideRecord('sc_req_item');

    ritm.addQuery('request', req); //req is what we passed from the previous function. the sys_id of the request.

    ritm.query();

    while (ritm.next()) {

        ritm.requested_for = req_for;

        //gs.getUserID(); //my ritm table separately tracks its own customer, since I don't use Request

        ritm.configuration_item = '';

        ritm.u_business_service = '';

        ritm.assignment_group = 'ddadd8360f7335003c10244be1050e2d'; 

        ritm.urgency = '1';

        ritm.priority = '2';

        ritm.impact = '1';

        ritm.update();

    }

}

Please refer the attachment and suggest a solution.......

[Nikhitha Mohan]

Hi @Tushar   ,

We are facing an issue when request is created for a user who has two different role under same department. Its creating total four requests in instead of 2 req. Previous code work fine but this issue we are facing now.

[Tushar]

Hi @Nikhitha Mohan  ,

below script should create only two requests for a user who has two different roles under the same department, instead of creating four requests as before.

var userDepartments = {};

var gr = new GlideRecord('u_role_count'); // u_role_count is the custom table
gr.addQuery('u_status', 'Active');
gr.query();

while (gr.next()) {
    var user = gr.u_user_name.toString();
    var department = gr.u_department_name.toString();

    if (user && department) {
        var key = user + '|' + department;
        if (!userDepartments[key]) {
            userDepartments[key] = true;
            createRequestForUserAndDept(user, department);
        }
    }
}

function createRequestForUserAndDept(user, department) {
    var cartId = GlideGuid.generate(null);
    var cart = new Cart(cartId);
    var item = cart.addItem('6877bf7697332110a000b6cfe153af05'); // sys_id of the catalog item

    var grUser = new GlideRecord('sys_user');
    if (grUser.get('name', user)) {
        var sys_user_id = grUser.sys_id;
        var req_for = grUser.name.toString();
        var dept_role = department;

        var rolesToRevoke = getRolesToBeRevoked(department, user);

        cart.setVariable(item, 'requester_name', sys_user_id); // requester_name is a variable in the catalog
        cart.setVariable(item, 'department_to_be_revoked', dept_role); // department to be revoked is a variable in the catalog
        cart.setVariable(item, 'role_to_be_revoked', rolesToRevoke); // role_to_be_revoked is a variable in the catalog

        var rc = cart.placeOrder();
        var reqRecord = new GlideRecord('sc_request');
        reqRecord.get(rc.sys_id);
        reqRecord.requested_for = req_for;
        reqRecord.update();
    }
}

function getRolesToBeRevoked(department, userName) {
    var rolesToRevoke = '';
    var grRoles = new GlideRecord('u_role_count'); // u_role_count is the custom table
    grRoles.addQuery('u_department_name', department);
    grRoles.addQuery('u_user_name', userName);
    grRoles.query();

    while (grRoles.next()) {
        if (rolesToRevoke !== '') {
            rolesToRevoke += ', ';
        }
        rolesToRevoke += grRoles.u_role_name.getDisplayValue();
    }

    return rolesToRevoke;
}

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar

[Arun Manoj1]

Hi Tushar,

we have two tables are there to check. for your previous one code generated all REQ for the

users in [u_role_count].But one issue find out that there is more than 2 REQ generated with same role.

example:

[u_role_count]

User Name	Department name	Expiry date	Role name	Updated	Status
Abraham Lincoln	Department A	31-07-2023	Role A1	26-07-2023 05:08	Active
Oma Duffy	Department A	20-07-2023	Role A2	26-07-2023 00:30	Active
Oma Duffy	Department A	27-07-2023	Role A1	26-07-2023 00:28	Active
Waldo Sisk	Department A	01-08-2023	Role A1	26-07-2023 00:13	Active
Waldo Sisk	Department A	27-07-2023	Role A2	26-07-2023 00:08	Active
Aileen Mottern	Department C	24-08-2023	Role C1	22-07-2023 06:29	Active
Adela Cervantsz	Department C	21-07-2023	Role C1	22-07-2023 06:26	Active
System Administrator	Department B	31-07-2023	Role B2	22-07-2023 06:21	Active

REQ genertaed by cart api result

REQ0011149	Oma Duffy	System Administrator	Approved	2023-07-27 22:38:23
Select record for action: REQ0011147		REQ0011147	Waldo Sisk	System Administrator	Approved	2023-07-27 22:38:22
Select record for action: REQ0011148		REQ0011148	Oma Duffy	System Administrator	Approved	2023-07-27 22:38:22
Select record for action: REQ0011145		REQ0011145	Oma Duffy	System Administrator	Approved	2023-07-27 22:38:21
Select record for action: REQ0011146		REQ0011146	Waldo Sisk	System Administrator	Approved	2023-07-27 22:38:21
Select record for action: REQ0011144		REQ0011144	Oma Duffy

Actually we need to generate REQ according to the number of role in [u_role_count] table.Please suggest a solution avoid that duplication in REQ generation.