Scrip : Preserve CF on Queue Change

RT Users
1

Howdy,

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.

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.

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.

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.

Be nice to my less than optimal Perl code - I write about once every 6 months. Thanks for your time.

Scrip Description : Preserve CF on Queue Change
Condition : On Queue Change
Custom Condition : None
Action : User Defined
Custom action preparation code: {follows}
Custom action cleanup code: None
Stage: Transaction Create
Template: Global template: Blank
Custom action preparation code

Retrieve important values

my $CurrentQueueID = $self->TransactionObj->NewValue();
my $OldQueueID = $self->TransactionObj->OldValue;
my $TicketID = $self->TicketObj->id();

my $CurrentQueue = new RT::Queue($RT::SystemUser);
$CurrentQueue->Load($CurrentQueueID);

my $OldQueue = new RT::Queue($RT::SystemUser);
$OldQueue->Load($OldQueueID);

my $CurrentQueueCFs = $CurrentQueue->CustomFields();
my $OldQueueCFs = $OldQueue->CustomFields();

my @CurrentQueueCFNames;
my @OldQueueCFNames;

while (my $CurrentCF = $CurrentQueueCFs->Next()) {

Exclude Global CFs

unless ($CurrentCF->Queue == “0”) {
push (@CurrentQueueCFNames, $CurrentCF->Name);
}
}

while (my $OldCF = $OldQueueCFs->Next()) {

Exclude Global CFs

unless ($OldCF->Queue == “0”) {
push (@OldQueueCFNames, $OldCF->Name);
}
}

@CurrentQueueCFNames now has a list of all CF for the current queue

@OldQueueCFNames now has a list of all CF for the previous queue

Find CFs that are common between Old Queue and Current Queue

my @PortableCFs;
my @NonPortableCFs;
my %found;
my $CF_Test = “”;

Find CFs that are in Old Queue and Current Queue

foreach $CF_Test (@OldQueueCFNames) { $found{$CF_Test} = 1 }

foreach $CF_Test (@CurrentQueueCFNames) {
if ($found{$CF_Test}) { push (@PortableCFs, $CF_Test) }
}

Find CFs that are in Old Queue but NOT in Current Queue

%found = ();
foreach $CF_Test (@CurrentQueueCFNames) { $found{$CF_Test} = 1 }

foreach $CF_Test (@OldQueueCFNames) {
unless ($found{$CF_Test}) {push (@NonPortableCFs, $CF_Test) }
}

@PortableCFs now has a list of all CF that exist in both queues

@NonPortableCFs now has a list of all CF that exist only in old queue

Setup database access (MySQL only at this point)

use DBI;

my $RT_DB = “DBI:mysql:$RT::DatabaseName”;
my $dbh = DBI->connect($RT_DB,$RT::DatabaseUser,$RT::DatabasePassword);

For every PortableCF, retrieve the values from the prior queue and

add them to the ticket in the new queue.

foreach my $CF_To_Copy (@PortableCFs) {
my $sql="
SELECT Content
FROM TicketCustomFieldValues, CustomFields
WHERE CustomFields.Name = ‘$CF_To_Copy’
AND CustomFields.Disabled = 0
AND CustomFields.Queue = $OldQueueID
AND TicketCustomFieldValues.CustomField = CustomFields.id
AND TicketCustomFieldValues.Ticket = $TicketID
";

my $output = $dbh->prepare($sql);
$output->execute();

while (my $CF_Value = $output->fetchrow_array()) {
my $CF_to_Update = new RT::CustomField($RT::SystemUser);
$CF_to_Update->LoadByNameAndQueue
(Queue => $CurrentQueueID, Name => $CF_To_Copy);

$self->TicketObj->AddCustomFieldValue
(Field => $CF_to_Update, Value => $CF_Value);
}

$output->finish();
}

For every NonPortableCF, retrieve the values from the prior queue and

put them in as a Comment on the ticket.

my $Comment;

if (@NonPortableCFs) {
$Comment =
“The following custom fields were in use before a queue change.\n”;
$Comment .=
“These fields don’t exist in the current queue.\n”;
}

foreach my $CF_To_Comment (@NonPortableCFs) {
$Comment .= "\n\n$CF_To_Comment : “;
my $sql=”
SELECT Content
FROM TicketCustomFieldValues, CustomFields
WHERE CustomFields.Name = ‘$CF_To_Comment’
AND CustomFields.Disabled = 0
AND CustomFields.Queue = $OldQueueID
AND TicketCustomFieldValues.CustomField = CustomFields.id
AND TicketCustomFieldValues.Ticket = $TicketID
";

my $output = $dbh->prepare($sql);
$output->execute();

while (my $CF_Value = $output->fetchrow_array()) {
$Comment .= "( $CF_Value ) ";
}
}

if ($Comment) {
$self->TicketObj->Comment(Content => $Comment);
}

$dbh->disconnect();

return 1;