<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-AU link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='color:#1F497D'>Hi,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>If it's a single value selected in the custom field, we generally use something like:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>SELECT<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>…<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> PA.Content,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>…<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>FROM<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> Tickets T<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> JOIN Users U ON T.Owner = U.id<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> JOIN Queues Q ON T.Queue = Q.id<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> LEFT OUTER JOIN (SELECT ObjectId, Content FROM ObjectCustomFieldValues WHERE CustomField = 27 AND ObjectType = 'RT::Ticket' AND Disabled = 0) PA ON PA.ObjectId = T.EffectiveId<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>WHERE<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>…<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>The CustomField value is shown in RT, or you can link in CustomFields in the outer join to get the field name involved.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Stuart<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] <b>On Behalf Of </b>Lander, Scott<br><b>Sent:</b> Wednesday, 20 June 2012 7:31 AM<br><b>To:</b> rt-users@lists.bestpractical.com<br><b>Subject:</b> [rt-users] in sql how do you get to custom fields?<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US>I am using a 3<sup>rd</sup> party BI tool for some auditing tools here, and am trying to understand the table layout.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>As part of the project I would like to list for a ticket the<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Ticket number, creator, creator phone number, created date, ….the queue, the business unit, and other data.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>I have figured out how to join the data from tickets, queues, and users tables, but I am not getting very far on the custom fields. In my example above, I have a custom field “Business Unit” and it has several different potential values. Can anyone suggest how I might add that to my query?<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Currently I have:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>select Tickets.id, Tickets.Status, Tickets.Subject, <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Tickets.Created,tickets_creator_user.Name as "Creator", <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Tickets.Started,tickets_owner_users.Name as "Owner", <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Tickets.Resolved, tickets_lastupdateby_users.Name as "Last Update By",<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Queues.Name as "Queue"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>from Tickets,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Users tickets_creator_user,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Users tickets_owner_users,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Users tickets_lastupdateby_users,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Queues<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>where Queues.id = Tickets.Queue and<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Tickets.Creator = tickets_creator_user.id and<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Tickets.Owner = tickets_owner_users.id and<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Tickets.LastUpdatedBy = tickets_lastupdateby_users.id;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Thanks<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Scott<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:12.0pt;font-family:"Courier New"'>------------------------------------------------------------------------------------<br>This e-mail message is intended only for the personal use of the recipient(s) named above. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify the Hearst Service Center (<a href="mailto:cadmin@hearstsc.com">cadmin@hearstsc.com</a>) immediately by email and delete the original message.<br>------------------------------------------------------------------------------------</span><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p></div></div></body></html>