Hi

I just answered a question here in the Community and thought to write these findings down in an article here.

Situation:

The selection of Cost Centers in a Catalog Variable shall be limited to those Cost Centers, that are configured to the Country of the current user.

Logical Solution:

Each User will have a "Location" record referenced in his/her [sys_user] record, and this "Location" will have a "Country" field.

That Country of the User will be used to find "Cost Center" [cmn_cost_center] records, which have Locationss referenced, where the Country is the same as for the user.

NOTE:

Just comparing "Locations" will NOT be sufficient, because there may be any numbers of "Locations" with the same "Country", so we need to make an extra step in between.

I will create an "Advanced Reference Qualifier" to implement this requirement.

See the steps to set this up below.

Steps to reproduce:

The solution takes the following steps:

- The current user will have a "Location" configures in his "sys_user" record.

- This "Location" record (cmn_location) does have a "country" field,

which will define the country relevant for searching Cost Centers.

- I will search for all Records in the Cost Center table, where the "Country" in the

referenced "Location" field is the same as in the user record.

The solution is set up for a Catalog Variable of type "Reference" to the "Cost Center" table.

The Reference Field uses an advanced "Reference Qualifier" to limit the shown records.

(I hope this meets your requirements, as I have understood it from your question above).

Steps to reproduce:

1) Create a Script Include to be used in the Advanced Reference Qualifier later (in your Catalog Variable)

Note: The "Name" field MUST be the same as the name in the Script !!

Here is the Script for copy/paste:

var DirkCountryCostCenterRefQual = Class.create();
DirkCountryCostCenterRefQual.prototype = {
    initialize: function() {
    },

    getData: function(current) {
        var mylocation = gs.getUser().getRecord().getValue('location');

        var gr = new GlideRecord('cmn_location');

        gr.addQuery('sys_id', mylocation);
        gr.query();

        var myCountry = '';

        if (gr.next()) {
            //   gs.info(gr.getDisplayValue('country'));
            myCountry = gr.getValue('country');
        }

        return "location.country=" + myCountry;
    },

    type: 'DirkCountryCostCenterRefQual'
};

2) Create a Test-Catalog Item

The Catalog Item (1) will need a Catalog Variable (2) which I called "cost_center" (3) - see details in the screenshot below.

Set the Variable (1) to "Type" = "Reference" and enter the Question (3).

On the "Type Specifications" Tab, set the "Reference" field to the "Cost Center" (cmn_cost_center) table.

Set the "Use reference qualifier" field to "Advanced" (3).

Enter the Value shown in the "Reference qualifier" (4) field as shown in the screenshot below. This will use the Script Include created above to create the filter for the Lookup list.

Copy it from here:

javascript:new DirkCountryCostCenterRefQual().getData(current)

3) Create Test records

The User is configured like shown below:

The used Location is set up like:

You can see, that the "Country" field is set up as "Mexico" (1).

The following marked three Cost Centers are using Locations, where the Country is set to "Mexico". These will be the Cost Centers shown in the reference List (Catalog Variable) below:

4) Finally earn your fruits

Test your catalog Item and open the reference List:

It will show only the matching and desired records.

That's it

I hope that explains in detail how to set up such scenarios in your environment.

Just let me know what you think about this article.

Thanks a lot in advance for your feedback and comments on this article, you can leave it here below!

Thanks for marking as helpful and bookmarking - that helps me determine your interest in further articles.

Enjoy ServiceNow & BR

Dirk

---------------------------------------------------------------------

If you like to also review my other articles on the ServiceNow Community, please have a look at the overview here:

Overview of my articles

NOTE: The content I provide here is based on my own experiences and does not necessarily represent my employer's views.