Help! Reference qualifier to show only with 5 digit numbers

[Jet R]

Hi! 

We have a custom field in the Department Tablen called "department id"  where in the data contains 5- 7 digit numbers. 

Now in the record producer field we only want to show the records with only 6 digits.  How can I achieve that? Thanks

Eg. Let say in the table below we only need to show in the record producer field the records Accounting and IT. 

Department Name	Department ID
HR	01203
Accounting	123456
Fleet	0234858
IT	033332

[SumanthDosapati]

Hi,

You can try this in advanced reference qualifier

javascript:'sys_idIN'+getIDs(); function getIDs(){var gr=new GlideRecord('table_name');gr.query();var ids=''; while(gr.next()){  if(gr.department.length == '6'){  ids+= gr.sys_id+',';}} return ids;}

Just update the table name and department field name in the above script

Mark as correct and helpful if it solved your query.

Regards,
Sumanth

[Allen Andreas]

Hi,

Unfortunately, to filter by length, you'd have to process it as a database call and not through the normal filter process. With that said, you'd probably want to build a script include that queries this table and builds an array with the sys_ids and then returns this to your reference qualifier in string.

In the reference qualifier you can use:

javascript:"sys_idIN"+new scriptincludeName().scriptincludeFunction();

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Jet R]

Hi Allen, 

Thanks for your response, below is my script include but it's not working. the department id is the "id" in the department table. 

Can you help me check what's wrong with my script? Thanks. 

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

    getSixDigits: function(){
        var idSix = [];
        var depGR = new GlideRecord ('cmn_department');
        depGR.query();
        while(depGR.next());
        if (depGR.id.toString().length == 6){
            idSix.push(depGR.getDisplayValue('id'));
        }
        return 'idIN' + idSix;        
    },    
    
    type: 'idUtils'
};

[SumanthDosapati]

Try changing the function as below

getSixDigits: function(){
        var idSix = [];
        var depGR = new GlideRecord ('cmn_department');
        depGR.query();
        while(depGR.next()){
        if (depGR.id.toString().length == '6'){
            idSix.push(depGR.sys_id);
        }
}
        return 'sys_idIN' + idSix;        
    },    

Regards,
Sumanth