How do I filter a Reference variable based on the user having a role in a field in the referred list

[ewilks]

I have a table that presents categories in a reference variable; allowing the user to select one before submitting an incident

Let’s say I have the 7 categories listed below.

Category       Active       Role
Audio             true            AV
CD Reader    false
Laptop           true
Network         true
Printer           true            Support
Server           true
Video             true            AV

Of those 7 categories, I’d only like to display those where active = true. Easy enough, I could just create a reference qualifier condition of Active = true.
But what I would like to do is to have all users see those active categories with a null role, and only those who have the role to see the records with a role that matches them

So, if a user that has either no role, or only itil role assigned, they will see the following categories available to choose from (i.e. those categories with no role which are active):
Laptop
Network
Server

However, if a user with the AV role logs on, they will see the following categories available (those categories with no role which are active + those active roles with the AV role):
Audio
Laptop
Network
Server
Video

I have taken a look writing a reference qualifier, but I am confused as to how I would bring back all of the records where the role matches one of the user’s roles.

[Michael Fry1]

I think you're going to have to use a Script includes script and call the script as your reference qualifier. Thinking the script looks something like:

var retval = "sys_idIN";
    var getCats = new GlideRecord('some_table');
    getCats.addQuery('active', true);
    var qc = getCats.addQuery('role', '');

    if (gs.hasRole('AV')){
        qc.addOrCondition('role','CONTAINS', 'AV');
    }
    if (gs.hasRole('Support')){
        qc.addOrCondition('role','CONTAINS', 'Support');
    }
    getCats.query();
    while(getCats.next()){
            retval += getCats.sys_id + ",";

    }

Maybe this gives you an idea.

[The SN Nerd]

Correction in while loop - cannot dot walk in a loop or you will only return the last value.

Using array for cleanliness

var retval = "sys_idIN";
var sysIDs = [];
    var getCats = new GlideRecord('some_table');
    getCats.addQuery('active', true);
    var qc = getCats.addQuery('role', '');

    if (gs.hasRole('AV')){
        qc.addOrCondition('role','CONTAINS', 'AV');
    }
    if (gs.hasRole('Support')){
        qc.addOrCondition('role','CONTAINS', 'Support');
    }
    getCats.query();
    while(getCats.next()){
            sysIDs.push( getCats.getValue('sys_id') ); //Needs .getValue in while loop

    }
return retval + sysIDs.join(",");

[ewilks]

Thanks for the advice, Paul, I'll see if I can make that work.  I appreciate it. 

[ewilks]

Thanks for the guidance, Mike.