Clarity Service Management

Expand all | Collapse all

Could I get object lrel_attachments_requests in stored_query?

  • 1.  Could I get object lrel_attachments_requests in stored_query?

    Posted 01-21-2019 04:06 AM

    Hi all,

     

    In our business, we need to manage information about whether the ticket had attached attachment or not had attached? So I want a stored query to get this information quickly from Scoreboard. But I do not know how to define object lrel_attachments_requests in cm.maj, so could anyone help me this case?

     

    Thanks in advance!



  • 2.  Re: Could I get object lrel_attachments_requests in stored_query?

    Posted 01-21-2019 10:11 AM

    hi,

    you can try to build query over BREL:

    // have attachments
    attachments.id > 0

    // havent
    NOT (attachments.id > 0)

    as workaround you can publish new flag in cr factory (ex: z_have_attmnt (int)) and update it on INSERT DELETE in lrel_attachments_requests factory.

    regards,

    cdtj



  • 3.  Re: Could I get object lrel_attachments_requests in stored_query?

    Posted 01-21-2019 08:23 PM

    Hello Yen,

     

    Continuing this conversation here, as it is out of scope for Support as it involves design and customisation, but something that the Communities can advise on..

     

    I very much like Timur's suggestion of adding a flag to the Call_Req table when an Attachment is present. 

    This brings it into the realm of a normal Scoreboard Query directly against an existing object, checking one indexed field only (without linking off) and therefore should be quick and simple in operation - provided that it really catches when Attachments are added/removed, of course.

     

    Otherwise, even if you could build this as a Stored Query pointing to another table, I'm not sure that this is a good idea for performance reasons.

     

    Some rules of thumb with Stored Queries:

    • Don't use a join when you could use an "IN" statement
    • Don't create a join if you don't have to
    • "Double dotted" joins can't be done at all

     

    I think some viable options are:

    • Take this to a Jasper Report to query across tables.
      (Better with replicated database for big/busy sites).
      That is, avoid the Scoreboard entirely.
    • Write a custom SPL code (complex) or add a Label (simple) to the ticket screen along the lines of "Please add an Attachment".
      This aims to catch it before it becomes an issue.

     

    This document here contains the basic linking of the fields between tickets and their attachments:

    How can one find out what attachments are linked to which tickets in the backend database tables?

     

    This is the documentation on Stored Queries

    Scoreboard Queries - CA Service Management - 17.1 - CA Technologies Documentation 

     

    Taken together, they show the chain between Call_Req.persid, usp_lrel_attachments.cr and the Attachments (attmnt) objects/sql tables.

     

    Also, on a slightly different questions, but may be useful as a reference in this area:

    Displaying count of active attachments in tab 

    Can we list attachment file in scoreboard? 

     

    Thanks, Kyle_R.