[rt-users] Query on Ticket-Transactions Custom Fields

[Carlos Ramon Lopez Midence]

Hello All:
In trying to extract values from Ticket-Transactions custom fields I have the following query: (using a ODBC connection to RT's mysql database)
 
SELECT  T.Id, OCFV.ObjectId, T.queue, T.subject, T.Status,  T.Resolved, CF.Name, OCFV.Content  
FROM ((Tickets T 
LEFT JOIN ObjectCustomFieldValues OCFV ON ((OCFV.Disabled = '0'))AND(OCFV.ObjectId = T.id))
LEFT JOIN CustomFields CF ON (CF.id = OCFV.CustomField))
WHERE ((T.Status = 'resolved')) 
with the following results:
 
313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor Description Solved313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Work Status Complete313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Materials Used Solved313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor Cost 0313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Materials Cost 0313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Issues None
But when I look on RT's web interface for the values of ticket 313 I get different values (Which are the correct values by the way, so my query is missing something or I am not using the right tables or I don't know)
 




Sat Jan 13 14:12:23 2007
jruzinsky - Comments added 
15 min
 [Reply] [Comment]





Labor Description:


already done 

Materials Used:


(no value) 

Issues:


(no value) 

Labor Cost:


10.25 

Work Status:


Complete 

Materials Cost:


(no value) 




What I am finding out is that is not matching for any of the tickets that I have.....
Any suggestions on improving the query will be greatly appreciated or ideas in using RT's API would be welcome too, I am not a perl programmer though...
 
 
 
 
Carlos
 
_________________________________________________________________
Hotmail to go? Get your Hotmail, news, sports and much more! Check out the New MSN Mobile! 
http://mobile.msn.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070622/b339e927/attachment.htm>