[rt-users] Modify CF dropdown list from external DB based off of another CFs value

[scott.dalzell]

I have 2 ticket CFs (Version_No and Product)

i currently have a perl script to update the Version_No CF drop down list
from an external database with a manually set Where value set in the code.
(This all works fine for controlling the drop down list values to show only
the version numbers for the manually set product)

i would now like to modify the code to allow the drop down list to be
updated with values which contain the same catagory as the value set in the
Product CF, similar to using the Categories are based on Field when you
create your own list

To do this i have added the following line

my $dbwherevalue = $self->TicketObj->FirstCustomFieldValue('Product');

but i also get the following error

[error]: Can't locate object method "TicketObj" via package
"RT::CustomFieldValues::Version_No" at
/opt/rt4/sbin/../lib/RT/CustomFieldValues/Version_No.pm line 60

would someone be able to advice me what to do to resolve this error

below is my code

-------------------CODE START-----------------------
##################################################
##Prep
##################################################

package RT::CustomFieldValues::Version_No;

use strict;
use warnings;
use DBI;
use DBD::Mysql;
use base qw(RT::CustomFieldValues::External);

##################################################
##List Description
##################################################

sub SourceDescription {
    return 'Version Numbers';
}

##################################################
##VARIABLES
##################################################

sub ExternalValues {
        my $self = shift;

# External Database details
	
	my $dbhost = "[HostName]";
	my $dbtype = "[DatabaseType]";
	my $dbname = "[DatabaseName]"; 
	my $dbtable="customfieldvalue";
	my $dbcolumn="Name";

	my $dbwherecolumn="Category";
	#my $dbwherevalue="'Testing1234'";
             my $dbwherevalue =
$self->TicketObj->FirstCustomFieldValue('Product');

	my $dbuser = "[UserName]"; 
	my $dbpass = "[Password]";
	
	my $i = 0;
        my @res;
        my $Hostname;

##################################################
##Connect to Database
##################################################
#Database on remote server
	my $dbh = DBI->connect("DBI:$dbtype:$dbname;host=$dbhost", "$dbuser",
"$dbpass",
	            { RaiseError => 1 }
	           );

##################################################
##Run Search of DB for values you wish in CF List
##################################################

	my $req = "SELECT distinct $dbcolumn FROM $dbtable where
$dbwherecolumn=$dbwherevalue";
	my $hreq = $dbh->prepare($req);
        $hreq->execute();;
        $hreq->bind_columns(\$Hostname);
        while ($hreq->fetch()){
                push @res, {
                    name        => $Hostname,
                    description => $Hostname,
                    sortorder   => $i++,
                };
        }
        return \@res;
}

RT::Base->_ImportOverlays();

1;

-------------------CODE END-------------------------

thank you in advanced

Scott



--
View this message in context: http://requesttracker.8502.n7.nabble.com/Modify-CF-dropdown-list-from-external-DB-based-off-of-another-CFs-value-tp55012.html
Sent from the Request Tracker - User mailing list archive at Nabble.com.