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.