Can I limit a reference field with a script?

ServiceNowSteve · ‎04-01-2019

We have a custom CI table that I want to reference on a form. What I would like to do is limit the choices to only CI items that are not currently related to another asset.

My fuzzy logic is

Check the relationship table
If the current CI is listed in that able as a child exclude it from the search results
If not then show it.

Can this be done since I am checking a different table than the one I am referencing or is there another way to go about it?

asifnoor · ‎04-01-2019

Hi Steve,

Your script include should return the sys_ids of the values that need to be shown. So you write a glide record and query and fetch the ones which need to be shown and return that as comma separated.

Here is the sample code of script include which can be used as a reference. This script include accepts a user parameter and return the groups sys_ids on this parameter

var GetCurrentUserGroups = Class.create();
GetCurrentUserGroups.prototype = Object.extendsObject(AbstractAjaxProcessor, {
initialize:function() {
},
BackfillAssignmentGroup:function(user) {
	gs.info("In the log");
var gp = [];
var a = user; // capture the sysID passed to this function
//sys_user_grmember has the user to group relationship
var grp = new GlideRecord('sys_user_grmember');
grp.addQuery('user',a);
grp.query();
while(grp.next()) {
//build a comma separated string of groups if there is more than one
gp.push(grp.getValue('group'));
}
gs.log(gp);
// return Groups where assigned to is in those groups we use IN for lists
return 'sys_idIN' + gp;
}, 
type: 'GetCurrentUserGroups'
});

You can call the script include the form the reference qualifier like below

javascript:new GetCurrentUserGroups().BackfillAssignmentGroup(current.user);

So your script include need to be updated to accept the parameter like below and call the script include as shown above.

findCmdbRelCpu:function(ci)

Mark the comment as a correct answer and also helpful if it helps.

View solution in original post

sachin_namjoshi · ‎04-01-2019

you can do all of this using advanced reference qualifier and script include/

Please check below on advance reference qualifier.

https://docs.servicenow.com/bundle/geneva-servicenow-platform/page/script/server_scripting/concept/c_AdvancedReferenceQualifierExample.html

Regards,

Sachin

ServiceNowSteve · ‎04-01-2019

I see this is probably the right track but I don't understand how to construct my query. For some reason script includes have always been hard for me to grasp.

On the reference field dictionary I set it to advanced and typed: javascript:new stevesScriptInclude().findCmdbRelCpu()

I made a script include called stevesScriptInclude and a function that is called findCmdbRelCpu but now how do I construct the query to compare what's in the cmdb_rel_ci table vs what's in the custom CI table I made?

asifnoor · ‎04-01-2019

Hi Steve

In your script include, you can write a glide record on the relationship table and add Query which does not contain your CI.

Pass the CI to your script include as a parameter.

If you have already written and not working, kindly share your script include and i can debug it for you. Also share the table structure of your tables.

Mark the comment as helpful if it helps.

ServiceNowSteve · ‎04-01-2019

How do I pass the CI to the script include? Also how does it in turn pass back the information saying if it should be shown or not?

This is my advanced qualifier

javascript:new stevesScriptInclude().findCmdbRelCpu()

This is my script include function

findCmdbRelCpu: function() 
	{	
		var  findRel = new GlideRecord('cmdb_rel_ci');
		findRel.addQuery('^child=' + current.u_cpu);
		findRel.query();
		
		if(findRel.next())
			{
                                //It found a relationship and shouldn't show up
                                //in my reference lookup
				return false;
			}
		else
			{  
//It did not find a relationship and should show upin my reference lookup

				return true;
			}