Reference field dependency in Service Catalog

[Marcel H_]

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:

1. 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.
2. 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.
3. 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.
4. 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!

[Kalaiarasan Pus]

If the tables to which the variables are pointing towards have relationship with each other, you can simply use advanced reference qualifier such as this

javascript: "sys_id!="+current.variables.firstvariablename

If there is no direct relationship between the table and you want to just access the variable and build the query in the reference qualifier script, use current.variables.firstvariablename to access the variable value.

[Marcel H_]

I've got something similar working with the following advanced reference qualifier:

u_cost_center_type=Location^u_company.name=Microsoft^EQ

However, I still don't understand what I need to do to get the current.variables.CompanyName working in place of naming the company specifically. I need that variable to be passed into the reference qualifier somehow so that the list is being filtered dynamically.

Sorry if my questions seem really basic, learning more and more for scripting, but it's never been something that I've had to do much at all, and the last time that I scripted much for a job was doing VBScript back in like 2005

[Marcel H_ · Accepted solution]

I found an example in a similar post and modified the script a bit. Turned out that I just needed to use javascript: in the advanced reference qualifier field on the variable. No need for a script include.

javascript: 'u_cost_center_type=location^u_company='+current.variables.CompanyName.toString();

[CharlesGriff]

Hi Marcel

I am hoping that you can maybe assist me I have a very similar situation as to what you did with your dependent fields, I want to know if you had any special reference qualifiers on the other fields? I have a table that I am using for job descriptions so that people can apply for vacancies and on this table I have four fields all lookup select boxes referencing the same table but different fields namely division, department, location and job description of which each one should be dependent on the previous. I am confused with the reference qualifiers and also did you make use of any variable attributes?

[Marcel H_]

In my case what I had was a simple reference qualifier on the first field "Company" to filter only the ones that I wanted to show for selection. I also have a Catalog UI Policy that hides the other fields until the previous one is filled in (to avoid people being able to see data before the scripts can take effect).

After the user chooses the Company, the next field for Location (which is really on the Cost Center table) will become visible, and that uses the advanced reference qualifier to display Cost Centers designated as a Location and related to the Company the user first chose. I think I may have added a custom field to associate the Company to the Cost Center record for this.

javascript: 'u_cost_center_type=location^u_company='+current.variables.u_company_name.toString(); 

Next, after that reference field is filled in, the catalog will display the Department field (also actually mapped to Cost Center in my case) and then uses another advanced reference qualifier to filter the results to be applicable.

javascript: 'u_cost_center_type=department^u_company='+current.variables.u_company_name.toString();

After Department the Account field will be displayed. In this case we had several account codes we were using that were the same for different companies, so I created a custom field on those records to associate an individual record to a company record. That uses the advanced reference qualifier below.

javascript: 'u_company='+current.variables.u_company_name.toString();

In this way, between these advanced reference qualifiers, Catalog UI Policies and reference fields on the individual records we are able to display only information that is applicable to the user's selected company to limit the number of choices.

In your case you could potentially use variables/fields related to division, department, location, etc. to narrow down the total number of job descriptions you're displaying. For example if a Department is related to a specific Division, you can use the Division selected by the user in the catalog, pass that into the qualifier for Department and only display departments specific to that Division. Similar with Department and Location and then down to Job. If needed you could also pass multiple parameters such as Department AND Location to narrow down Job even further.

Hope that helps!