Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Request Tracker: Users

in sql how do you get to custom fields?

 

 

Request Tracker users RSS feed   Index | Next | Previous | View Threaded


slander at hearstsc

Jun 19, 2012, 2:31 PM

Post #1 of 2 (419 views)
Permalink
in sql how do you get to custom fields?

I am using a 3rd party BI tool for some auditing tools here, and am trying to understand the table layout.

As part of the project I would like to list for a ticket the

Ticket number, creator, creator phone number, created date, ....the queue, the business unit, and other data.

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?

Currently I have:

select Tickets.id, Tickets.Status, Tickets.Subject,
Tickets.Created,tickets_creator_user.Name as "Creator",
Tickets.Started,tickets_owner_users.Name as "Owner",
Tickets.Resolved, tickets_lastupdateby_users.Name as "Last Update By",
Queues.Name as "Queue"
from Tickets,
Users tickets_creator_user,
Users tickets_owner_users,
Users tickets_lastupdateby_users,
Queues
where Queues.id = Tickets.Queue and
Tickets.Creator = tickets_creator_user.id and
Tickets.Owner = tickets_owner_users.id and
Tickets.LastUpdatedBy = tickets_lastupdateby_users.id;

Thanks

Scott

------------------------------------------------------------------------------------
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 (cadmin [at] hearstsc) immediately by email and delete the original message.
------------------------------------------------------------------------------------


stuart.browne at ausregistry

Jun 19, 2012, 4:35 PM

Post #2 of 2 (404 views)
Permalink
Re: in sql how do you get to custom fields? [In reply to]

Hi,

If it's a single value selected in the custom field, we generally use something like:

SELECT
...
PA.Content,
...
FROM
Tickets T
JOIN Users U ON T.Owner = U.id
JOIN Queues Q ON T.Queue = Q.id
LEFT OUTER JOIN (SELECT ObjectId, Content FROM ObjectCustomFieldValues WHERE CustomField = 27 AND ObjectType = 'RT::Ticket' AND Disabled = 0) PA ON PA.ObjectId = T.EffectiveId
WHERE
...

The CustomField value is shown in RT, or you can link in CustomFields in the outer join to get the field name involved.

Stuart

From: rt-users-bounces [at] lists [mailto:rt-users-bounces [at] lists] On Behalf Of Lander, Scott
Sent: Wednesday, 20 June 2012 7:31 AM
To: rt-users [at] lists
Subject: [rt-users] in sql how do you get to custom fields?

I am using a 3rd party BI tool for some auditing tools here, and am trying to understand the table layout.

As part of the project I would like to list for a ticket the

Ticket number, creator, creator phone number, created date, ....the queue, the business unit, and other data.

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?

Currently I have:

select Tickets.id, Tickets.Status, Tickets.Subject,
Tickets.Created,tickets_creator_user.Name as "Creator",
Tickets.Started,tickets_owner_users.Name as "Owner",
Tickets.Resolved, tickets_lastupdateby_users.Name as "Last Update By",
Queues.Name as "Queue"
from Tickets,
Users tickets_creator_user,
Users tickets_owner_users,
Users tickets_lastupdateby_users,
Queues
where Queues.id = Tickets.Queue and
Tickets.Creator = tickets_creator_user.id and
Tickets.Owner = tickets_owner_users.id and
Tickets.LastUpdatedBy = tickets_lastupdateby_users.id;

Thanks

Scott

------------------------------------------------------------------------------------
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 (cadmin [at] hearstsc<mailto:cadmin [at] hearstsc>) immediately by email and delete the original message.
------------------------------------------------------------------------------------

Request Tracker users RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.