[rt-users] Problem with searching for empty custom fields with REST search method

[Jeff Ilgen]

Hi all,
I'm having trouble finding a ticket that has one or more custom fields  
that are empty.

I'm creating tickets based on an excel file.  Each record in the excel  
file represents a new RT ticket.  Once the initial load of tickets is  
complete, I intend to update the tickets on a daily basis with a  
similar excel file.  As a part of this process, I want to mark tickets  
that have custom field values that have changed since the last update  
as "changed".

I'm trying to determine if a ticket has changed by searching for a  
ticket with the values from the new input file.   If the search  
returns a ticket id, then I know that the ticket hasn't changed.  If  
it can't find a ticket, I know that the ticket hasn't changed. (Before  
I do the search, I check that a ticket does exist with the same "key"  
as the new row.   The key value is stored in a custom field.

Here is a code snippet of the call I'm making to the search method,   
if this search returns true, then I know that the ticket hasn't changed:

         my @ticket = $rt->search(type => 'ticket',
                                  query=> "'CF.Add to RRD  
Catalog'='$row->{'RRD_Catalog_Choice'}' AND
         'CF.Add to SAP Catalog'='$row->{'SAP_Catalog_Choice'}' AND
         'CF.Item Number'='$row->{'SAP_Number'}' AND
         'CF.Replaces'='$row->{'OBSOLETE'}' AND
         'CF.Qty Ordered'='$row->{'Order_Qty'}' AND
         'CF.Rqtsd Delivery Date'='$row->{'Requested_Delivery_Date'}'  
AND
         'CF.Obsolete Material Disposition'='$row-> 
{'Reasons_for_Obsolescence'}' AND
         'CF.Requires update to BOM'='$row->{Update_to_BOM}'");

Unfortunately, when I try to do a query equivalent to:

my @ticket = $rt->search(type => 'ticket',
                                  query=> "'CF.Qty Ordered'='');

Where the value for the custom field is empty(''), the search always  
fails even if both the incoming value and the current custom field are  
both empty.

Is there some way to invoke the query where the search would find a  
ticket if one or more custom fields was empty?

I could just replace all empty values with "NULL" or something like  
that, but it would be "prettier" do search the tickets as-is.

Thanks,
Jeff Ilgen