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

[mike]

Yeah, I was afraid of it being a potentially Really Stupid Idea - I'm no
Perl idiot, nor am I a real Perl hacker, so I am definitely concerned about
this perhaps not being the best solution to the problem I'm trying to
solve.  I am going to chew this over and wait for some more feedback from
the devs and generally more clueful RT folk.  I have a test RT box that I
can break six ways to Sunday, so this would absolutely go through a good
hammering in test before ever seeing our production RT system...I'm pretty
sure I'd be strung up if I hurt the prod system, the upgrade to 3.6.3 was
interesting enough (too much magic sledged in the old version from previous
employees :)).  The lack of linkage between the two disparate DBs isn't too
large a problem, but I would definitely need to find a clever way of keeping
it updated at least two or three times per day, so I'll need to do some more
thinking on that, too.  at first glace, rt-crontool doesn't seem to be my
answer to that, but i had great hope for about 30 seconds :)  Thanks for the
great response, I clearly have a lot more thinking to do!

cheers,
mike



On 5/8/07, Gene LeDuc <gleduc at mail.sdsu.edu> wrote:
>
> 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
>
>


-- 
--
One way or another, everyone stops bleeding.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070508/b51e22ea/attachment.htm>