[Vtigercrm-developers] Ticket comments

[Mickie]

Dear Scott,

you can run the following query to get the list of open tickets and their comments
select vtiger_troubletickets.ticketid,vtiger_troubletickets.title, vtiger_crmentity.smownerid,vtiger_ticketcomments.* from vtiger_troubletickets inner joinvtiger_crmentity onvtiger_troubletickets.ticketid=vtiger_crmentity.crmid left joinvtiger_ticketcomments onvtiger_ticketcomments.ticketid=vtiger_troubletickets.ticketid wherevtiger_troubletickets.status='Open' 

where as if you want get the details for a single ticket then you can add 
 and vtiger_troubletickets.ticketid=117 
at the end of the previous query.
Hope this helps to get the result as per your need.

Thanks & Regards
Mickie






----scott at centritechsolutions.com wrote ---- 

                                                                                    Thanks Joe,

How would I join this to the vtiger_troubletickets table so that I can pull
out the corresponding ticketed, assignedto, and the title.  I also need the
where clause to filter out only tickets that have a status of Open.

I think I need a combination of what you have and what Brian sent earlier

Select * from vtiger_troubletickets tt
join vtiger_crmentity ce on ce.crmid = tt.ticketid where tt.status = 'Open'


It looks like his suggestion pulls out all the tickets with the status of
Open but no comments, and yours would pull out all the comments for a
specific ticket.

Should I run Brian's query and then step through the result and pull out the
ticket Id and then execute a 2nd query using your suggestion and display the
data. Then go to the next result in Brian's query ?

 
Scott Brown
Operations Manager
Main: 804-360-9753 Ext. 400
Fax: 1-800-851-0516
scott at centritechsolutions.com
http://www.CentriTechSolutions.com

-----Original Message-----
From: vtigercrm-developers-bounces at lists.vtigercrm.com
[mailto:vtigercrm-developers-bounces at lists.vtigercrm.com] On Behalf Of Joe
Bordes
Sent: Monday, March 05, 2007 6:49 PM
To: vtigercrm-developers at lists.vtigercrm.com
Subject: Re: [Vtigercrm-developers] Ticket comments

Hi Scott,

The ticket comments are in the vtiger_ticketcomments table so you will
have to select in that table to get the comments you are looking for:

select comments, createdtime from vtiger_ticketcomments
where ticketid={yourticketid}
order by createdtime;

That should get the comments.

Let us know how it turns out.

Regards, Joe
TSolucio

El lun, 05-03-2007 a las 15:13 -0500, scott at centritechsolutions.com
escribió:
> What I need is some type of output to a table that shows comments of
certain tickets.  What would my sql statment be to achieve this?
> 
> I.E
> 
> SELECT ticketid, assignedto, ticketcomments, createdtime WHERE ticket
status is equal to 'Open';
> 
> If I have this sql statment then I would think I should be able to print
the results out to a html table and that would be sufficent for me.  It
doesn't even have to be integrated into any modules.  I could just call the
url directly such as http://www.crmserver.com/getTicketComments.php
> 
> Thanks
> Scott
> 
> ------Original Mail------
> From: "bharath" <bharath at vtiger.com>
> To: <scott at centritechsolutions.com>
> Sent: Mon, 05 Mar 2007 16:07:27 +0530
> Subject: Re:[Vtigercrm-developers] Ticket comments
> 
> 
> 
> 
> Dear Scott Brown,
> 
> Thanks for your fruitful suggestion.
> Currently we are not supporting this feature,
> but we will take this in one of our future release.
> Please do get back to us for further details.
> 
> Thanks & Regards
> Bharath
> 
> 
> 
> 
> ----scott at centritechsolutions.com wrote ---- 
> 
>
Is there a way to have a report ofthe ticket comments or a way to create a
custom view to show all the ticketcomments on one screen? This way I can at
least do a print screen.
>  
> 
> Scott Brown
> Operations Manager
> Main: 804-360-9753Ext. 400
> Fax: 1-800-851-0516
> scott at centritechsolutions.com
> http://www.CentriTechSolutions.com
>  
> 
> 
> _______________________________________________
> Reach hundreds of potential candidates - http://jobs.vtiger.com 
> 
> 
> _______________________________________________
> Reach hundreds of potential candidates - http://jobs.vtiger.com 
> 

_______________________________________________
Reach hundreds of potential candidates - http://jobs.vtiger.com 


_______________________________________________
Reach hundreds of potential candidates - http://jobs.vtiger.com 

                                                         
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20070306/82e0881c/attachment-0004.html