Database View - Joined Tables Shows Multiple Same Results

casey_barela1 · ‎03-04-2014

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.

Slava Savitsky · ‎08-18-2014

In its simplest version, the code of the 'after' business rule could look like this:

var ritm = new GlideRecord('sc_req_item');
ritm.addQuery('request', current.sys_id);
ritm.query();
 


while (ritm.next()) {
         ritm.u_requestor = current.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();
         }
}

Blog: https://sys.properties | Telegram: https://t.me/sys_properties | LinkedIn: https://www.linkedin.com/in/slava-savitsky/

View solution in original post

casey_barela1 · ‎08-18-2014

Ok - That worked Perfectly - Thank you again so much Slava. -

Now to just bug you once more. How did you handle the scheduled job to update all the old records?

I have the business rules in place, could I just do an update / check on all records? I am already noticing many records are populating that field due to notifications / sla's and normal updates to the ticket so they are working as expected.

But if you could copy / paste what you used for the 1 time scheduled job. I will let your madness of post vacation emails to commence.

Slava Savitsky · ‎08-19-2014

The scheduled job is simply a combination of the code from all the 'before' business rules.

Blog: https://sys.properties | Telegram: https://t.me/sys_properties | LinkedIn: https://www.linkedin.com/in/slava-savitsky/

casey_barela1 · ‎08-19-2014

I was thinking that - However since the scheduled jobs do not have tables tied to them i was wondering how to format it.

Anyone have a guide / cheat sheet to point me to?

Slava Savitsky · ‎08-19-2014

You just loop through all tasks where requestor is empty and populate that field. Here is a bit of code that does it for incidents:

var incident = new GlideRecord('incident');


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


incident.query();



while (incident.next()) {


         incident.u_requestor = incident.caller_id;


         incident.setWorkflow(false); 


         incident.update();


}

You will need to do the same for other types of tasks. For tasks that have child tasks (e.g. RITMs), you will need to propagate requestor to the child tasks as I showed in one the previous comments (or vice versa — loop through the child tasks and copy the requestor from the parent, in which case you need to make sure you populate requestor at the top level first).

Blog: https://sys.properties | Telegram: https://t.me/sys_properties | LinkedIn: https://www.linkedin.com/in/slava-savitsky/

casey_barela1 · ‎08-19-2014

Just the start I needed. Thanks.