Filter Reference Field based on Another Variable.

alexcharleswort · ‎03-31-2016

Is there a way to do this? I have two fields; u_location (reference to cmn_location table) and u_supplies (reference to u_supply_list table).

On the u_supply_list table, I have a checkbox variable u_omaha and when checked I know that supply is only available in that region.

What I want to have happen is if u_location == 'omaha', don't filter u_supplies, show them all;

else, if u_location != 'omaha' filter u_supplie by u_omaha == false, done show any supplies that are Omaha only.

Any idea how I might be able to accomplish this? I haven't been able to find much other than static filters when I've searched online.

Thanks in advance for your help!

Pradeep Sharma · ‎04-10-2016

Hi Alex,

You have to define advance reference qualifier on u_suplies and the reference qualifier will be

javascript:new BackfillAssignmentGroup().BackfillAssignmentGroup()

Script will be

Name : BackfillAssignmentGroup

Script :

var BackfillAssignmentGroup = Class.create();

BackfillAssignmentGroup.prototype = {

   initialize: function() {

   },


   BackfillAssignmentGroup:function() {

   var gp = ' ';

   var a = current.u_location.getDisplayValue(); //Here I am assuming u_location is the column name


   //return everything if the location value is empty or location is omaha

   if(!a || a == 'omaha')

   return;


   var gr = new GlideRecord('u_supply_list');

   gr.addQuery('u_omaha',false);

   gr.query();

   while(gr.next()) {

   if (gp.length > 0) {

   gp += (',' + gr.sys_id);

   }

   else {

   gp = gr.sys_id;

   }

   }

   return 'sys_idIN' + gp;

   },

   type: 'BackfillAssignmentGroup'

}

P.S : Here I am assuming you are not defining the reference qualifier on service catalog variables. Also please define the naming conventions as per your req.

Please let me know if you have any questions.

View solution in original post

Mihir Mohanta · ‎03-31-2016

Hi Alex,

Write a advanced reference qualifier that will call a script include and return the result as you want.

Thanks,

Mihir

alexcharleswort · ‎03-31-2016

I'm tapping into uncharted territory, so I know this isn't right. Any idea what I need to change??

Script Includes: filterByLocation

Script:

function filterByLocation(){

var location = g_form.getValue('u_location');
if (location == 'omaha'){

var notOmaha = [];
var gr = new GlideAggregate('u_supply_closet');

gr.addQuery('u_omaha', false);
gr.query();
while (gr.next()){
dupRecords.push(gr.u_omaha.toString());
}
return notOmaha;
}
}

Advanced reference qualifier on u_supplies:

alexcharleswort · ‎04-08-2016

Any other ideas out there or corrections that might make this work??

Help is much appreciated!

Mihir Mohanta · ‎04-09-2016

Hi Alex,

You have mentioned in the requirement that u_supplies variable is referenced to "u_supply_list " table.So according to the scenario you have mentioned I have created a catalog item with same variable name and same table in Demo instance.It's working fine.Please follow below steps.

1.Create a advanced reference qualifier in the u_supplies variable.

2.Create a script include with name : filterByLocation

Please let me know if you face any issue.

Please mark it as Helpful/Correct according to its impact.

Thanks,

Mihir