[rt-users] Creating a customer drop-down multi-select menu with a remote SQL query

[Gene LeDuc]

WARNING - Trying this might be a Really Stupid idea

This might work, but it messes with the RT database directly, so before 
doing anything else you should (in this order):
   1) wait about a week to see how badly this idea gets flailed by the 
developers and the more experienced users;
   2) consider it carefully a couple dozen times before doing it;
   3) cross your fingers;
   4) cover your ears in case it explodes

That said, _I'd_ certainly try it.  But I've always been kind of a thrill 
seeker.

When you set up a "select multiple values" CF in RT, an entry for it is 
added to the CustomFields table of the rt3 database.  After you've created 
your CF and given it at least 1 value, go into that table and find the "id" 
value that goes with your CF.  Write it down here: _________

The CustomFieldValues table of the rt3 database has a record for each 
"select multiple values" entry.  What I would do is write a 27-line perl 
script that adds an entry to this table for each of the names you pulled 
from your other database with the 5-liner you wrote.  The fields you need 
to be concerned with are:
   CustomField: A constant, the number you got from looking at the 
CustomFields table and that you wrote down above
   Name: This is what shows up in the pull-down menu for your CF, put the 
names you extracted here
   Description: I'm not sure what this text field is used for or where it 
shows up other than the page used for creating custom fields
   SortOrder: Integer determines what order the items appear in your 
pull-down list

To be complete, you should also fill these fields:
   Creator: A constant - your user id (an integer) goes here
   Created: Today's date
   LastUpdatedBy: Same as Creator
   LastUpdated: Same as Created

Downsides:
   1.  It might explode.
   2.  You only get a snapshot of your other database, there's no linkage 
to keep the CF values in synch with the other database.
   3.  It might explode.

Before trying this, go back and repeat steps 1 and 2 from my first list, 
paying particular attention to step 1.

Good luck!
Gene


At 01:04 PM 5/8/2007, mike wrote:
>Still trying to feel my way around RT, and couldn't find a definitive 
>answer in any of the docs/wiki...what I aim to do is enforce some 
>consistency in customer names used in the Subject lines of tickets...I 
>figure this should make things like reporting chronic issues/customers 
>with endless problems rather simply if they're all listed the same rather 
>than the multiple abbreviations that get used.
>
>I've written a quick and nasty 5-liner in Perl that queries a MySQL table 
>on a different box, and returns a complete list of customer 
>names.  Assuming that these names will live in a drop-down custom field 
>that allows multi-selects, how would I kick off this script and feed the 
>results in to that drop-down?  I'm trying very, very hard to not sledge 
>this directly in to the RT code itself, but to bolt it in based on the 
>current frame-work so that future ugprades won't be as much a trainwreck 
>as our last one was (due to lots of customization inside RT itself).  Any 
>clues or steers in the right direction would be appreciated :)


-- 
Gene LeDuc, GSEC
Security Analyst
San Diego State University