Need to get the records for a reference field based on another fields value

[Yesh]

Hi Team,

I have field named Category which is a reference type field, referencing table contains two fields: Category and Plant. I have another Field called Plant Name in the current table, it is also a reference type field. By selecting the plant name, I need the records for category in which the selected plant name matches with the Plant which is there in Category table.

Current Table:

Category Table:

Is there any suggestion, how to achieve this?

Thanks,

Yesh

[Rahul Talreja]

Hi Yesh,

Than its better to go the way by script include as you are going,
Suggested by @Nikhil

Try changing your script as:-

uniqueCat: function(plant) {
var pList = [];
var plan = new GlideRecordSecure('x_rfq_module_categories');
plan.addQuery('u_plant', plant);
plan.query();

while(plan.next()) {
gs.addInfoMessage(plan.u_category);//here i am getting all categories which are matched with plant name
pList.push(plan.getValue('sys_id'));// Make a change here.
}
return 'sys_idIN' + pList.toString();//only returning the last record
}

Regards,
Rahul

[Nikhil65 · Accepted solution]

Hi @yesh ,

Please create a new script include with the following function, also make sure its client callable:

getCategory: function(plant) {
var refQuery = '';
refQuery = 'category=plant';
return refQuery;
},

After this, use advanced reference qualifier and use the below code for calling your script include:

javascript: yourScriptIncludeName().getCategory(current.variables.plantName);

However, if its a scoped application, then you will have to append the application name before yourScriptIncludeName() in reference qualifier

Note: Make sure that you change the variable name as per the name in your system.

Mark as correct if helpful!

Thanks,

Nikhil.

[Yesh]

Hi @Nikhil ,

I have trued the below script. But is returning only one record. Can you help me?

uniqueCat: function(plant) {
var pList = [];
var plan = new GlideRecordSecure('x_rfq_module_categories');
plan.addQuery('u_plant', plant);
plan.query();

while(plan.next()) {
gs.addInfoMessage(plan.u_category);//here i am getting all categories which are matched with plant name
pList.push(plan.sys_id);
}
return 'sys_idIN' + pList.toString();//only returning the last record
}

Thanks,

Yesh