Select values for a reference field, based on a table and another reference field

[gagneet]

We have 4 tables and forms.

• 2 are reference data tables.
  • These have data for Users and Goods
  • UsersTbl:
    • UserID: Auto Number
    • UserName: String
    • UserEmail: String
    • UserAddress: String
  • GoodsTbl:
    • GoodsID: Auto Number
    • GoodsName: String
• 1 is a linking table (LinkTbl). This has a 1 to many relation between the Users and Goods
  • For each User, there can be multiple Goods
  • Both the fields on this table are reference fields to the 2 tables for Users and Goods
    • LinkID: Auto Number
    • GoodsRef: Reference - GoodsName
    • UserRef: Reference - UserName
    • DateCreated: Date/Time
• The final table (NewLinkTbl) is for creating data for the linking of Users with Goods. Due to business requirements, we are keeping this separate.
  • NewLinkID: Auto Number
  • UserRef: Reference - UserName
  • GoodsRef: Reference - GoodsName
  • LinkID: Number
  • DateCreated: Date/Time

For the final table, I want to select and use a value from the the Users (Reference) first. Based on that, when I click the GoodsRef field, I only wish to show the data values, where the LinkTbl DOES NOT have a link already between the User and the Goods.

I tried this:

1. Client Script
2. onChange in NewLinkTbl
3. Field: UserRef

function onChange(control, oldValue, newValue, isLoading, isTemplate) {
	if (isLoading || newValue === '') {
		return;
	}
	// Call the script to fetch the records which are not matching
	var ga = new GlideAjax('ScriptIncludeJS');
	
	// Fetch the value of the User selected
	var user_ref = g_form.getValue('user_name');
	ga.addParam('sysparam_name', 'getNotLinkedGoods');
	ga.addParam('sysparam_user', newValue);
	ga.getXMLAnswer(DisplayGoods);
}

// Callback function to process the response returned from the server
function DisplayGoods(response) {
	var jsonResultObj = JSON.parse(response);
	//alert(jsonResultObj);
	g_form.setValue('linked_goods_name', jsonResultObj);
}

1. Script Include
2. Client Callable
3. Accessible from: All application scopes

var ScriptIncludeJS= Class.create();
ScriptIncludeJS.prototype = Object.extend(global.AbstractAjaxProcessor, {

	getNotLinkedGoods: function() {
		// Declare and initialize the return JSON
		var jsonString = {};
		
		// Retrive the value of the User record
		var userRef = this.getParameter('sysparam_user');
		var goods = [];
		
		// Query and get all the linked Customs Brokers
		var grAMObj = new GlideRecord('x_478634_att_pro_0_NewLinkedTbl');
		grAMObj.addQuery('NewLinkedTbl.UserRef', '<>' + userRef);
		grAMObj.query();
		
		// Loop through all the values, which do not match and store them
		while (grAMObj.next()) {
			goods.push(grAMObj.get_values());
		}
		return goods;
	},
	type: 'ScriptIncludeJS'
});

I tried with the above, but it is not working.

Any ideas and help on a script would be helpful.

Thanks.

[Anil Lande · Accepted solution]

Hi,

In your case you need Advanced reference qualifier on the Goods Disctionary.

From the Reference qualifier call a Script include function and return the query to exclude the goods already assigned to user. 

Your advanced qualifier would be like below:

javascript: new ScriptIncludeJS(). getNotLinkedGoods(current.userRef);

In your script include use below logic:

var ScriptIncludeJS= Class.create();
ScriptIncludeJS.prototype = Object.extend(global.AbstractAjaxProcessor, {

	getNotLinkedGoods: function(userId) {
		var goodsArr =[];
var linkGr = new GlideRecdord('x_478634_att_pro_0_LinkTbl');  // replace name of linkTbl here
linkGr.addQuery('UserRef',userId);  // replace coorect field name here
linkGr.query();
while(linkGr.next()){
//Prepare the array of sys_id of users goods
goodsArr.push(goodsArr.goods.toString());  // replace name of your goods field in LinkTbl
}
return 'sys_idNOT IN'+goodsArr.toString();
	},
	type: 'ScriptIncludeJS'
});

To learn more about reference qualifier refer below links:

https://www.servicenow.com/community/itsm-articles/reference-qualifier/ta-p/2306509

https://docs.servicenow.com/en-US/bundle/utah-platform-administration/page/script/server-scripting/c...

Thanks,

Anil Lande

[Anil Lande · Accepted solution]

Hi,

In your case you need Advanced reference qualifier on the Goods Disctionary.

From the Reference qualifier call a Script include function and return the query to exclude the goods already assigned to user. 

Your advanced qualifier would be like below:

javascript: new ScriptIncludeJS(). getNotLinkedGoods(current.userRef);

In your script include use below logic:

var ScriptIncludeJS= Class.create();
ScriptIncludeJS.prototype = Object.extend(global.AbstractAjaxProcessor, {

	getNotLinkedGoods: function(userId) {
		var goodsArr =[];
var linkGr = new GlideRecdord('x_478634_att_pro_0_LinkTbl');  // replace name of linkTbl here
linkGr.addQuery('UserRef',userId);  // replace coorect field name here
linkGr.query();
while(linkGr.next()){
//Prepare the array of sys_id of users goods
goodsArr.push(goodsArr.goods.toString());  // replace name of your goods field in LinkTbl
}
return 'sys_idNOT IN'+goodsArr.toString();
	},
	type: 'ScriptIncludeJS'
});

To learn more about reference qualifier refer below links:

https://www.servicenow.com/community/itsm-articles/reference-qualifier/ta-p/2306509

https://docs.servicenow.com/en-US/bundle/utah-platform-administration/page/script/server-scripting/c...

Thanks,

Anil Lande

[gagneet · Accepted solution]

Thank you for the answer Anil. Had to modify things a bit, it basically this is what worked with the 'Reference Specification' call on the field:

		var ScriptIncludeJS= Class.create();
		ScriptIncludeJS.prototype = Object.extend(global.AbstractAjaxProcessor, {

			getNotLinkedGoods: function(userID) {
				// Create a variable for the Array of the Goods to be shown
				var goodsArray = [];

				// Select the LinkedTbl table
				var grObj = new GlideRecord('x_1234_pro_0_linkedtbl');

				// Get the User details from the form on which the field is displayed as 'userID'
				// For the table selected above, use the linked-table
				grObj.addQuery('user_name', userID);
				grObj.query();

                               // The table object is used to look up the next row, which has the same userID
				while(grObj.next()) {
					// Prepare the array of goods names as a PUSH with the table object
					goodsArray.push(grObj.goods_name.toString());
				}
				return 'sys_idNOT IN' + goodsArray.toString();
			},
			type: 'ScriptIncludeJS'
		});