Hi Bhavana,
I did find a solution. It may not be the best, but it works. I don't have a lot of information based on what you have given me as your requirement, but I can give you more information on my requirements and what I did in case that helps.
Our customers use the Service Portal to order their items. If the customer knows how it will be paid for, they will enter the financial information into the MRVS on the Service Portal form before submitting it. Any catalog item that has a financial impact also needs departmental approval before it is fulfilled. The department approver has to review the financial data on the submitted RITM and if it needs changing (additions, modifications or deletions) it is done in the MRVS variable on the RITM.
The data from a MRVS is stored in the Multi Row Question Answer (sc_multi_row_question_answer) table and it does not track changes. If any data changes in the same MRVS for the same parent ID (RITM in my case), then it is deleted and the new data is entered. Even though it has a row index, you can't use this as a unique id since the data is always deleted if it is changed. The data is stored with the sys_id of the related tables, you do have to add code to get to the field name and data value that is entered.
Here is what I did:
1. I created a custom table to meet our business requirements of retaining the original, submitted data and add the modified/added/deleted data from the approver. We also needed to create multiple Financial reports and it's much easier to use the custom table for the reports than try to use the variables from the RITM in the report.
2. I added the custom table as a related list to the RITM - mostly for viewing from the RITM, it's not a necessary step.
3. I created two business rules on the sc_multi_row_question_answer; a before BR to remove the MRVS deleted data from the custom table and an after BR to insert the MRVS data into the custom table.
Business rules:
1. Sync deleted rows with the custom table:
Before BR with a filter condition on the name of the multi row variable set
(function executeRule(current, previous /*null when async*/ ) {
// Add your code here
var fund = new GlideRecord('u_custom_table');
fund.addQuery('u_requested_item', current.parent_id);
fund.query();
while (fund.next()) {
fund.deleteRecord();
}
})(current, previous);
2. Insert into custom table:
After BR with a filter condition on the name of the multi row variable set.
(function executeRule(current, previous /*null when async*/ ) {
var question = current.getValue('item_option_new');
var fund = new GlideRecord('u_its_funding');
fund.initialize();
fund.setValue('u_requested_item', current.parent_id);
switch (question) { // for each variable in the MRVS, set the value in the custom table
case "ba6d3fa01b9201104cf78622604bcb91": // this is the sysid in item_option_new for the variable name
fund.setValue('u_custom_table_field', current.value);
default:
break;
}
fund.insert();
}
})(current, previous);
