My advanced reference qualifier does not return the correct number of records

[Student1]

Hello all,

I mention my scenario in the following lines. If you could help me on that, I would be so grateful for your assistance.

1)I have a field in a record producer, called "Opened for", that makes a reference to the sys_user table. For this field, if the logged in user has the "sn_customerservice.customer_admin" role, I want to display all the users that have the same account (account is a field on the "customer_contact" table, for example account="Warehouse Account"). If the user has not the role mentioned above, I just want to display his/her name (so, Abel Tuter that has not the role mentioned above, in this field he will be able to see only his name ("Abel Tuter").

2)I made a reference qualifier as shown below.

(I don't know if the variable attributes field is correct. If I leave this field blank, then its even worse -- so I guess I need it).

3) When I open the form on service portal to create a new incident as a logged in user with name "UserA" that has "sn_customerservice.customer_admin" role and account equals to "Warehouse account", I can see in the dropdown list only 9 users.

The script include that is used is the following:

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

	returnCustomerContacts: function(){
		
		if(gs.hasRole("admin")){      //if the logged in user has "admin" role, then I return all the users
			return;
		}
		
		//check if the user has "sn_customerservice.customer_admin" role
		var isCustomerAdmin=false;
		if(gs.hasRole("sn_customerservice.customer_admin")){
			isCustomerAdmin=true;
		}
		
		var userSysId="";   //variable that will store all the sys ids of the users that will be returned
		if(isCustomerAdmin==false){
			return "sys_id"+"="+gs.getUserID();  //return only the current user's id (as an encoded query)
		}else{
			//get the account of the current logged in user
			var grGr=new GlideRecord("customer_contact");  
			grGr.addQuery("sys_id",gs.getUserID());
			grGr.query();
			
			var account;
			if(grGr.next()){
				account=grGr.getValue("account");
			}
			
			//get all the users id with the same account
			var grAccount=new GlideRecord("customer_contact");  
			grAccount.addQuery("account",account);
			grAccount.query();
			
			while(grAccount.next()){
				var user_id=grAccount.getUniqueValue();
				if(gs.nil(userSysId)){
					userSysId=user_id;
				}else{
					userSysId=userSysId+","+user_id;
				}
			}
			return "sys_idIN"+userSysId;    //return an encoded query that is a comma seperated string of users' ids
		}
	},
    type: 'CustomerContacts'
};

4)However, if I go back to backend and run a filter, I retrieve 19 users.

If I put the code of my script include in scripts background, I also retrieve 19 users.

So, I guess that the code works properly (since in scripts background I retrieve the correct number of users), but something prevents it from showing all the users in dropdown list.

Any ideas??

[Thom8 · Accepted solution]

Just a thought; do you have any before-query-BR which interfere?

[Student1]

Hey, thanks for your reply!! I need to check it again, but I don't think so.

[Student1]

Indeed the problem was a before query BR on sys_user table.

I find that the OOTB BR "user query" was the reason for my error. I needed to make it inactive.

Thank you.

[Willem]

Have you checked if account is retrieved correct in the script include? Try add a log. Also getting the sys_id's can be done with array. See below.

Have you added the "ref_qual_elements"-attribute to your variable that has the Reference Qualifier?

Like so:

ref_qual_elements=account

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

    returnCustomerContacts: function () {

        if (gs.hasRole("admin")) {      //if the logged in user has "admin" role, then I return all the users
            return;
        }

        //check if the user has "sn_customerservice.customer_admin" role
        var isCustomerAdmin = false;
        if (gs.hasRole("sn_customerservice.customer_admin")) {
            isCustomerAdmin = true;
        }

        var userSysId = [];   //variable that will store all the sys ids of the users that will be returned
        if (isCustomerAdmin == false) {
            return "sys_id" + "=" + gs.getUserID();  //return only the current user's id (as an encoded query)
        } else {
            //get the account of the current logged in user
            var grGr = new GlideRecord("customer_contact");
            grGr.addQuery("sys_id", gs.getUserID());
            grGr.query();
            var account;
            if (grGr.next()) {
                account = grGr.getValue("account");
            }
            gs.info("account=" + account);
            //get all the users id with the same account
            var grAccount = new GlideRecord("customer_contact");
            grAccount.addQuery("account", account);
            grAccount.query();

            while (grAccount.next()) {
                var user_id = grAccount.getUniqueValue();
                userSysId.push(user_id);
            }
            return "sys_idIN" + userSysId.join(',');    //return an encoded query that is a comma seperated string of users' ids
        }
    },
    type: 'CustomerContacts'
};