I need a fool proof way to query the activity for sys_journal_field table for comments

[Community Alums]

Hello everyone, I need help with the following.  For a SCTASK that we produce for asset recovery, I have in place an action in the Flow designer that will look at any comment in the last ten days from the SysID and the requested from field on the sctask that is created from the REQ for a catalog item.

Here is my code:

(function execute(inputs, outputs) {

  var gr = new GlideRecord('sys_journal_field');
   // gr.addEncodedQuery('sys_created_onRELATIVEGT@minute@ago@5^element_id='+ inputs.sysid + '^sys_created_by=' + inputs.requested_for );
  
  //Comment LINE 4 Before Production so we get the right information being tested
  gr.addEncodedQuery('sys_created_onRELATIVEGT@dayofweek@ago@10^element_id='+ inputs.sysid + '^sys_created_by=' + inputs.requested_for );
  
  gr.orderByDesc('sys_created_on');
  gr.query()
  if (gr.getRowCount() >= 1) {
      outputs.tf = true
    } else {
      outputs.tf = false
    }
})(inputs,outputs);

This is designed with an encoded query to see if the sys_created_on date is relative to 10 days ago and the sys_created_by is the requested user.
If the sctask is changed on the requested for field, the script will fail as the flow has started as soon as the SCTASK is created.  Meaning that some of the support personnel have to change the Requested for on the ticket after the flow starts, which I believe is messing with the flow process as it's no longer looking at the original values.

The query is pretty simple as it returns a true or false so it can follow an appropriate path.  We can't have it look for any comments as it needs to be from whoever is in the requested for field (which is a reference field).  Is there any way to accomplish this so that it will not grab what is captured in the requested for field when it's created, and it will grab what is currently in the requested field?

Open to suggestions.  The basic request is I need to see if the person in the requested field on an SCTASK has made a comment or responded via email and, if so (within that timeframe), make the value true or false.

[John Dahl]

Update the trigger to be when the Requested For field is updated rather than when the record is created.

[Maik Skoddow · Accepted solution]

Hi

basically it looks good, but the code is highly imperformant. Please be aware that table sys_journal_field will have many records and therefore I'm wondering why you have to sort if you just want to know whether there is a record or not.

And also getRowCount() is a performance killer. Instead write:

outputs.tf = gr.hasNext();

Maik

[Community Alums]

Maik, thank you for the information.  I'm not sure why I wrote the sort on the query.  I'm going to try the outputs.tf = gr.hasNext() and see if that helps.  I think before for the sort, I was trying to get the dates that were most current, but looking a the code now I do see I really don't need that piece of it.

Thanks again.  I'll mark as Correct as soon as I test it out.