Database View - Joined Tables Shows Multiple Same Results

[casey_barela1]

Hey Everyone. I am trying to create a Database View that Joins the Tickets from the INC and the RITM(REQ) Tables for the customer. Since the Task table does not have Customer or Requested For we cannot easily look-up customers tickets on one table.

I have created the database view but it is showing Multiple Same Results. Shown Below - That is the real problem and i would like to only see 1 per number. (HELP PLEASE)

Below is what my Database View Setup Looks like. If someone can help me figure out why multiples show up and how to fix it it would be awesome. - I have tried the where clause ( inc_sys_id = task_sys_id || req_sys_id = task_sys_id) and also inc_number = task_number || req_number = task_number ) That had the same result and i thought it would be better to use the system ID. Also if i remove the clause (req_requested_for = inc_caller_idreq_requested_for = inc_caller_id) From incident or move it to task table i don't get any data back.

Also bonus points. If you can figure a way to do this with the req_item table instead of the sc_request table by dot walking or some other fashion that would be amazing. What we really want to see is just the Requested Items and Incidents for one User all on one Table. But since the requested_for is only truly on the sc_request table that is all i was able to do.

[casey_barela1]

Ok that was perfect with the INC and REQ Tables. Just floundering with the RITM's and SC_Tasks..

I know I must be close, and sorry I keep reaching back out.

Here is what I have for the Scheduled Job - On Demand.

var ritm = new GlideRecord('sc_req_item');

ritm.addQuery('u_requestor,'');

ritm.query();

while (ritm.next()) {

      ritm.u_customer = request.u_requestor;

      ritm.setWorkflow(false);

      ritm.update();

      var scTask = new GlideRecord('sc_task');

      scTask.addQuery('request_item', ritm.sys_id);

      scTask.query();

      while (scTask.next()) {

                  scTask.u_requestor = current.u_requestor;

                  scTask.setWorkflow(false);

                  scTask.update();

      }

}

[Slava Savitsky]

How about something like this. Make sure this code goes after the bit where you populate 'u_requestor' field for REQ records.

var ritm = new GlideRecord('sc_req_item');
ritm.addQuery('u_requestor', '');
ritm.query();


while (ritm.next()) {
         ritm.u_requestor = ritm.request.u_requestor;
         ritm.setWorkflow(false);
         ritm.update();
}


var scTask = new GlideRecord('sc_task');
scTask.addQuery('u_requestor', '');
scTask.query();


while (scTask.next()) {
         scTask.u_requestor = scTask.request_item.u_requestor;
         scTask.setWorkflow(false);
         scTask.update();
}

[casey_barela1]

I was just making those changes when you posted it. Thanks again for all the tuns of help Slava. I am fully set now and If there is another user having the same issue feel free message me so I can spread the knowledge.

[Jon23]

Thanks Slava for all your help on this.

One thing I noticed when testing the script to update all the old tickets is the 'Updated/Updated by' fields get...... updated.

This is something that is probably not desired, so using the 'autoSysFields(false)' method will disable the update to the fields: sys_updated_by, sys_updated_on, sys_mod_count, sys_created_by, and sys_created_on

var incident = new GlideRecord('incident');  

incident.addQuery('u_requestor','');  

incident.query();  

while (incident.next()) {  

        incident.u_requestor = incident.caller_id;  

        incident.setWorkflow(false);

        incident.autoSysFields(false);

        incident.update();  

}  

[casey_barela1]

Finally Got to put this in another environment and the incident.autoSysFields(false); worked like a charm.

Thanks again J