I'm having some issues wrapping my brain around an issue that I've been really banging my head against for the last few days. I am a scripting novice, I am able to read examples most of the time and build out something that works, but for the Service Catalog I am just not getting it and all the examples that I've found here and other places on the internet are just not clicking in my brain apparently.
Basically the issue that I'm having is that I need to filter reference field lists on a Record Producer form in the catalog based on the value of another field on the form. I have a Company field that has the name of a company out of 4 possible choices. If a particular company is selected, I need values that have the same value in the Company field on those tables displayed. I am using the following tables:
- core_company - This is used as the dependent value on my forms with 4 possible values (e.g. Microsoft, Apple, HP, Google) in the "u_company_name" field.
- cmn_cost_center - This is used with the field on my form "u_location_code". Location code choices are determined by the dependent value in "u_company_name" and where "u_cost_center_type" = "Location" on the cmn_cost_center table.
- cmn_cost_center - This is used with the field on my form "u_department_code". Department code choices are determined by dependent value in "u_company_name" and where "u_cost_center_type" = "Department" on the cmn_cost_center table.
- itfm_gl_accounts - This is used with the field on my form "u_account_code". Account code choices are determined by the dependent value in "u_company_name".
The problem that I'm seeing of course in the Service Catalog is that the reference fields are displaying all possible choices, and while I've looked at scripts and tried a few modified to what I thought I needed, nothing works, and everything looks like a wall of text to me now . Below is a screenshot example of what I'm seeing (happening on Location code, Department code and Account code fields).
The challenge is that the companies that we are setting up for this share similar account structures and account codes, as we are all related and use a single Finance department. On the itfm_gl_accounts table I am differentiating the account codes/accounts by the company they are related to, and want to filter the reference fields the same way so that when we start reporting on stats we are showing the correct GL account(s).
Again, this all works well and configuration was relatively simple for the request forms (we're running this on the sn_sm_finance_request table), but the service catalog has different quirks.
Any help is greatly appreciated, as I've spent several days trying to solve this on my own and am a bit behind on implementing this feature. Thanks!
I've modified the script a bit after attempting to test it exactly as defined in this wiki article (replacing of course the tables it was querying): Adding Dependent Reference Variables - ServiceNow Wiki
I know the script was affecting the results of the reference field on my record producer, because there were a few times that the list came up blank instead of showing all of the available cost centers, and disabling the script again makes all of the come up when the magnifying glass is clicked. Below are screenshots of my catalog variable configurations, and my script. I'm not sure exactly what I should be doing different to make it work. The example in the wiki article is probably fine for those more familiar with SN scripting, but not 100% clear to me where the variables are getting pulled from, and why the catalog variables are using "sys_user_group" and "sys_user" tables, but the script is referencing the "sys_user_grmember" with no mention of how or why that is being used.
COMPANY VARIABLE
COST CENTER VARIABLE
Below is the script that I created as well, using the wiki article as an example and attempting to modify the necessary fields. No matter what I do the reference list either comes back blank, or with all the cost centers regardless of the attempt to set a dependent value. I'd also like to filter the list further by narrowing the selections by "u_cost_center_type" to separate department cost centers from location cost centers.
var CostCenterCompanyScript = Class.create();
CostCenterCompanyScript.prototype = Object.extendsObject(AbstractAjaxProcessor, {
CostCenterCompanyFunction: function(name){
var costCenterList = ' ';
var comp = new GlideRecord('core_company');
comp.addQuery('name',name);
comp.addQuery(u_cost_center_type" == "Location");
comp.query();
while(comp.next()) {
if (costCenterList.length > 0) {
costCenterList += (',' + comp.name);
}
else {
costCenterList = comp.name;
}
}
return 'sys_idIN' + costCenterList;
},
type: ' CostCenterCompanyScript'
});
