Location relationship (CIs at current location + downstream CIs)

[Mike Hashemi]

I am trying to build a relationship so I can add a related list to a form, that will display all CIs with the same location as the current CI, plus those CIs that have a location that is a child of the current location. For example, display all CIs at "Location A" and those at "Location A, room 1" (where "Location A, room 1's" parent is "Location A". I am testing with cmdb_ci_win_server.

When I run the following script in Scripts - Background, SN prints information about the expected CIs (from different classes):

var locationHierarchy = [];

function getChildLocations(location) {
    locationHierarchy.push(location);

    var childLocationGR = new GlideRecord('cmn_location');
    childLocationGR.addQuery('parent', location);
    childLocationGR.query();

    while (childLocationGR.next()) {
        getChildLocations(childLocationGR.sys_id);
    }
}

var current = new GlideRecord('cmdb_ci_win_server');
current.addQuery('sys_id', 'd1d2bbe447533d10291fe0e8036d432a');
current.query();

if (current.next()) {
    getChildLocations(current.location.sys_id);

    var ciGR = new GlideRecord('cmdb_ci');
    ciGR.addQuery('location', 'IN', locationHierarchy.join(','));
    ciGR.addQuery('sys_id', '!=', current.sys_id);
    ciGR.addNotNullQuery('location');
    ciGR.query();

    while (ciGR.next()) {
        gs.info('returning CI: sys_id = {0}, name = {1}, class = {2}', ciGR.sys_id, ciGR.name, ciGR.getClassDisplayValue());
    }
}

So I created a relationship called "location test", with the following script:

(function refineQuery(current, parent) {
    var locationHierarchy = [];

    function getChildLocations(location) {
        locationHierarchy.push(location);

        var childLocationGR = new GlideRecord('cmn_location');
        childLocationGR.addQuery('parent', location);
        childLocationGR.query();

        while (childLocationGR.next()) {
            getChildLocations(childLocationGR.sys_id);
        }
    }

    getChildLocations(current.location.sys_id);

    var ciGR = new GlideRecord('cmdb_ci');
    ciGR.addQuery('location', 'IN', locationHierarchy.join(','));
    ciGR.addQuery('sys_id', '!=', current.sys_id);
    ciGR.addNotNullQuery('location');
    ciGR.query();

    while (ciGR.next()) {
        ciGR;
    }

})(current, parent);

The related list shows all CIs in the cmdb_ci_win_server class and I am not sure why. What am I missing here?

Edited to add the relationship tables. I am using Windows Server for testing, but will eventually query from a custom class.

Edited to add that I tried swapping the "Applies to table" and "Queries from table" values, now the background script still works:

var locationHierarchy = [];

function getChildLocations(location) {
    locationHierarchy.push(location);

    var childLocationGR = new GlideRecord('cmn_location');
    childLocationGR.addQuery('parent', location);
    childLocationGR.query();

    while (childLocationGR.next()) {
        getChildLocations(childLocationGR.sys_id);
    }
}

getChildLocations('8847a867c3fb06100240d1ec7a013164');

gs.info('locations {0}', locationHierarchy.join(','));

var ciGR = new GlideRecord('cmdb_ci');
ciGR.addQuery('location', 'IN', locationHierarchy.join(','));
//	ciGR.addQuery('sys_id', '!=', current.sys_id);
ciGR.addNotNullQuery('location');
ciGR.query();

while (ciGR.next()) {
    gs.info('ci: {0}', ciGR.name);
}

And the relationship is showing me call CIs (even those without a location):

(function refineQuery(current, parent) {
    var locationHierarchy = [];

    function getChildLocations(location) {
        locationHierarchy.push(location);

        var childLocationGR = new GlideRecord('cmn_location');
        childLocationGR.addQuery('parent', location);
        childLocationGR.query();

        while (childLocationGR.next()) {
            getChildLocations(childLocationGR.sys_id);
        }
    }

	getChildLocations(parent.location);

	var ciGR = new GlideRecord('cmdb_ci');
	ciGR.addQuery('location', 'IN', locationHierarchy.join(','));
	ciGR.addQuery('sys_id', '!=', current.sys_id);
	ciGR.addNotNullQuery('location');
	ciGR.query();

	//I tried both with and without the following lines, same result
        //while(ciGR.next()) {
	//	ciGr;
	//}
})(current, parent);