Need help to extract list of approvers to be shown in a dropdown list referencing a custom table

Karter83
Tera Contributor

‎03-04-2024 07:27 PM

In my form, I have a custom "Approval Matrix" table which contains the following fields (with example): 

UserIDNameLevel 1 Org UnitLevel 2 Org UnitPositionEmail AddressApproval RoleCost Center IDCost Center Name
EDDIE.FOOEddie Foo  Team LeadEDDIE_FOO@GMAIL.COMTeam LeadSSC_CC0024PROCUREMENT
ALAN.GOHAlan Goh   Chief Executive OfficerALAN_GOH@GMAIL.COMCEOSSC_CC0178CAPABILITY DEVELOPMENT & INTEGRATION

 

I inserted the UserID to be the same as UserID in sys_user table. Each user in sys_user table is assigned with a cost center. 

Approval Role field contains roles such as CEO, Chairman, HOD, Chief, Team Lead, etc.

Cost Center ID corresponds to the Cost Center Code in the "Cost Centers" table. 

I would like the list to execute the Scripts Include function onchange to retrieve the list of approvers that matches the following criteria from the "Approval Matrix" custom table:

1. Cost Center to match with requester's cost center from sys_user table

2. Approval role to match with predefined condition (e.g. Team Lead, HOD and Chief)

Based on the matching records found in Approval Matrix table, system to retrieve the corresponding users from sys_user table based on the UserID to show on the dropdown list in Record Producer.

 

Forgive me as I am new to scripting, I tried the following scripts in my scripts include but it failed to return results:

 
var AORApprovalMatrix1 = Class.create();
AORApprovalMatrix1.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
    getApproverList: function()
    {
        // Get the current user's cost center
        var requesterCostCenter = gs.getUser().getCompanyRecord().getValue('cost_center');
        gs.info(requesterCostCenter);
        // Create a new GlideRecord object for the Approval Matrix table
        var approvalMatrixGr = new GlideRecord('approval_matrix');

        // Add a query condition to filter by cost center and approval role
        approvalMatrixGr.addQuery('approval_role', 'Team Lead');
        approvalMatrixGr.addQuery('cost_center_id', requesterCostCenter);
        approvalMatrixGr.query();

        // Array to store the list of approvers
        var approvers = [];

        // Iterate over the records in the Approval Matrix table
        while (approvalMatrixGr.next()) {
            // Add the approver to the list
            approvers.push(approvalMatrixGr.name.toString());
        }

        // Return the list of approvers
        approvers;

    },
    type: 'AORApprovalMatrix1'
});

 

In the dropdown reference list, I select "Approval Matrix" table as reference and "Advanced" under Use reference qualifier. In the reference qualifier field, I inserted the script javascript: AORApprovalMatrix1().getApproverList(); 

in an attempt to call the script includes function.

 

Please assist me as soon as possible if you can. Thank you very much!

Labels:
0 Helpfuls
  • 458 Views
1 REPLY 1

James Chun
Kilo Patron

‎03-05-2024 02:18 AM

Hi @Karter83,

 

First of all, I wouldn't recommend creating a custom table for this. There is a limit on the number of custom tables you can create and most of the information in your custom table seems like it should be already available in the User [sys_user] table. Instead, I would recommend a few different approaches:

  • Create Group records per 'condition'. i.e. Create a group called 'Team Lead Cost Center XYZ' and add the appropriate members to the group
  • Use Decision Table
  • Store the necessary information in the User record

Anyway, I will provide some feedback of your script.

	//var requesterCostCenter = gs.getUser().getCompanyRecord().getValue('cost_center'); //not sure where you got this code, but there is no 'getCompanyRecord' function in GlideUser object - https://developer.servicenow.com/dev.do#!/reference/api/washingtondc/server_legacy/GUserAPI#GUser-getID

		var currentUser = new GlideRecord('sys_user');
		currentUser.get( gs.getUserID());
		var requesterCostCenter = currentUser.getValue('cost_center');
		
		var approvalMatrixGr = new GlideRecord('approval_matrix'); //Are you sure this is the right name? If it's a custom table, normally the name is prepended with 'u_'

		approvalMatrixGr.addQuery('approval_role', 'Team Lead'); //Also, are you sure it's the right column name? If it's a custom column, normally it's prefixed with 'u_'
		approvalMatrixGr.addQuery('cost_center_id', requesterCostCenter); //Also, are you sure it's the right column name? If it's a custom column, normally it's prefixed with 'u_'
		approvalMatrixGr.query();

		var approvers = [];

		while (approvalMatrixGr.next()) {
			approvers.push(approvalMatrixGr.name.toString());
		}

		//approvers;
		return approvers;

 

Is the 'Name' column in the custom table referencing the user table?

If so, you should change the reference list variable to reference the User [sys_user] table and use the reference qualifier there. Also, note that you don't have to use AJAX, and invoking the script include should be in the following format: javascript: 'sys_idIN' + new AORApprovalMatrix1().getApproverList();

That's a mouthful, best of luck. Cheers

0 Helpfuls