<br><font size=2 face="sans-serif">One of the groups using RT here wants
to be able to tag tickets with</font>
<br><font size=2 face="sans-serif">one or more keywords. This is a perfect
application for a custom field</font>
<br><font size=2 face="sans-serif">set up as "Select Multiple Values";
I've set it up and it looks good.</font>
<br>
<br><font size=2 face="sans-serif">And searching for tagged tickets works
quite nicely when you're</font>
<br><font size=2 face="sans-serif">searching for a single tag value.</font>
<br>
<br><font size=2 face="sans-serif">But my users want to be able to search
for tickets that carry multiple</font>
<br><font size=2 face="sans-serif">tags, e.g. both "ABC" and
"DEF", and I haven't succeeded in getting</font>
<br><font size=2 face="sans-serif">this to work.</font>
<br>
<br><font size=2 face="sans-serif">The best I've been able to do with the
UI is to generate this Ticket</font>
<br><font size=2 face="sans-serif">SQL:</font>
<br>
<br><font size=2 face="sans-serif">Queue = 'QueueName' AND 'CF.{Keywords}'
= 'ABC' AND 'CF.{Keywords}' = 'DEF'</font>
<br>
<br><font size=2 face="sans-serif">this gets translated into the following
real SQL:</font>
<br>
<br><font size=2 face="sans-serif">SELECT DISTINCT main.*</font>
<br><font size=2 face="sans-serif">FROM Tickets main</font>
<br><font size=2 face="sans-serif"> JOIN ObjectCustomFields
ObjectCustomFields_1</font>
<br><font size=2 face="sans-serif"> ON ( ObjectCustomFields_1.ObjectId
= '0' )</font>
<br><font size=2 face="sans-serif"> OR ( ObjectCustomFields_1.ObjectId
= main.Queue )</font>
<br><font size=2 face="sans-serif"> JOIN CustomFields CustomFields_2</font>
<br><font size=2 face="sans-serif"> ON ( CustomFields_2.id
= ObjectCustomFields_1.CustomField )</font>
<br><font size=2 face="sans-serif"> JOIN ObjectCustomFieldValues
ObjectCustomFieldValues_3</font>
<br><font size=2 face="sans-serif"> ON ( ObjectCustomFieldValues_3.ObjectId
= main.id )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_3.Disabled
= '0' )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_3.ObjectType
= 'RT::Ticket' )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_3.CustomField
= CustomFields_2.id )</font>
<br><font size=2 face="sans-serif">WHERE (CustomFields_2.Name = 'Keywords'
AND CustomFields_2.Name = 'Keywords')</font>
<br><font size=2 face="sans-serif">AND (main.Status != 'deleted')</font>
<br><font size=2 face="sans-serif">AND ( ( main.Queue = '7' )</font>
<br><font size=2 face="sans-serif"> AND ( ( ObjectCustomFieldValues_3.Content
= 'ABC' )</font>
<br><font size=2 face="sans-serif"> OR
( ObjectCustomFieldValues_3.Content = 'DEF' ) ) )</font>
<br><font size=2 face="sans-serif">AND (main.Type = 'ticket') AND (main.EffectiveId
= main.id) </font>
<br>
<br><font size=2 face="sans-serif">And this SQL never returns any tickets,
since the conditions on the</font>
<br><font size=2 face="sans-serif">second and third lines from the bottom
can never be satisfied at the</font>
<br><font size=2 face="sans-serif">same time.</font>
<br>
<br><font size=2 face="sans-serif">What I want is SQL something like this:</font>
<br><font size=2 face="sans-serif">SELECT DISTINCT main.*</font>
<br><font size=2 face="sans-serif">FROM Tickets main</font>
<br><font size=2 face="sans-serif"> JOIN ObjectCustomFields
ObjectCustomFields_1</font>
<br><font size=2 face="sans-serif"> ON ( ObjectCustomFields_1.ObjectId
= '0' )</font>
<br><font size=2 face="sans-serif"> OR ( ObjectCustomFields_1.ObjectId
= main.Queue )</font>
<br><font size=2 face="sans-serif"> JOIN CustomFields CustomFields_2</font>
<br><font size=2 face="sans-serif"> ON ( CustomFields_2.id
= ObjectCustomFields_1.CustomField )</font>
<br><font size=2 face="sans-serif"> JOIN ObjectCustomFieldValues
ObjectCustomFieldValues_3</font>
<br><font size=2 face="sans-serif"> ON ( ObjectCustomFieldValues_3.ObjectId
= main.id )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_3.Disabled
= '0' )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_3.ObjectType
= 'RT::Ticket' )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_3.CustomField
= CustomFields_2.id )</font>
<br><font size=2 face="sans-serif"> JOIN ObjectCustomFieldValues
ObjectCustomFieldValues_4</font>
<br><font size=2 face="sans-serif"> ON ( ObjectCustomFieldValues_4.ObjectId
= main.id )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_4.Disabled
= '0' )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_4.ObjectType
= 'RT::Ticket' )</font>
<br><font size=2 face="sans-serif"> AND ( ObjectCustomFieldValues_4.CustomField
= CustomFields_2.id )</font>
<br><font size=2 face="sans-serif">WHERE (CustomFields_2.Name = 'Keywords'
AND CustomFields_2.Name = 'Keywords')</font>
<br><font size=2 face="sans-serif">AND (main.Status != 'deleted')</font>
<br><font size=2 face="sans-serif">AND ( ( main.Queue = '7' )</font>
<br><font size=2 face="sans-serif"> AND ( ( ObjectCustomFieldValues_3.Content
= 'ABC' )</font>
<br><font size=2 face="sans-serif"> AND (
ObjectCustomFieldValues_4.Content = 'DEF' ) ) )</font>
<br><font size=2 face="sans-serif">AND (main.Type = 'ticket') AND (main.EffectiveId
= main.id) </font>
<br>
<br><font size=2 face="sans-serif">I've run this SQL directly through the
mysql client, and it does what</font>
<br><font size=2 face="sans-serif">I want: it finds tickets where both
the ABC and the DEF keywords have</font>
<br><font size=2 face="sans-serif">been selected.</font>
<br>
<br><font size=2 face="sans-serif">So my question is: is there a way to
get this result via the web</font>
<br><font size=2 face="sans-serif">interface?</font>
<br><font size=2 face="sans-serif">It's not clear to me how to write Ticket
SQL that will do the</font>
<br><font size=2 face="sans-serif">right thing, much less how to construct
that Ticket SQL through the</font>
<br><font size=2 face="sans-serif">web UI. Is there a way to do this?</font>
<br>
<br><font size=2 face="sans-serif">I've found one work-around, but it's
not very satisfying: you can give</font>
<br><font size=2 face="sans-serif">up on the "Select Multiple Values"
type of custom field, and just use</font>
<br><font size=2 face="sans-serif">a text area. Then wild card searches
("contains") against the text</font>
<br><font size=2 face="sans-serif">area will do the right thing. But this
gives up the controlled</font>
<br><font size=2 face="sans-serif">vocabulary of the pull-down list, not
to mention the nice UI.</font>
<br>
<br><font size=2 face="sans-serif">I can imagine changing _CustomFieldLimit()
in Tickets_Overlay.pm so that</font>
<br><font size=2 face="sans-serif">the Ticket SQL that the UI generates
gets translated into SQL that does</font>
<br><font size=2 face="sans-serif">what I want. But that wouldn't be a
simple thing, so I'm hoping some</font>
<br><font size=2 face="sans-serif">one has a simpler (or better) solution.</font>
<br>
<br><font size=2 face="sans-serif">Thanks in Advance,</font>
<br>
<br><font size=2 face="sans-serif">-- <br>
Harry</font>