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.

[Jon23]

Hi Slava, I am looking to do the same, and add a custom field to the Task table, so I would be interested in seeing your approach.

[Slava Savitsky]

This solution was designed by a good friend of mine. Nikita Mironov, I hope you do not mind me posting it here.

For "standalone" tasks that do not have child tasks (in our environment — incidents, problems, KB submissions), we have one "before" business rule per task type that records the requestor's name in the custom "u_requestor" field on insert/update.

For task types that have child tasks (in our environment — service catalog requests and change requests), we use a combination of business rules:

1. "before insert/update" business rule for the top level records (REQ or CHG)
2. "before insert" business rule for child tasks (requested items, catalog tasks, change tasks) to copy Requestor from the parent record
3. "after update" business rule for top level records to propagate changes to the child records if Requestor changes at the top level.

Besides that, we used a background script to populate u_requestor field in all tasks that had been created before we introduced this field.

[casey_barela1]

Thanks Slava,

I am finally able to get back to developing and will be able to implement this solution. Just a cpl quick questions. Did you add that field on all of the tables aka inc, ritm, reg, task, chg or did you just add it to the task table?

Second Question is more of   a Beg and Plead - Could you provide the business rules / schedule job you had for this setup. It would help a tun. Even if you just took large enough screenshots it would be awesome to have the leg work partially done.

Thanks again on setting me on the right path.

[casey_barela1]

So i figured out the very simple business rule for the update / insert for the INC, REQ, RITM, and SC Task Tables. They are all working perfectly. Now i am just running into the issue if i Change the REQ it will not pass the change down the to RITM or SC Tasks.

I know this needs to be done like Slava says below.

1. "after update" business rule for top level records to propagate changes to the child records if Requestor changes at the top level.

I am just running into how it should be formatted in the script. I know it should be a After, Update Business rule on the REQ table. Just dont know what the condition and the script would look like to update children. I know it must be super easy> but can anyone point me in the right direction.

[Slava Savitsky · Accepted solution]

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();
         }
}