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

Peter Williams · ‎09-12-2024

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

Sushma Pavani K · ‎09-12-2024

Script Include -

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

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'
};
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

View solution in original post

Peter Williams · ‎09-13-2024

ive updated those to 'u_office' and 'u_department'

then i check to see if the sys_ids are being logged in the gs and they are showing in the logs

Sushma Pavani K · ‎09-13-2024

Is it 'u_office' and 'u_department_name' or

'u_office' and 'u_department'

Peter Williams · ‎09-13-2024

on the GL table its 'u_office' and 'u_department'

on the custom table where the filter need to show is 'u_office' and 'u_department_name'

Sushma Pavani K · ‎09-13-2024

Can you send screenshot of logs

Bert_c1 · ‎09-13-2024

He needs to use 'u_office' and 'u_department_name' in the two addQuery() lines. Seen from his dictionary record. And add some debug like I have to see what the script include is returning.