<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta content="text/html;charset=UTF-8" http-equiv="Content-Type"></head><body >
Dear Scott,<br>
<br>
you can run the following query to get the list of open tickets and their comments<br>
<span style="font-weight: bold;">select vtiger_troubletickets.ticketid,
vtiger_troubletickets.title, vtiger_crmentity.smownerid,
vtiger_ticketcomments.* from vtiger_troubletickets inner join
vtiger_crmentity on
vtiger_troubletickets.ticketid=vtiger_crmentity.crmid left join
vtiger_ticketcomments on
vtiger_ticketcomments.ticketid=vtiger_troubletickets.ticketid where
vtiger_troubletickets.status='Open' </span><br>
<br>
where as if you want get the details for a single ticket then you can add <br>
<span style="font-weight: bold;"> and vtiger_troubletickets.ticketid=117 </span><br>
at the end of the previous query.<br>
Hope this helps to get the result as per your need.<br>
<br>
Thanks & Regards<br>
Mickie<br><br><br><br><br><br><br>----scott@centritechsolutions.com wrote ---- <br><br><blockquote style="border-left: 2px solid rgb(0, 0, 255); padding: 6px;">
<div>
Thanks Joe,<br><br>How would I join this to the vtiger_troubletickets table so that I can pull<br>out the corresponding ticketed, assignedto, and the title. I also need the<br>where clause to filter out only tickets that have a status of Open.<br><br>I think I need a combination of what you have and what Brian sent earlier<br><br>Select * from vtiger_troubletickets tt<br>join vtiger_crmentity ce on ce.crmid = tt.ticketid where tt.status = 'Open'<br><br><br>It looks like his suggestion pulls out all the tickets with the status of<br>Open but no comments, and yours would pull out all the comments for a<br>specific ticket.<br><br>Should I run Brian's query and then step through the result and pull out the<br>ticket Id and then execute a 2nd query using your suggestion and display the<br>data. Then go to the next result in Brian's query ?<br><br> <br>Scott Brown<br>Operations Manager<br>Main: 804-360-9753 Ext. 400<br>Fax: 1-800-851-0516<br>scott@centritechsolutions.com<br><a target="_blank" href="http://www.CentriTechSolutions.com">http://www.CentriTechSolutions.com</a><br><br>-----Original Message-----<br>From: vtigercrm-developers-bounces@lists.vtigercrm.com<br>[mailto:vtigercrm-developers-bounces@lists.vtigercrm.com] On Behalf Of Joe<br>Bordes<br>Sent: Monday, March 05, 2007 6:49 PM<br>To: vtigercrm-developers@lists.vtigercrm.com<br>Subject: Re: [Vtigercrm-developers] Ticket comments<br><br>Hi Scott,<br><br>The ticket comments are in the vtiger_ticketcomments table so you will<br>have to select in that table to get the comments you are looking for:<br><br>select comments, createdtime from vtiger_ticketcomments<br>where ticketid={yourticketid}<br>order by createdtime;<br><br>That should get the comments.<br><br>Let us know how it turns out.<br><br>Regards, Joe<br>TSolucio<br><br>El lun, 05-03-2007 a las 15:13 -0500, scott@centritechsolutions.com<br>escribió:<br>> What I need is some type of output to a table that shows comments of<br>certain tickets. What would my sql statment be to achieve this?<br>> <br>> I.E<br>> <br>> SELECT ticketid, assignedto, ticketcomments, createdtime WHERE ticket<br>status is equal to 'Open';<br>> <br>> If I have this sql statment then I would think I should be able to print<br>the results out to a html table and that would be sufficent for me. It<br>doesn't even have to be integrated into any modules. I could just call the<br>url directly such as <a target="_blank" href="http://www.crmserver.com/getTicketComments.php">http://www.crmserver.com/getTicketComments.php</a><br>> <br>> Thanks<br>> Scott<br>> <br>> ------Original Mail------<br>> From: "bharath" <bharath@vtiger.com><br>> To: <scott@centritechsolutions.com><br>> Sent: Mon, 05 Mar 2007 16:07:27 +0530<br>> Subject: Re:[Vtigercrm-developers] Ticket comments<br>> <br>> <br>> <br>> <br>> Dear Scott Brown,<br>> <br>> Thanks for your fruitful suggestion.<br>> Currently we are not supporting this feature,<br>> but we will take this in one of our future release.<br>> Please do get back to us for further details.<br>> <br>> Thanks & Regards<br>> Bharath<br>> <br>> <br>> <br>> <br>> ----scott@centritechsolutions.com wrote ---- <br>> <br>><br>Is there a way to have a report ofthe ticket comments or a way to create a<br>custom view to show all the ticketcomments on one screen? This way I can at<br>least do a print screen.<br>> <br>> <br>> Scott Brown<br>> Operations Manager<br>> Main: 804-360-9753Ext. 400<br>> Fax: 1-800-851-0516<br>> scott@centritechsolutions.com<br>> <a target="_blank" href="http://www.CentriTechSolutions.com">http://www.CentriTechSolutions.com</a><br>> <br>> <br>> <br>> _______________________________________________<br>> Reach hundreds of potential candidates - <a target="_blank" href="http://jobs.vtiger.com">http://jobs.vtiger.com</a> <br>> <br>> <br>> _______________________________________________<br>> Reach hundreds of potential candidates - <a target="_blank" href="http://jobs.vtiger.com">http://jobs.vtiger.com</a> <br>> <br><br>_______________________________________________<br>Reach hundreds of potential candidates - <a target="_blank" href="http://jobs.vtiger.com">http://jobs.vtiger.com</a> <br><br><br>_______________________________________________<br>Reach hundreds of potential candidates - <a target="_blank" href="http://jobs.vtiger.com">http://jobs.vtiger.com</a> <br>
</div>
</blockquote></body></html>