Is it possible for a child table to reference records from another child table of the same parent?

[Michael Lovell]

I have a parent table called 'Projects'.  Projects has a child table called 'Project Tasks'.  Projects has another child table called 'LOE'. 

• Each Project Task has a field called 'Name'. 
• The LOE table has a choice field called 'Task

What I'd like to do, if possible, is populate the options available for the Task choice in LOE with the each of the Name values in Project Tasks for each Project.

I am on Kingston.  Thank you.

[Xavier Cordero · Accepted solution]

Hello Michael,

You can achieve this through an advanced reference qualifier, by calling a script include. Basically, you want to have a reference field within the LOE table which references the task table. You'll want to create a script include that conducts a gliderecord query on the other table for the parent being the same parent in both LOE and Task tables.Then you will want to configure the dictionary of the field and change the reference qualifier to advanced and call the script include.

Here are some examples on Advanced Reference Qualifiers:

https://docs.servicenow.com/bundle/geneva-servicenow-platform/page/script/server_scripting/task/t_Ja...

[Michael Lovell]

Thanks Xavier.  I was able to solve this challenge using a script include.  Just in case it may be helpful for any others, here is some sample code that worked for me:

//Client side script:

function onChange(control, oldValue, newValue, isLoading, isTemplate) {	
	
	var val = g_form.getValue('u_parent');	
	var ga = new GlideAjax('ProjectTasksForLoes');	
	
	ga.addParam('sysparm_name','projectTasksForLoes');	
	ga.addParam('sysparm_user_name', val);	
	ga.getXML(ProjectTasksForLoes);	
	
	function ProjectTasksForLoes(response) {		
		
		var answer = response.responseXML.documentElement.getAttribute("answer");
		var myArray = answer.evalJSON();
		for(var i=0; i<myArray.length; ++i){
			g_form.addOption('u_task', myArray[i], myArray[i]);
		}
	}	
}




//Script include:

var ProjectTasksForLoes = Class.create();
ProjectTasksForLoes.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	projectTasksForLoes: function() {		
		
		var c = this.getParameter('sysparm_user_name');		
		var testArray = [];
		var task = new GlideRecord('pm_project_task');
		task.addQuery('parent', c);
		task.query();
		
		while(task.next()){
		testArray.push(task.getDisplayValue('short_description'));
		}
		return JSON.stringify(testArray);
		
	},	
	type: 'ProjectTasksForLoes'
});