Get Relationship Related Lists via script and refineQuery function (query_with)

[Liam Barksdale]

I'm trying to retrieve the contents of Related Lists via script. Not any specific Related List, but any list. Many Related lists are created via sys_relationship, and while it's simple to get data from some, using the current element sys_id, the table ( query_basic_from) and the reference_field. 

However, reference_field is not always populated, and the query is further refined via script, which works fine if you are on the form of the item you are looking at, but if you are trying to get that data via script, I can't figure out how to use the query_with field in a script to get the values.

Compounding the issue, I think, is that some refined queries might call a script include, sometimes in another scope, and I have noticed that some of the OOB scripts don't always specify the scope where the script include is (I just happened to notice that the other day when looking at two different GRC relationships - one uses the scope prefix but another doesn't)

The idea I have is to get the relationship record for the table of the element you are looking at from sys_relationship, check to see if there is a value in the query_with field, then do a GlideRecord against the basic_query_from field with your element as the sys_id to check, and the refined query in an addQuery or addEncodedQuery statement. I'm just not sure how to utilise the query_with field for this purpose.

Has anyone done this? I feel like I've done a pretty exhaustive search thus far, but perhaps I missed something.

Thank you

[DrewW]

Did you pass in proper values for current and parent?  Parent = the record the list is attached to and current is a GlideRecord for the table the list is for.  So if you pass in those objects to the evaluator it will work, I have done something similar several times and it works great.

[Liam Barksdale]

Yes, I think so.

Here is what I have for the example I'm trying to use:

The relationship (sys_relataionship) : Architectural Artifacts (sys_id: 2318d81077350110407d8716be5a99d2)

Applies to table (basic_apply_to): cmdb_ci_business_app

Queries from table (basic_query_from): sn_apm_architectural_artifact

Query with (query_with):

(function refineQuery(current, parent) {

    var refGR = new GlideRecord('sn_apm_related_entities');
    refGR.addQuery('table_name', parent.getRecordClassName());
    refGR.addQuery('target_record', parent.sys_id);
    refGR.query();

    var sysIds = "";
    while (refGR.next()) {
        sysIds = sysIds + refGR.document.sys_id + ",";
    }
    sysIds = sysIds.substring(0, sysIds.length - 1);
    current.addQuery('sys_id', 'IN', sysIds);

})(current, parent);

The business app I'm using as 'parent' has one record for the relationship in a related list with sys_id '2a495cca18040300964f2ff0d21a4e88'

Here is the script I'm trying to use to populate the evaluator code:

var att = new GlideRecord('sys_relationship');
att.get('2318d81077350110407d8716be5a99d2');

var currentGR = new GlideRecord('sn_apm_architectural_artifact');

var parentGR = "2a495cca18040300964f2ff0d21a4e88"; //the business application record that I'm checking
//or, alternatively:
//var parentGR = new GlideRecord("cmdb_ci_business_app");
//parentGR.get("2a495cca18040300964f2ff0d21a4e88");


var vars = {
  "current" : currentGR,
	"parent" : parentGR
};
var evaluator = new GlideScopedEvaluator(); 
var result = evaluator.evaluateScript(att, 'query_with', vars);
gs.info("result: " + result);

And the result is 'result: null' or 'result: undefined'

[DrewW]

Parent is a GlideRecord but you have it defined as a string, so you need to use your commented out lines.

//var parentGR = "2a495cca18040300964f2ff0d21a4e88"; //the business application record that I'm checking
//or, alternatively:
var parentGR = new GlideRecord("cmdb_ci_business_app");
parentGR.get("2a495cca18040300964f2ff0d21a4e88");

The result is null because the script does not return anything.  The result should be in the modifications that where made to the GlideRecords you passed in.  So you should 

gs.info(current.getEncodedQuery());

to check if its been updated like it should have been.  If the function in the code was returning something the result would have that value.

[Liam Barksdale]

I just get an error when I add that. If I run the script that is in the refineQuery function with GlideRecords, I get a result:

var hasvalue = false;
var refGR = new GlideRecord('sn_apm_related_entities');
    refGR.addQuery('table_name', 'cmdb_ci_business_app');
    refGR.addQuery('target_record', '2a495cca18040300964f2ff0d21a4e88');
    refGR.query();

    var sysIds = "";
    while (refGR.next()) {
        sysIds = sysIds + refGR.document.sys_id + ",";
    }
    sysIds = sysIds.substring(0, sysIds.length - 1);

var aa = new GlideRecord('sn_apm_architectural_artifact');
    aa.addQuery('sys_id', 'IN', sysIds);
    aa.query();
    if (aa.next()) {
      hasvalue = true;
    }

gs.info(hasvalue);

Returns 'true'

I just can't seem to run it using evaluator.

[Liam Barksdale]

It seems the problem is there is no return value in the refineQuery functions, and getEncodedQuery() is not working for me to retrieve the query. Therefore the result just comes back as 'undefined'