How to apply dual filters onto a field on a custom Table

[Peter Williams]

Good Morning, 

i have an issue where i am trying to apply dual fitlers onto a reference fields on a custom table.

the two reference fields are 

Office -> cmn_location table

Department -> cmn_department table

the third field i need to filter base on the office + department

This third field is a reference to a custom table called u_gl_codes and the columns in this table (Office and Department) is also link to the cmn_location for Office and cmn_department for department

I need to be able to filter out the list when users select the office on the table and department to see only the filter of the Gl Codes

i have tried many options which nothing has worked

[Mayur2109]

Hi @Peter Williams ,

Create reference qualifier on Gl Codes field. Pass the current values of office & department to it in the qualifier. After that you can write a script include which will filter the custom table based on office & department.

Please check and Mark Helpful and Correct if it really helps you.

Regards,
Mayur Shardul

ServiceNow Rising Star 2024

[Peter Williams]

tried this, do you have a sample of this?

[Sushma Pavani K · Accepted solution]

Script Include - 

To implement an advanced reference qualifier using a Script Include in ServiceNow, follow these steps:

1. Create the Script Include: This Script Include will have a method that queries the records matching the specified office and department fields.

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

   MethodName: function(office, department) {
   var glCodes = [];
   var gr = new GlideRecord('gl_code_table'); // Replace 'gl_code_table' with your actual table name
   gr.addQuery('office', office);
   gr.addQuery('department', department);
   gr.query();

   while (gr.next()) {
   glCodes.push(gr.getValue('sys_id'));
   }

   return glCodes; // Returns an array of sys_ids matching the office and department
   },

   type: 'ScriptIncludeName'
   };

2. Apply the Reference Qualifier: Use the Script Include in the advanced reference qualifier on the form field where the GL Codes should be filtered:

javascript(Colon) new ScriptIncludeName().MethodName(current.office, current.department);

This will dynamically filter GL Codes based on the office and department fields from the current form.

Feel free to let me know if you need any adjustments or clarifications!

Thanks,

Sushma.

Mark it as helpful, if it helped you

[Sushma Pavani K]

javascript(colon) new ScriptIncludeName().MethodName(current.office, current.department);