How to remove undesired/unwanted comments from case record?

[HARI KISHAN GVS]

Hi Team, 

I have a requirement that i need to be able to delete the comments from case record.

i have a scenario in my mind that will work to remove comments but, i am not able to put it in script via UI action or via a flow.

Can anyone plese help me or provide me the sample script or flow?

I thought these below Steps will work to remove/update the comments but i can't able to convert them to a script or flow to replace the comment with ### and add a new comment stating "A comment which was not appropriate for this record has been replaced with ###"

-----------------------------------------

1 Obtain the sys_id of the record

2 Remove from sys_journal_field
Use this URI to query the journal entries: https://your-instance.service-now.com/sys_journal_field_list.do?sysparm_query=element_id=sys_id
o Replace sys_id with the sys_id of the record.
o Replace your-instance with your instance name.
refine the query, adding a "Value contains ????" condition. Replace ??? with the text you seek.

Edit the Value field of each record to remove the data.

3 Remove from sys_audit
Use this URI to query the audit entries: https://your-instance.service-now.com/sys_audit_list.do?sysparm_query=documentkey=sys_id
o Replace sys_id with the sys_id of the record.
o Replace your-instance with your instance name.
It may be helpful to add conditions to the query for "Old value contains ???" OR "New value contains ???".

Edit the text of the Old value and New value fields, removing the ???.

4 Delete the sys_history_set record
Query the sys_history_set table using this URI: https://your-instance.service-now.com/sys_history_set_list.do?sysparm_query=id=sys_id
o Replace sys_id with the sys_id of the record.
o Replace your-instance with your instance name.
Delete the record that is returned.

Thanks in advance,

Hari Kishan.

[Peter Bodelier]

Hi @HARI KISHAN GVS 

The only advice I would give you here is to push back as hard as you can against this requirement. You do not want to do this!

[Tai Vu]

Hi @HARI KISHAN GVS 

Let's try my script below.

var table_name = 'incident'; //Replace your table name
var encoded_query = 'number=INC0010002'; //Replace your encoded query 
var key_word = 'KB0000135'; //Put the keyword contains in the comment

deleteComment(table_name, encoded_query, key_word);

function deleteComment(table_name, encoded_query, key_word){
	var gr = new GlideRecord(table_name);
	gr.addEncodedQuery(encoded_query);
	gr.query();
	while(gr.next()){
		var record_id = gr.getUniqueValue();
		removeJournalEntry(table_name, record_id, key_word);
		removeAudit(table_name, record_id, key_word);
		removeHistory(table_name, record_id, key_word);
	}
}

function removeJournalEntry(table_name, record_id, key_word){
	var grJournalField = new GlideRecord('sys_journal_field');
	grJournalField.addQuery('element', 'comment');
	grJournalField.addQuery('name', table_name);
	grJournalField.addQuery('element_id', record_id);
	grJournalField.addQuery('value', 'CONTAINS', key_word);
	grJournalField.query();
	grJournalField.deleteMultiple();
}

function removeAudit(table_name, record_id, key_word){
	var grAudit = new GlideRecord('sys_audit');
	grAudit.addQuery('tablename', table_name);
	grAudit.addQuery('documentkey', record_id);
	grAudit.addQuery('fieldname', 'comments');
	grAudit.addQuery('newvalue', 'CONTAINS', key_word);
	grAudit.query();
	grAudit.deleteMultiple();
}

function removeHistory(table_name, record_id, key_word){
	var grHistory = new GlideRecord('sys_history_line');
	grHistory.addQuery('set.table', table_name);
	grHistory.addQuery('set.id', record_id);
	grHistory.addQuery('field', 'comments');
	grHistory.addQuery('new', 'CONTAINS', key_word);
	grHistory.query();
	grHistory.deleteMultiple();
}

Enjoy the result.

NOTE: (to understand History Sets)

KB0744473 - History sets - How are they generated

Cheers,

Tai Vu

[HARI KISHAN GVS]

Hi Tai,

Thanks for the script. it's working via Background script.

But, when i put the script in UI action, it's not working.

can you please let me know how can i use this script in UI action?

requirement is, if i click the UI action(say "Delete Comments"), it needs to show a textbox in which i will enter the comments that needs to be deleted.

Our script will take those comments and will be set to 'key_word' variable in the script and 'table_name' is always the Case(sn_customerservice_case) table and 'encoded_query' should be current record.

My Thoughts on how to acheive this is:

