[rt-devel] Data pulling advice

[Ashley Etherington]

Hi all looking for some advise on the best way to do the following.
Currently our RT is Setup to deal with incoming repairs.  Some of our clients require a weekly report (we work with bigger retail stores not individual users) Throughout the life of the ticket there are 3 main things that we need to pull out.  
Condition on arrival.  Diagnostics, Symptoms and Work undertaken.
Currently our engineer's use a Ticket Transactions custom field called Ticket Type and set it to one of the above,  When reading through a completed ticket you can easily pick out the information.  We also generate a worksheet based on the ticket ID and REST interface,  For a year now we have been using a very inefficient script that generates the work sheet (It manually scours the database as the Ticket Transactions custom field is not exposed to the rest interface).
Now reporting has become an issue, Because of the slow method above if we try to do a report on more than 30 ticket the script takes about 5 Minutes, and any longer just fails.
We tried to fix this by editing the REST interface and exposing the Ticket Transactions field to the comment history REST but this just resulted in even slower response for every other script we work with that uses the REST. (We are not huge perl coders but it was a one line addition to expose the field and then dumping it in local)
We though about 2 other methods, and wondered if we could get some input or other options.
1: We make our own database that is populated when a ticket type has been assigned to a comment.  This seems to be the simplest method but seems a bit extreme for what we want.2: Add a custom field (As they load a lot faster from the REST interface) That contains the full comment for each ticket type, This also seems like a waste.  We also dont want to add 4 new custom fields for each peice of information as we are already hitting 30 required custom fields (We have removed any that are never used etc)
Sorry for the story :) Hopefully we can find a resolution. Thank you!!
 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-devel/attachments/20140612/a5fa3b06/attachment.html>