Hi
I just sat down and created an example for you, which hopefully will solve your issue:
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)
4) 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:
5) Finally earn your fruits
Test your catalog Item and open the reference List:
It will show only the matching and desired records.
I hope, this will meet your requirement to make you happy.
Just give it a shot and let me know, please.
Let me know if that answers your question and mark my answer as correct and helpful.
BR
Dirk
