Good Morning, We are trying to find a Rest API that will link the "sc_task" or the "sc_req_item" tables to the variables and the answers to these so we can create a report in PowerBI. I believe the table is "sc_item_option_mtom" but I am unable to find a way to link this to the correct table to link all things together. We need to pull out 1 particular field in the variables list and link to the request so we have all details.
Please create a Scripted Rest API for this and edit the script as per your field requirement.
step1:
Create a scripted Rest API:
step 2 create one resource record under this scripted rest server.
Then add the below script and modify it as per your requirement:
(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
// implement resource here
var result = {};
var number = request.body.data.number.toString();
if (number != '') {
var ritm = new GlideRecord('sc_req_item');
ritm.addQuery('number', number);
ritm.query();
if (ritm.next()) {
variable = {};
//variable.request_for = ritm.variables.requested_for.getDisplayValue();
//variable.costcenter = ritm.variables.costcenter.getDisplayValue();
variable.requested_for=ritm.request.getDisplayValue();
result.variables = variable;
result.status = 'success';
} else {
result.status = 'fail';
result.reason = 'Given number not avaibale in the table.';
}
} else {
result.status = 'fail';
result.reason = 'number is empty.';
}
return result;
})(request, response);output as per my script:
Please mark it correct and helpful if my efforts really help you.
