Configure reference qualifier to show only records which are not already used in other record

[Chandler2]

I have a custom reference field "Choose your demand" on task table which refers to dmn_demand table. Now, once I have selected a demand record in field "Choose your demand",

Option 01: I don't want already selected records to be showing up in magnifying glass next time someone is trying to select a value

OR

Option 02: If someone selects a demand record which has been selected already in another task record, show an error message that this demand has already been used in "xyz(name of task)" so please select a different demand.

Please suggest for both. (Although I think option 01 is a better way to go)

Thanks

[Marco0o1]

Hi @Chandler2 ,

For your Option 1 I recommend to use a Reference Qualifier type advanced "Is the better way and user friendly. Follow this step.

- Create a script include called "CheckDemand", there we will return a reference qualifier with the records that are available.

var CheckDemand = Class.create();
CheckDemand.prototype = {
    initialize: function() {
    },
	getAvailable: function(){
		var query = "sys_idIN";
		var demanGR = new GlideRecord("dmn_demand");
		demanGR.addQuery("task", "!=", ""); //There you will need to put how to know if you want to use this record, if is a related list or you store the value in a field or whatever you want, on this example I just put a reference field called task and is not empy that means is available
		demanGR.query();

		while(demanGR.next()){
			query+= demanGR.getUniqueValue() + ",";
		}
		return query;
	},
    type: 'CheckDemand'
};

- Then you will call you availables records in your field

- The user only can select between the available records that are available.

For your opton 2 I can recomend to create a Client Script onChange that throww a error when the user select a record that is not aviable and another Client Script onSubmit that throw error if the use want to submit the value if the user continue selecting, just use the same logic but this time you will need to create a Client Script Callable, in you dont want to create a Client Script you can just use a BR on Insert or update the record and check and throw error when the user try to submit.

Hope that help you.

[Vrushali Kolte]

Hello @Chandler2,

As per you scenario, please try below mentioned method, I have tried in my PDI and it worked for me 100%

Reference qualifier code:

javascript: "sys_id!=" + current.choose_your_Demand; //please check your field name from backend

Please Accept my answer or mark it helpful if it works for you!

[Chandler2]

Hi Vrushali,

when I say that value shouldn't be used already, I don't mean only for that particular record. I mean, in any of the records it should not be already present.

[Amit Gujarathi]

HI @Chandler2 ,
I trust you are doing great.
To implement this, you can use a Reference Qualifier with an Advanced Script. The idea is to filter out demands that have already been chosen in other tasks. Here's a basic approach using Javascript:

(function() {
    var usedDemands = [];
    var gr = new GlideRecord('task'); // Replace 'task' with your specific task table if different
    gr.query();
    while (gr.next()) {
        // Assuming 'choose_your_demand' is the field name of the reference field
        if (gr.choose_your_demand) {
            usedDemands.push(gr.choose_your_demand.toString());
        }
    }

    // Constructing a query that excludes already used demands
    var query = 'sys_idNOT IN' + usedDemands.join(',');
    return 'sys_idNOT IN' + usedDemands.join(',');
})();