In my scoped app I have a case where I want to use certain sys_choice elements from the Hardware table in a decision table. I want the user to map a vendor part id to values from Manufacturer and Equipment type, two Choice fields on the Asset table. In other words I want to limit the user's selections to certain sys_choice elements from the global scope:
But I find that Workflow studio will not let me use a sys_choice from outside my scope.
So if I cannot use those sys_choice rows directly, I will use them indirectly, by giving the DT its own choice lists, and running a nightly job to overwrite these with the values from the asset table. This update will run twice, once for Equipment Type, once for Manufacturer.
But I am at a loss to figure out how you do this in the DecisionTable API.
Actually you can. But the API is not especially easy to understand:
It's easy to add a new choice to my embedded choice list.
// Get the decision table sys_id
var gr = new GlideRecord("sys_decision");
gr.addQuery("name", "Models");
gr.addQuery("sys_scope", "54e80a33933ed610a96bb6cd1dba1013");
gr.setLimit(1);
gr.query();
gr.next();
var dt_sys_id = gr.getValue("sys_id");
// get the equipment type id and values
var dtapi = new sn_dt.DecisionTableAPI();
var dt = dtapi.getDecisionTable(dt_sys_id);
var equipment_type_element = dt.answerElements.filter(function (e) {
return e.type == "choice" && e.name == "u_equipment_type";
})[0];
var elements = [{
"answerElementId": equipment_type_element.id,
"label": "Zap",
"order": 40,
"value": "zapzap"
}];
var result = dtapi.createAnswerElementChoices(dt_sys_id, elements);
gs.info(JSON.stringify(result));
But my requirement is not so much to add values but synchronize my DT choices with certain choices in the Asset table, to treat the asset table as the source of truth and keep the DT updated accordingly. And I do not see how you can selectively delete or update an individual choice. There are methods that appear to support this:
updateChoice (String choiceID, Object choice)
But that first param calls for a sys_id, the sys_id of the choice ("Point of Sale" for example) that you want to update, and I don't see how you get that without leaving the API to go rooting around the underlying table, hoping you identified the right record (there might be any number of "Point of Sale" choices). I am thoroughly confused.
I'm going to try making a copy of the entire answer element, then call this:
updateAnswerElement(String answerElementID, Object answerElement)
Two things are key to understanding how to update the choices in your DT:
1. The choices in your DT are stored in sys_choice, where the name is "var__m_sys_decision_multi_result_element_" + <decision table sys_id>. So you can obtain them through GlideRecord.
2. They are also exposed through the DecisionTable api, and here you get the sys_id of the gr they come from.
So if you want to update your DT’s choice list for answer element x, you will need:
- The decision table’s sys_id
- The sys_id of your answer element (“Manufacturer”). You add this to each k/v pair you want to add.
- An array of the value/label pairs from sys_choice that you consider the source of truth.
- The current choices, via the api, so you can check if they already match.
In the code samples below I am dropping all existing existing choices and creating new ones. This is a brute force approach and is probably inefficient. But I do not see a simple way to do a selective insert/update/delete. This is "1 AM code" so not quite production ready.
Get the table’s sys_id.
var gr = new GlideRecord("sys_decision");
gr.addQuery("name", "Models");
gr.addQuery("sys_scope", "54e80a33933ed610a96bb6cd1dba1013" /* Mapper POC */);
gr.setLimit(1);
gr.query();
gr.next();
var dt_sys_id = gr.getValue("sys_id");
Getting the answerElement sys_id
When you read your DT’s content through the api, it’s the id of the answerElement whose type is “choice” and whose name is “<whatever>”.
var gr = new GlideRecord("sys_decision");
gr.addQuery("name", "Models");
gr.addQuery("sys_scope", "54e80a33933ed610a96bb6cd1dba1013");
gr.setLimit(1);
gr.query();
gr.next();
var dt_sys_id = gr.getValue("sys_id");
var dtapi = new sn_dt.DecisionTableAPI();
var dt_data = dtapi.getDecisionTable(dt_sys_id);
gs.info(JSON.stringify(dt_data));
So:
// [{
// "label": "-- None --",
// "value": "",
// "order": 1
// },
// {
// "label": "Lenovo",
// "value": "lenovo",
// "order": 2
// },...]
var dt_data = dtapi.getDecisionTable(dt_sys_id);
var manufacturer_choices = dt_data.answerElements.filter(function (e) {
return e.type == "choice" && e.name == "u_manufacturer";
})[0].choices;
A utility for reading the choices from your answer element:
var ChoiceValues = Class.create();
ChoiceValues.prototype = {
_elements: [],
initialize: function(dt_sys_id, elementName) {
this._elements = this._fetch(dt_sys_id, elementName);
},
_fetch: function(dt_sys_id, elementName) {
this._elements.length = 0;
var dtapi = new sn_dt.DecisionTableAPI();
var dt_data = dtapi.getDecisionTable(dt_sys_id);
var element = dt_data.answerElements.filter(function(e) {
return (e.type == "choice" && e.name == elementName);
})[0];
return element.choices;
},
getElements: function() {
return this._elements;
},
type: 'ChoiceValues'
};
Dropping Current Choices
I do not see a simple way to selectively update/delete/insert choices while preserving ABC order. But dropping all choices is simple, if you remember that your DT’s choice list is stored in sys_choice where name is "var__m_sys_decision_multi_result_element_" + <dt_sys_id>.
_dropChoices: function(dtapi, dt_sys_id, elementName, values) {
values.forEach(function(e) {
var gr = new GlideRecord("sys_choice");
gr.addQuery("name", "var__m_sys_decision_multi_result_element_" + dt_sys_id);
gr.addQuery("element", elementName);
//gr.addQuery("language", "en");
//gr.addQuery("inactive", false);
gr.addQuery("value", e);
gr.setLimit(1);
gr.query();
if (gr.next()) {
var response = dtapi.deleteChoice(gr.getValue("sys_id"));
gs.info(JSON.stringify(response));
} else {
gs.info("could not find a row for " + e);
}
});
},
I have not found a way to do this without touching the table.
But here I start from an empty list:
var gr = new GlideRecord("sys_decision");
gr.addQuery("name", "Models");
gr.addQuery("sys_scope", "54e80a33933ed610a96bb6cd1dba1013" /* Mapper POC */);
gr.setLimit(1);
gr.query();
gr.next();
var dt_sys_id = gr.getValue("sys_id");
var model = new <scope>.ChoiceValues(dt_sys_id, "u_manufacturer").getElements();
gs.info(JSON.stringify(model));
Result:
[{"label":"-- None --","value":"","order":1}]
Updating the choicelist
Here’s a first pass at a script for reading certain sys_choice rows:
var Model = Class.create();
Model.prototype = {
_table: "",
_element: "",
_language: "en",
_encodedQuery: "",
_limit: 1000,
_elements: [],
initialize: function(table, element, language, encodedQuery, limit /* optional */ ) {
this._table = table;
this._element = element;
this._encodedQuery = encodedQuery;
if (language) this._language = language;
if (encodedQuery) this._encodedQuery = encodedQuery;
if (limit) this._limit = limit;
this._fetch();
},
// unordered
_fetch: function() {
this._elements.length = 0;
var gr = new GlideRecord("sys_choice");
gr.addQuery("name", this._table);
gr.addQuery("element", this._element);
gr.addQuery("language", this._language);
gr.addEncodedQuery(this._encodedQuery);
gr.setLimit(this._limit);
gr.query();
while (gr.next()) {
this._elements.push({
"label": gr.getValue("label"),
"value": gr.getValue("value")
});
}
},
getElements: function() {
return this._elements;
},
type: 'Model'
};
I will import just “ELO”, because I need to work on handling data duplication in my choice list:
var model = new <scope>.Model("alm_asset", "u_manufacturer", "en", "inactive=false^label=ELO").getElements();
gs.info(JSON.stringify(model));
[
{"label":"ELO","value":"elo"},
{"label":"ELO","value":"elo_possystem"},
{"label":"ELO","value":"elo_monitor"}
]
To set these up as choices, we just need to find and add the sys_id of the answerElement (“Manufacturer”) they will belong to. This goes in a new property named answerElementId:
_createChoices: function(dtapi, dt_sys_id, elementId, referenceObjects) {
var order = 0;
var choices = [];
choices = referenceObjects.map(function(e) {
order += 10;
return {
"answerElementId": elementId,
"label": e.label,
"order": order,
"value": e.value
};
});
var response = dtapi.createAnswerElementChoices(dt_sys_id, choices);
return response;
},
Here’s a utility that does the whole job:
var ChoiceSynchronizer = Class.create();
ChoiceSynchronizer.prototype = {
initialize: function() {},
// Synchronize the choices under answerElement @elementName to match the values and labels in @referenceObjects
Run: function(dt_sys_id, elementName, referenceObjects) {
var dtapi = new sn_dt.DecisionTableAPI();
var dt_data = dtapi.getDecisionTable(dt_sys_id);
var response;
// Test 1: Are there any current elements not matching @reference?
// Test 2: Are there any elements in @reference not also in the answerelement?
// if either test returns true, drop all the choices on @elementName
// and create new ones, one for each entry in @referenceObjects
var test1 = this._hasCurrentChoicesNotInReference(dt_data, elementName, referenceObjects);
var test2 = this._hasReferenceObjectsNotInCurrentChoices(dt_data, elementName, referenceObjects);
if (test1 || test2) {
// gs.info("running _dropChoices");
// Get all the choice values under this element now. Drop these choices.
var values = this._getCurrentValues(dt_data, elementName);
this._dropChoices(dtapi, dt_sys_id, elementName, values);
// gs.info("running _createChoices");
// populate new choices
// For this we need the element id
var elementId = dt_data.answerElements.filter(function(e) {
return e.type == "choice" && e.name == elementName;
})[0].id;
response = this._createChoices(dtapi, dt_sys_id, elementId, referenceObjects);
gs.info(JSON.stringify(response));
}
},
// Return all the choices in the current answer element (but filter
// out '-- None --' which has a value of "")
_getCurrentValues: function(dt_data, elementName) {
var choiceElement = dt_data.answerElements.filter(function(e) {
return e.type == "choice" && e.name == elementName;
})[0];
var choices = choiceElement.choices;
return choices.map(function(e) {
return e.value;
}).filter(function(e) {
return e != "";
});
},
_getCurrentChoicesNotInReference: function(dt_data, elementName, referenceObjects) {
var reference_values = referenceObjects.map(function(e) {
return e.value;
});
var answerElement = dt_data.answerElements.filter(function(e) {
return e.type == "choice" && e.name == elementName;
})[0];
var choices = answerElement.choices;
return choices.filter(function(e) {
return reference_values.indexOf(e.value) == -1;
});
},
_hasCurrentChoicesNotInReference: function(dt_data, elementName, referenceObjects) {
var reference_values = referenceObjects.map(function(e) {
return e.value;
});
var answerElement = dt_data.answerElements.filter(function(e) {
return e.type == "choice" && e.name == elementName;
})[0];
var choices = answerElement.choices;
choices = choices.filter(function(e) {
return reference_values.indexOf(e.value) == -1;
});
return choices.length > 0;
},
_getReferenceObjectsNotInCurrentChoices: function(dt_data, elementName, referenceObjects) {
var current_values = this._getCurrentValues(dt_data, elementName);
return referenceObjects.filter(function(e) {
return current_values.indexOf(e.value) == -1;
});
},
_hasReferenceObjectsNotInCurrentChoices: function(dt_data, elementName, referenceObjects) {
var current_values = this._getCurrentValues(dt_data, elementName);
var tmp = referenceObjects.filter(function(e) {
return current_values.indexOf(e.value) == -1;
});
return tmp.length > 0;
},
// Drop all the choices in the DT for this answer element
_dropChoices: function(dtapi, dt_sys_id, elementName, values) {
values.forEach(function(e) {
var gr = new GlideRecord("sys_choice");
gr.addQuery("name", "var__m_sys_decision_multi_result_element_" + dt_sys_id);
gr.addQuery("element", elementName);
//gr.addQuery("language", "en");
//gr.addQuery("inactive", false);
gr.addQuery("value", e);
gr.setLimit(1);
gr.query();
if (gr.next()) {
var response = dtapi.deleteChoice(gr.getValue("sys_id"));
gs.info(JSON.stringify(response));
} else {
gs.info("could not find a row for " + e);
}
});
},
_createChoices: function(dtapi, dt_sys_id, elementId, referenceObjects) {
var order = 0;
var choices = [];
choices = referenceObjects.map(function(e) {
order += 10;
return {
"answerElementId": elementId,
"label": e.label,
"order": order,
"value": e.value
};
});
var response = dtapi.createAnswerElementChoices(dt_sys_id, choices);
return response;
},
type: 'ChoiceSynchronizer'
};
So to run it, you look up the choices you want to use, called referenceObjects here, and pass that to the synchronizer along with the DT sys_id and the name (“u_manufacturer”) of your answerElement.
var gr = new GlideRecord("sys_decision");
gr.addQuery("name", "Models");
gr.addQuery("sys_scope", "54e80a33933ed610a96bb6cd1dba1013");
gr.setLimit(1);
gr.query();
gr.next();
var dt_sys_id = gr.getValue("sys_id");
// Get the list of manufacturers from alm_asset
var model = new <your_scope>.Model("alm_asset", "u_manufacturer", "en", "inactive=false^label=ELO");
var referenceObjects = model.getElements();
// Synchronize those choices into the Decision Table's Manufacturer column
var sync = new <your_scope>.ChoiceSynchronizer();
sync.Run(dt_sys_id, "u_manufacturer", referenceObjects);
Next I will fiddle with the labels to make duplicate values easier to deal with. For example if the label is duplicated and the value != label, then the label becomes the value. With something like that in place the list would be:
ELO
elo_possystem
elo_monitor
This will make it easy for the users to always select "elo" and get all their asset records in the same bucket.
