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

hpesata · ‎03-05-2014

Hi!

since the incident table is based on the task table, comparing the incident.sys_id with the task.sys_id will always be true. what happens if You just use the incident and sc_request tables and use the where clause "req_requested_for = inc_caller_id" within the sc_request table ?

regards

Hans

casey_barela1 · ‎03-05-2014

It loads the same type of data but it doubles up all the fields. You cannot select one short description field you have two and so forth. This is why i put the task table in there so i could have 1 Number Field, I Short Desc, 1 Desc and so forth.

Unless you think i would be good to write a statement equating all of those fields? Even when it was just the REQ / INC tables and i had the fields doubled up it still showed multiples of each ticket.

rob_pastore · ‎04-29-2014

I think your best bet may be to flip this system property:

glide.ui.list.allow_extended_fields

It's a global setting, so you may not want to do it, but what this does is allow base tables to show and use extended fields.

Basically it will make caller_id and request available on the task table, then you will be able to use task and filter to only be showing request items and incidents.

Slava Savitsky · ‎04-29-2014

Absense of a common user reference in all types of tasks is something we have been struggling with for quite a while. Eventually we created a custom field Requestor (u_requestor) on Task [task] table and a set of business rules that populate it based on the value of certain table-specific fields of the extended tables (Incident, Problem, Change). If you decide to take this approach, feel free to reach out to me for more details.

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