we need to create a UI Page that contains a the Text box, OK and and Cancel buttons. and we need to call that UI Page in our UI action and after clicking the Ok button in the page/dialog window, our script needs tobe executed.

Thanks in advance,

Hari Kishan.

[Tai Vu · Accepted solution]

Hi @HARI KISHAN GVS 

There you go!

#UI Page

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	<g:dialog_notes_ok_cancel
		dialog_id="confirm_delete_comments"
		textarea_id="confirm_key_word"
		textarea_label="${gs.getMessage('Keyword')}"
		textarea_label_title="${gs.getMessage('Keyword is required')}"
		textarea_name="confirm_key_word"
		textarea_onkeyup="enableButton()"
		textarea_onchange="enableButton()"
		textarea_style="height:auto; width: 100%; resize: vertical;"
		textarea_title="${gs.getMessage('Enter the keyword here')}"
		ok=""
		ok_action="processDeleteComments"
		ok_id="confirm_ok_btn"
		ok_title="${gs.getMessage('Delete Comments')}"
		ok_type="button"
		ok_style_class="btn btn-primary disabled"
		cancel_title="${gs.getMessage('Close the dialog')}"
	/>
</j:jelly>

function processDeleteComments() {
	var textArea = $("confirm_key_word");
	if (textArea){
		var key_word = textArea.value.trim();
		var table_name = g_form.getTableName();
		var encoded_query = 'sys_id=' + g_form.getUniqueValue();
		var ga = new GlideAjax('global.CLCatalogItemUtilAJAX');
		ga.addParam('sysparm_name', 'deleteComment');
		ga.addParam('sysparm_table_name', table_name);
		ga.addParam('sysparm_encoded_query', encoded_query);
		ga.addParam('sysparm_key_word', key_word);
		ga.getXMLAnswer(function(response){
			g_navigation.reloadWindow(window);
			//g_form.addInfoMessage('Delete processing...');
		});
	}
}

(function() {
	$("confirm_key_word").focus();
})();

#UI Action

function deleteComments() {
	var sysId = typeof rowSysId == 'undefined' || rowSysId == null ? gel('sys_uniqueValue').value : rowSysId;
	var dialog = new GlideModal('confirm_delete_comments');
	dialog.setTitle("Delete Comments");
	dialog.setPreference('sysparm_sys_id', sysId);
	dialog.render();
}

#Script Include

var CLCatalogItemUtilAJAX = Class.create();
CLCatalogItemUtilAJAX.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    deleteComment: function() {
        var table_name = this.getParameter('sysparm_table_name');
        var encoded_query = this.getParameter('sysparm_encoded_query');
        var key_word = this.getParameter('sysparm_key_word');
        var gr = new GlideRecord(table_name);
        gr.addEncodedQuery(encoded_query);
        gr.query();
        while (gr.next()) {
            var record_id = gr.getUniqueValue();
            this._removeJournalEntry(table_name, record_id, key_word);
            this._removeAudit(table_name, record_id, key_word);
            this._removeHistory(table_name, record_id, key_word);
        }
    },

    _removeJournalEntry: function(table_name, record_id, key_word) {
        var grJournalField = new GlideRecord('sys_journal_field');
        grJournalField.addQuery('element', 'comment');
        grJournalField.addQuery('name', table_name);
        grJournalField.addQuery('element_id', record_id);
        grJournalField.addQuery('value', 'CONTAINS', key_word);
        grJournalField.query();
        grJournalField.deleteMultiple();
    },

    _removeAudit: function(table_name, record_id, key_word) {
        var grAudit = new GlideRecord('sys_audit');
        grAudit.addQuery('tablename', table_name);
        grAudit.addQuery('documentkey', record_id);
        grAudit.addQuery('fieldname', 'comments');
        grAudit.addQuery('newvalue', 'CONTAINS', key_word);
        grAudit.query();
        grAudit.deleteMultiple();
    },

    _removeHistory: function(table_name, record_id, key_word) {
        var grHistory = new GlideRecord('sys_history_line');
        grHistory.addQuery('set.table', table_name);
        grHistory.addQuery('set.id', record_id);
        grHistory.addQuery('field', 'comments');
        grHistory.addQuery('new', 'CONTAINS', key_word);
        grHistory.query();
        grHistory.deleteMultiple();
    },

    type: 'CLCatalogItemUtilAJAX'
});

Remember use it at your own risk, the script was not being tested carefully. Enjoy! 😋

Cheers,

Tai Vu