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

dlively · ‎07-01-2019

Slava,

I am very new to SN. Where would I put this business rule?

thank you,

dlively

Chris M3 · ‎04-29-2014

I'm trying to figure out exactly what you're trying to do. Are you looking for a view of incident and request item records for a particular user?

Joining the data together in a database view is going to create multiple records because it's not a 1:1 relationship. If a user has 2 Incidents & 5 request items, the result will be 10 rows of data, with each incident listed 5 times(one for each request item).

It sounds like all you really need is a view of the task table, and flip on the property mentioned above.

task_type=incident & customer=Joe OR

task_type=request item & requested_for=Joe

garyopela · ‎07-30-2014

I agree with Chris. He points out why you are seeing each record four times.

Because Inc is an extension of Task, whenever you jion the two you will end up showing each incident twice for each of the records returned, so you see 4.

We have that property mentioned above turned on, and it works great. You can write a report against the task table and see the fields which are only on the exteded tables alongside the fields on the base table. Granted, your report might be a bit ugly because it shows all hte fields on the report. So for instance, if you have FieldA on Inc and FieldB on sc task, then both show in the report, and the Incs will have data in FieldA but have FieldB blank, and vice versa for the sc tasks.

cbweiner · ‎07-30-2014

We have taken an approach similar to Slava's and would recommend it over the database view. If you want to continue with a database view, your table entries need to be the following:

Table Order Prefix Where Clause Left Join

task 100 task false

incident 200 inc task_sys_id = inc_sys_id true

sc_request 300 req task_sys_id = req_sys_id true

You can then show both the Customer & Requested For fields at the same time. This is not very efficient, though. Nor is it scalable as you look to add new processes beyond incident & request.