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-12-2024

This worked perfectly thank you

Mayur2109 · ‎09-12-2024

Hi @Peter Williams ,

Please find screenshots below, I've used incident table for reference Dependent field shows value based on Assignment Group & Assigned to from table Test Table.

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

Regards,
Mayur Shardul

ServiceNow Rising Star 2024

Mayur2109 · ‎09-12-2024

Here is the code for your reference

SI :

	getDepF : function(group,assignee){
		var dep =[];
        var gr = new GlideRecord("u_test_table");
        gr.addEncodedQuery('u_assignment_group='+group+'^u_assign_to='+assignee);
		gr.query();
        while(gr.next()) {
			
			dep.push(gr.getUniqueValue());
        }
		gs.log('Values '+ dep);
		return "sys_idIN" + dep.toString();
	},

Reference qualifier ;

javascript&colon; new TestSI().getDepF(current.assignment_group,current.assigned_to);

Regards,

Mayur

Bert_c1 · ‎09-12-2024

You need to be more specific, is the form shown for the u_gl_codes table or some other table? If creating a new record in the u_gl_codes table, it seems odd to restrict what is in the GL Code field based on existing records.

Peter Williams · ‎09-12-2024

Sorry the previous one i accepted didnt work, i had teh field depended on the department from the Form designer.