Disclaimer: This worked for our use case, and as with anything on the platform, there are probably many other ways to do this. (Except OOB)
One major draw back to assessments/surveys is the inability to filter the results displayed in reference metric fields. ServiceNow simply states that "Note: Reference qualifiers are not supported." and expects the end user to do the filtering based on the the entire table returned as a result. Our requirement was to pre-filter the results in a dynamic way for each specific reference field on an assessment. We created a re-usable and dynamic process to do so using a table to hold all the metric questions references, filters, and business rules that create QUERY business rules on the reference remote table to pre-filter the results before they are returned to the assessment and displayed to the user.
To accomplish this we did the following-
Create a script include that creates/removes business rules and builds the filter query.
Name: AsmtRefUtils
Client Callable: True
var AsmtRefUtils = Class.create();
AsmtRefUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {
buildBusRule: function(table){
// Check input variables to identify client or server side
if(gs.nil(table)){
table = this.getParameter('sysparm_table');
// Validate variables are not empty if so return error
if(gs.nil(table))
return "ERROR: Invalid Inputs";
}
// Make sure the rule doesnt already exist and activate it if nessessary
if(this._checkBusRuleExists(table)){
this._enableBusRule(table);
return "SUCCESS: Business Rule Exists.";
}
// Create the advanced script
var advancedScript = "(function executeRule(current, previous /*null when async*/) {\n";
advancedScript += " try{\n";
advancedScript += " var url = gs.action.getGlideURI();\n";
advancedScript += " if(url.includes(\'ASMTQUESTION\')){\n";
advancedScript += " var encodedQuery = new AsmtRefUtils().buildEncodedQuery(url);\n";
advancedScript += " if(encodedQuery)\n";
advancedScript += " current.addEncodedQuery(new AsmtRefUtils().buildEncodedQuery(url));\n";
advancedScript += " \n";
advancedScript += " }\n";
advancedScript += " }catch(e){}";
advancedScript += "})(current, previous);";
var bsRule = new GlideRecord('sys_script');
if (!bsRule.canCreate())
return "ERROR: You do not have permission to create business rules on table " + table + ".";
bsRule.initialize();
bsRule.name = 'Auto generate Assessment Ref Filter';
bsRule.description = 'Auto generated business rule to filter Assessment reference field results.';
bsRule.collection = table;
bsRule.active = true;
bsRule.advanced = true;
bsRule.when = 'before';
bsRule.action_query = true;
bsRule.script = advancedScript;
bsRule.insert();
return "SUCCESS: Business Rule Created.";
},
removeBusRule: function(table){
// Check if other reference rules exist for the table, if none remove business rule
var asmt_ref_rule = new GlideRecord('u_asmt_ref_filter');
asmt_ref_rule.addQuery('u_ref_table', table);
asmt_ref_rule.query();
if(asmt_ref_rule.next())
return;
// Remove table business rule
var bsRule = new GlideRecord('sys_script');
bsRule.addQuery('collection', table);
bsRule.addQuery('name', 'Auto generate Assessment Ref Filter');
bsRule.query();
if(bsRule.next()){
// bsRule.deleteRecord(); Inactivate vs Delete
bsRule.active = false;
bsRule.update();
}
},
buildEncodedQuery: function(url){
// Parse the question sys_id from url
var r = /ASMTQUESTION%3a([0-9a-f]{32})/;
var asmt_question_id = r.exec(url)[1];
// Get the question record
var asmt_question = new GlideRecord('asmt_assessment_instance_question');
asmt_question.addQuery('sys_id', asmt_question_id);
asmt_question.query();
if(asmt_question.next()){
// Query the Assessment Reference Filters
var asmt_ref = new GlideRecord('u_asmt_ref_filter');
asmt_ref.addQuery('u_asmt_metric', asmt_question.metric);
asmt_ref.query();
if(asmt_ref.next()){
return asmt_ref.u_asmt_ref_filter;
}
}
return;
},
_enableBusRule: function(table) {
var gr = new GlideRecord('sys_script');
gr.addQuery('collection', table);
gr.addQuery('name', 'Auto generate Assessment Reference Filter');
gr.addQuery('active', false);
gr.query();
if(gr.next()){
gr.active = true;
gr.update();
}
},
_checkBusRuleExists: function(table){
var gr = new GlideRecord('sys_script');
gr.addQuery('collection', table);
gr.addQuery('name', 'Auto generate Assessment Reference Filter');
gr.query();
if(gr.next()){
return true;
}
return false;
},
type: 'AsmtRefUtils'
});
We created a table that gave admins a dynamic way to add new filters to any assessment metric with a reference question.
Name: u_asmt_ref_filter
Adding a reference qualifier to the Assessment Metric reference field "datatype=reference^categoryISNOTEMPTY^EQ"
Then create two business rules on the "u_asmt_ref_filter" table. One that creates a business rule on the referenced table and another to remove/cleanup the business rule.
Name: "Create Table Query Business Rules"
Before/Insert
Script:
(function executeRule(current, previous /*null when async*/) {
var asmtRefUtils = new global.AsmtRefUtils();
var result = asmtRefUtils.buildBusRule(current.u_ref_table);
if(result.includes('ERROR')){
gs.addErrorMessage(result);
current.setAbortAction(true);
}
})(current, previous);
Name: "Remove Table Query Business Rules"
Async/Delete
Script:
(function executeRule(current, previous /*null when async*/) {
var asmtRefUtils = new global.AsmtRefUtils();
var result = asmtRefUtils.removeBusRule(current.u_ref_table);
if(result.includes('ERROR'))
gs.addErrorMessage("Error removing query business rule on " + current.u_ref_table);
})(current, previous);
Then we added a new module entry in Assessments > Administration > "Reference Filters" that shows the list of records in the "u_asmt_ref_filter" table.
Now any time the table is queried, the URL is examined and if its being referenced from an assessment, the assessment question/metric is identified and the filter table queried. If there are any matches to the assessment question, the results are filtered before returned to the survey/assessment. Making for a much simpler user experience taking/completing the assessments.
Admin UI-
