<html>
<head>
<style>
P
{
margin:0px;
padding:0px
}
body
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body>Hello All:<BR>
In trying to extract values from Ticket-Transactions custom fields I have the following query: (using a ODBC connection to RT's mysql database)<BR>
<BR>
SELECT <BR> T.Id,<BR> OCFV.ObjectId,<BR> T.queue,<BR> T.subject,<BR> T.Status, <BR> T.Resolved,<BR> CF.Name,<BR> OCFV.Content<BR> <BR>
FROM ((Tickets T <BR>
LEFT JOIN ObjectCustomFieldValues OCFV ON ((OCFV.Disabled = '0'))<BR>AND(OCFV.ObjectId = T.id))<BR>
LEFT JOIN CustomFields CF ON (CF.id = OCFV.CustomField))<BR>
WHERE ((T.Status = 'resolved')) <BR><BR>
with the following results:<BR>
<BR>
313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor Description Solved<BR>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Work Status Complete<BR>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Materials Used Solved<BR>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor Cost 0<BR>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Materials Cost 0<BR>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Issues None<BR><BR>
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)<BR>
<BR>
<TABLE cellSpacing=0 cellPadding=2 width="100%" border=0>
<TBODY>
<TR>
<TD class=date>Sat Jan 13 14:12:23 2007</TD>
<TD class=description>jruzinsky - Comments added </TD>
<TD class=time-taken>15 min</TD>
<TD class=actions> [<A href="https://maintenance.boynemountain.com/bm/Ticket/Update.html?id=313&QuoteTransaction=4237&Action=Respond"><U><FONT color=#0000ff>Reply</FONT></U></A>] [<A href="https://maintenance.boynemountain.com/bm/Ticket/Update.html?id=313&QuoteTransaction=4237&Action=Comment"><U><FONT color=#0000ff>Comment</FONT></U></A>]</TD></TR>
<TR>
<TD class=content colSpan=4>
<TABLE>
<TBODY>
<TR id=CF-3-ShowRow>
<TD class=label>Labor Description:</TD>
<TD class=value>
<UL>
<LI>already done </LI></UL></TD></TR>
<TR id=CF-4-ShowRow>
<TD class=label>Materials Used:</TD>
<TD class=value>
<UL>
<LI><I>(no value)</I> </LI></UL></TD></TR>
<TR id=CF-5-ShowRow>
<TD class=label>Issues:</TD>
<TD class=value>
<UL>
<LI><I>(no value)</I> </LI></UL></TD></TR>
<TR id=CF-6-ShowRow>
<TD class=label>Labor Cost:</TD>
<TD class=value>
<UL>
<LI>10.25 </LI></UL></TD></TR>
<TR id=CF-7-ShowRow>
<TD class=label>Work Status:</TD>
<TD class=value>
<UL>
<LI>Complete </LI></UL></TD></TR>
<TR id=CF-8-ShowRow>
<TD class=label>Materials Cost:</TD>
<TD class=value>
<UL>
<LI><I>(no value)</I> </LI></UL></TD></TR></TBODY></TABLE>
<TABLE>
<TBODY></TBODY></TABLE>
<DIV class=messagebody>
<DIV class=message-stanza-depth-0></DIV></DIV>
<DIV>What I am finding out is that is not matching for any of the tickets that I have.....</DIV>
<DIV>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...</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>Carlos</DIV>
<DIV> </DIV></TD></TR></TBODY></TABLE><BR><br /><hr />Hotmail to go? Get your Hotmail, news, sports and much more! <a href='http://mobile.msn.com' target='_new'>Check out the New MSN Mobile</a></body>
</html>