<html>
<head>
<style>
P
{
margin:0px;
padding:0px
}
body
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body>Here is the query that displays the values of custom fields on Ticket-Transactions, It took me a while to get it but here it is for the list:<BR>
<BR>
<BR>
SELECT<BR><FONT face="">T.Id</FONT>, <BR><FONT face="">OCFV.ObjectId</FONT>, <BR><FONT face="">Tr.ObjectId</FONT>,<BR><FONT face="">Tr.Type</FONT>,<BR>T.queue,<BR>T.subject,<BR>T.Status,<BR>T.Resolved,<BR>CF.Name,<BR>OCFV.Content <BR>FROM ((Tickets T <BR>LEFT JOIN Transactions TR on ((<FONT face="">Tr.objectid</FONT> = <FONT face="">t.id</FONT>))<BR>LEft join ObjectCustomFieldValues OCFV on ((<FONT face="">OCFV.Objectid</FONT> = <FONT face="">tr.id</FONT>))<BR>left join Customfields CF on ((CF.id = <FONT face="">OCFV.CustomField</FONT>))))<BR>WHERE <BR>T.Status = 'Resolved'<BR>and OCFV.objecttype = 'RT::TRansaction' <BR>and <FONT face="">TR.type</FONT> = 'Comment' <BR>and <FONT face="">T.id</FONT> = '1127'<BR>
<BR>
One thing to notice is the left join on OCFV.ObjectId is equal to Transactions.ID (There seem to be a confusion when it comes to Transactions.ObjectID and ObjectCustomFieldValues.ObjectID: They are not the same, nor they point to each other)<BR>
<BR>
Suggestions on how to improve this query are more than welcome....<BR>
<BR>
Carlos<BR>
<BR>
<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>