<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1400" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Howdy,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>A while back I asked if there was a way to preserve
the values of my queue-specific custom fields during a queue change. Since
there wasn't one, I built one and it might be of some value to
someone.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Problem - The same custom fields exist in multiple
queues, but not in all queues. Since these fields are usually specific to
only one or two queues, using global custom fields isn't the best option.
However, per queue custom fields are queue specific and transparent of each
other.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Solution - Assuming that the custom fields have
identical names in both queues, compare the fields and update the ticket's
fields in the New Queue if they have siblings in the Old Queue. If any
CF doesn't have a sibling in the New Queue, comment on the ticket to that
extent.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Admittedly, my solution is a _very_ bad way to do
this - I actually had to muck with the database directly, and that's less than
ideal. If anyone has suggestions on a way around this, I'd appreciate
hearing about them. In the meantime, it works (until I upgrade...)
I'd _really_ like to see something to address this on the wish list, if
possible. IMHO, I'm can't be the only one who would find this
useful.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Be nice to my less than optimal Perl code - I write
about once every 6 months. Thanks for your time.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial
size=2>----------------------------------------</FONT></DIV></FONT></DIV>
<DIV><FONT face=Arial size=2>Scrip Description : Preserve CF on Queue
Change</FONT></DIV>
<DIV><FONT face=Arial size=2>Condition : On Queue Change</FONT></DIV>
<DIV><FONT face=Arial size=2>Custom Condition : None</FONT></DIV>
<DIV><FONT face=Arial size=2>Action : User Defined</FONT></DIV>
<DIV><FONT face=Arial size=2>Custom action preparation code:
{follows}</FONT></DIV>
<DIV><FONT face=Arial size=2>Custom action cleanup code: None</FONT></DIV>
<DIV><FONT face=Arial size=2>Stage: Transaction Create</FONT></DIV>
<DIV><FONT face=Arial size=2>Template: Global template: Blank</FONT></DIV>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial
size=2>----------------------------------------</FONT></DIV>
<DIV>Custom action preparation code</DIV>
<DIV> </DIV>
<DIV># Retrieve important values<BR>my $CurrentQueueID =
$self->TransactionObj->NewValue();<BR>my $OldQueueID =
$self->TransactionObj->OldValue;<BR>my $TicketID =
$self->TicketObj->id();</DIV>
<DIV> </DIV>
<DIV>my $CurrentQueue = new
RT::Queue($RT::SystemUser);<BR>$CurrentQueue->Load($CurrentQueueID);</DIV>
<DIV> </DIV>
<DIV>my $OldQueue = new
RT::Queue($RT::SystemUser);<BR>$OldQueue->Load($OldQueueID);</DIV>
<DIV> </DIV>
<DIV>my $CurrentQueueCFs = $CurrentQueue->CustomFields();<BR>my $OldQueueCFs
= $OldQueue->CustomFields();</DIV>
<DIV> </DIV>
<DIV>my @CurrentQueueCFNames;<BR>my @OldQueueCFNames;</DIV>
<DIV> </DIV>
<DIV>while (my $CurrentCF = $CurrentQueueCFs->Next()) {<BR> # Exclude
Global CFs<BR> unless ($CurrentCF->Queue == "0") {<BR> push
(@CurrentQueueCFNames, $CurrentCF->Name);<BR> }<BR>}</DIV>
<DIV> </DIV>
<DIV>while (my $OldCF = $OldQueueCFs->Next()) {<BR> # Exclude Global
CFs<BR> unless ($OldCF->Queue == "0") {<BR> push
(@OldQueueCFNames, $OldCF->Name);<BR> }<BR>}</DIV>
<DIV> </DIV>
<DIV># @CurrentQueueCFNames now has a list of all CF for the current queue<BR>#
@OldQueueCFNames now has a list of all CF for the previous queue</DIV>
<DIV> </DIV>
<DIV># Find CFs that are common between Old Queue and Current Queue</DIV>
<DIV> </DIV>
<DIV>my @PortableCFs;<BR>my @NonPortableCFs;<BR>my %found;<BR>my $CF_Test =
"";</DIV>
<DIV> </DIV>
<DIV># Find CFs that are in Old Queue and Current Queue<BR>foreach $CF_Test
(@OldQueueCFNames) { $found{$CF_Test} = 1 }</DIV>
<DIV> </DIV>
<DIV>foreach $CF_Test (@CurrentQueueCFNames) {<BR> if ($found{$CF_Test}) {
push (@PortableCFs, $CF_Test) }<BR>}</DIV>
<DIV> </DIV>
<DIV># Find CFs that are in Old Queue but NOT in Current Queue</DIV>
<DIV> </DIV>
<DIV>%found = ();<BR>foreach $CF_Test (@CurrentQueueCFNames) { $found{$CF_Test}
= 1 }</DIV>
<DIV> </DIV>
<DIV>foreach $CF_Test (@OldQueueCFNames) {<BR> unless ($found{$CF_Test})
{push (@NonPortableCFs, $CF_Test) }<BR>}</DIV>
<DIV> </DIV>
<DIV># @PortableCFs now has a list of all CF that exist in both queues<BR>#
@NonPortableCFs now has a list of all CF that exist only in old queue</DIV>
<DIV> </DIV>
<DIV># Setup database access (MySQL only at this point)</DIV>
<DIV> </DIV>
<DIV>use DBI;</DIV>
<DIV> </DIV>
<DIV>my $RT_DB = "DBI:mysql:$RT::DatabaseName";<BR>my $dbh =
DBI->connect($RT_DB,$RT::DatabaseUser,$RT::DatabasePassword);</DIV>
<DIV> </DIV>
<DIV># For every PortableCF, retrieve the values from the prior queue and<BR>#
add them to the ticket in the new queue.</DIV>
<DIV> </DIV>
<DIV>foreach my $CF_To_Copy (@PortableCFs) {<BR> my
$sql="<BR> SELECT Content<BR> FROM
TicketCustomFieldValues, CustomFields<BR> WHERE CustomFields.Name =
'$CF_To_Copy'<BR> AND CustomFields.Disabled = 0<BR> AND
CustomFields.Queue = $OldQueueID<BR> AND
TicketCustomFieldValues.CustomField = CustomFields.id<BR> AND
TicketCustomFieldValues.Ticket = $TicketID<BR> ";</DIV>
<DIV> </DIV>
<DIV> my $output =
$dbh->prepare($sql);<BR> $output->execute();<BR> <BR> while
(my $CF_Value = $output->fetchrow_array()) {<BR> my $CF_to_Update
= new
RT::CustomField($RT::SystemUser);<BR> $CF_to_Update->LoadByNameAndQueue<BR> (Queue
=> $CurrentQueueID, Name =>
$CF_To_Copy);<BR> <BR> $self->TicketObj->AddCustomFieldValue<BR> (Field
=> $CF_to_Update, Value => $CF_Value);<BR> }</DIV>
<DIV> </DIV>
<DIV> $output->finish();<BR>}</DIV>
<DIV> </DIV>
<DIV># For every NonPortableCF, retrieve the values from the prior queue
and<BR># put them in as a Comment on the ticket.</DIV>
<DIV> </DIV>
<DIV>my $Comment;</DIV>
<DIV> </DIV>
<DIV>if (@NonPortableCFs) {<BR> $Comment =<BR> "The following custom
fields were in use before a queue change.\n";<BR> $Comment
.=<BR> "These fields don't exist in the current queue.\n";<BR>}</DIV>
<DIV> </DIV>
<DIV>foreach my $CF_To_Comment (@NonPortableCFs) {<BR> $Comment .=
"\n\n$CF_To_Comment : ";<BR> my $sql="<BR> SELECT
Content<BR> FROM TicketCustomFieldValues,
CustomFields<BR> WHERE CustomFields.Name =
'$CF_To_Comment'<BR> AND CustomFields.Disabled = 0<BR> AND
CustomFields.Queue = $OldQueueID<BR> AND
TicketCustomFieldValues.CustomField = CustomFields.id<BR> AND
TicketCustomFieldValues.Ticket = $TicketID<BR> ";</DIV>
<DIV> </DIV>
<DIV> my $output =
$dbh->prepare($sql);<BR> $output->execute();</DIV>
<DIV> </DIV>
<DIV> while (my $CF_Value = $output->fetchrow_array())
{<BR> $Comment .= "( $CF_Value ) ";<BR> }<BR>}</DIV>
<DIV> </DIV>
<DIV>if ($Comment) {<BR> $self->TicketObj->Comment(Content =>
$Comment);<BR>} </DIV>
<DIV> </DIV>
<DIV>$dbh->disconnect();</DIV>
<DIV> </DIV>
<DIV>return 1;</DIV></FONT></DIV></BODY></HTML>