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

[Mathew]

Having tried the direct database connection myself, I would actually
recommend against doing it even if the data comes back as expected.  Use
the API.  Makes things so much simpler.  Trust me.

Mathew
Keep up with my goings on at http://theillien.blogspot.com

Gene LeDuc wrote:
> Hi Carlos,
> 
> This doesn't solve your problem, but I ran your query (using Toad)
> against our MySQL database and it worked as expected.  Puzzling.  Maybe
> you can figure out where the "window crank missing" data came from by
> snooping the database and work backwards from there.
> 
> Regards,
> Gene
> 
> At 11:51 AM 6/22/2007, Carlos Ramon Lopez Midence wrote:
>> 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 Solved
>> 313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000
>> Work Status Complete
>> 313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000
>> Materials Used Solved
>> 313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000
>> Labor Cost 0
>> 313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000
>> Materials Cost 0
>> 313 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)
>>
>> <snip>
>>
>> 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...
>