[rt-users] 0 tickets found when using custom fields

[Kenneth Crocker]

Erik,

	I believe that the reason your inital SQL failed was that when you 
specify a "literal" (CF.XXX LIKE 'FFF') then to code LOOKS for that 
literal in the field, not the translated value of what NULL means. 
Looking for 'NULL' will LITERALLY look for the letters NULL, not the hex 
value of nulls (CF.XXX IS NULL). Anyway, I THINK that is why it failed.


Kenn
LBNL

On 3/27/2008 5:46 AM, Peterson, Erik wrote:
>> From: Arkadiusz Jakubas <ajakubas at arces.net>
>> Date: Thu, 27 Mar 2008 11:47:24 +0100
>> To: <rt-users at bestpractical.com>
>> Subject: [rt-users] 0 tickets found when using custom fields
>> I extracted sql query ( 'CF.{Approval}' LIKE '1. Pending' ) :
> 
> 
> Hi,
> 
> I found yesterday, that I could solve a similar problem by making sure that
> I used the ³is² and ³isn¹t² conditions instead of ³contains² and ³doesn¹t
> contain² when using CustomFields that were chosen from dropdown select
> options.  I was searching for CF with {No Value}, so it may be different for
> you.  This changed:
> 
>   (CF.{Center} LIKE 'NULL')
> 
> into
> 
>   (CF.{Center} IS NULL)
> 
> and that seemed to return the correct tickets.  Not exactly sure why, but
> hopefully it can at least help get the problem solved.
> 
> I believe the number of NULL values in your return is because of the LEFT
> JOIN not finding any matches in the ObjectCustomFields_1 and
> ObjectCustomFields_2 (which also produces the COUNT being 0).
> 
> Hope that helps,
> Erik
> 
> --
> Erik Peterson
> Manager, Project Technology Services
> Education Development Center, Inc.
> http://main.edc.org
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
>