Cloned HR case reporting

[si21]

Hi experts,

Our agents use Clone HR case UI action. After cloning case, work note gets updated with the parent case number.

But there is no track of how many cases are cloned or what is the parent case number.

They need a report for this scenario. I have checked that there is no way for filtering worknotes with 'cloned case of..' for reporting.

Could you please suggest and guide me the best way to achieve this requirement.

TIA

[Sandeep Rajput]

@si21 Try the following and see if it works.

 var grJournal = new GlideRecord('sys_journal_field');
  grJournal.addQuery('name', 'STARTSWITH', 'sn_hr_'); // Filter for worknotes related to HR
  grJournal.query();

  while (grJournal.next()) {
    var worknote = grJournal.getValue('value');

    // Regex to find "Cloned case of HRC[number]" and extract the number
    var regex = /Cloned case of HRC(\d+)/;
    var match = regex.exec(worknote);

    if (match && match[1]) {
      var hrCaseNumber = 'HRC'+match[1];

      // Query the sn_hr_case table to find the HR Case record
      var grHRCase = new GlideRecord('sn_hr_core_case'); // Replace 'sn_hr_core_case' with your actual HR Case table name
      grHRCase.addQuery('sys_id',grJournal.getValue('element_id')); // Assuming 'number' field holds the HR Case number
      grHRCase.query();

      if (grHRCase.next()) {
        // Update the u_clone_of field on the HR Case record
        grHRCase.setValue('u_clone_of', hrCaseNumber); // Assuming 'element_id' is the sys_id of the original HR case.  If you want to store the cloned case number then use hrCaseNumber.

        //Important: Add a check to prevent infinite loop if u_clone_of is used for the cloned case number
        if(grHRCase.u_clone_of.nil()){ //Check if the field is empty before updating to prevent potential loop if the target case already has a value in the field
          grHRCase.update();
          gs.info('Updated HR Case: ' + grHRCase.getValue('number') + ' with cloned case: ' + hrCaseNumber);
        } else {
           gs.info('HR Case: ' + grHRCase.getValue('number') + ' already has a value in u_clone_of. Skipping update.');
        }


      } else {
        gs.warn('HR Case with number ' + hrCaseNumber + ' not found.');
      }
    }
  }

[Robbie]

Hi @si21,

It is possible to report on work notes or comments when you create a database view (a link) between the sys_journal_field table and the sn_hr_core_case.

Take a look at the following SN knowledge article demonstrating this for the incident table and simply swap incident out for the HR Case table:

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0696891

To help others (and for me to gain recognition for my efforts), please mark this response correct by clicking on Accept as Solution and/or Kudos.



Thanks, Robbie

In case the link breaks in the, here's an extract of the article:

Issue
In the scenarios where you may want to tie comments to a particular task. You can do this by creating a database view to join the tables where they are stored.

Release
All

Cause
Work Notes and Additional Comments are stored in a table named "sys_journal_field". You can report directly on that table, but for a more meaningful report, you can tie those comments to a particular task (an incident, for example) by creating a database view, joining the "task" table to "sys_journal_field" on element_ID = sys_id.

Resolution
Steps to create a database view joining the 'task' table to the 'sys_journal_field' table below:

1. Navigate to System Definition > Database Views. Click "New"
2. Give it a name, and then a description and label if you'd like. Click "Submit".
3. Open the newly created view, and click "New" next to "View Table" and add table "sys_journal_field" with a "Variable prefix" of "jou". Click "Submit".
4. Now open the "sys_journal_field" View Table, and add View Fields "Element", "Element_id", and "Value".
4. Add a second View table "task", with Variable prefix "tsk", and "Where clause" tsk_sys_id = jou_element_id.
5. Personalize the form and add the "Left join" field. Check it to make it "true". Click "Submit".
6. Open the "task" View Table, and add View Fields "number", "sys_id", and "sys_created_on" (and anything else that you would like to see in a report). Click "Submit"
7. Create a report against the new Database View.

[si21]

Hi @Robbie , I have created database view but parent case number is a hyperlink.