I forgot how to do this

I need to get a list of all of the values for one particular custom field. I
had a script which did this after about a week or two of banging my head against
my desk. However, I no longer have this script and am left banging my head
again as I’ve forgotten how to gather this list up.

I’ve tried using CustomFieldValue, CustomFieldValues, CustomField and
CustomFields. I can’t for the life of me figure out how to do this. Can
someone point me in the right direction?

Keep up with me and what I’m up to: http://theillien.blogspot.com

I am sure you can do this by using a single mysql command, but…

To get the numeric id of the custom field:

mysql> select id from CustomFields where Name=‘’;

Grab the id, then:

mysql> select * from CustomFieldValues where CustomField=‘’;

James Moseley

         Mathew Snyder                                                 
         <theillien@yahoo.                                             
         com>                                                       To 
         Sent by:                  RT Users                            
         rt-users-bounces@         <rt-users@lists.bestpractical.com>  
         lists.bestpractic                                          cc 
         al.com                                                        
                                                               Subject 
                                   [rt-users] I forgot how to do this  
         08/08/2007 02:40                                              
         PM                                                            

I need to get a list of all of the values for one particular custom field.
I
had a script which did this after about a week or two of banging my head
against
my desk. However, I no longer have this script and am left banging my head
again as I’ve forgotten how to gather this list up.

I’ve tried using CustomFieldValue, CustomFieldValues, CustomField and
CustomFields. I can’t for the life of me figure out how to do this. Can
someone point me in the right direction?

Keep up with me and what I’m up to: http://theillien.blogspot.com
The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Why do you need a query to do this? Can’t you just look at Configuration->
CustomFields->YourCustomField and view its current value options?

If you need to do a SQL query, use:
SELECT DISTINCT CustomFieldValues.Name FROM CustomFieldValues JOIN
CustomFields ON CustomFields.id=CustomFieldValues.CustomField WHERE
CustomField.Name=‘YourCustomField’ AND CustomFieldValues.Disabled=0;

If you want ALL customfieldvalues for the field (and not just the current
ones) get rid of the Disabled specification.

ForrestOn 8/8/07, James Moseley jmoseley@corp.xanadoo.com wrote:

I am sure you can do this by using a single mysql command, but…

To get the numeric id of the custom field:

mysql> select id from CustomFields where Name=‘’;

Grab the id, then:

mysql> select * from CustomFieldValues where CustomField=‘’;

James Moseley

         Mathew Snyder
         <theillien@yahoo.
         com>                                                       To
         Sent by:                  RT Users
         rt-users-bounces@         <rt-users@lists.bestpractical.com>
         lists.bestpractic                                          cc
         al.com
                                                               Subject
                                   [rt-users] I forgot how to do this
         08/08/2007 02:40
         PM

I need to get a list of all of the values for one particular custom field.
I
had a script which did this after about a week or two of banging my head
against
my desk. However, I no longer have this script and am left banging my
head
again as I’ve forgotten how to gather this list up.

I’ve tried using CustomFieldValue, CustomFieldValues, CustomField and
CustomFields. I can’t for the life of me figure out how to do this. Can
someone point me in the right direction?


Keep up with me and what I’m up to: http://theillien.blogspot.com


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

I’m sure I don’t have to explain that sometimes people do things that
require this kind of information. It’s hard to look at the RT interface
and telepathically move the information in the custom field to the
application I’ll be using it in.

That said, as the API has the functionality to do this without direct
queries to the database, I have no interest in doing things outside of
said API. I’d rather not have to include more perl modules than
necessary (DBI) to get information that is already available via modules
I’m already using (everything in RT).

Thanks for the input though.

Mathew
Keep up with my goings on at http://theillien.blogspot.com

James Moseley wrote:

my $values = RT::CustomFieldValues->new( $RT::SystemUser );
$values->LimitToCustomField( $custom_field_id );
while ( my $value = $values->Next ) {
print $value->Name, “\n”;
}On 8/8/07, Mathew Snyder theillien@yahoo.com wrote:

I need to get a list of all of the values for one particular custom field. I
had a script which did this after about a week or two of banging my head against
my desk. However, I no longer have this script and am left banging my head
again as I’ve forgotten how to gather this list up.

I’ve tried using CustomFieldValue, CustomFieldValues, CustomField and
CustomFields. I can’t for the life of me figure out how to do this. Can
someone point me in the right direction?


Keep up with me and what I’m up to: http://theillien.blogspot.com


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

Mathew Snyder wrote:

I need to get a list of all of the values for one particular custom field. I
had a script which did this after about a week or two of banging my head against
my desk. However, I no longer have this script and am left banging my head
again as I’ve forgotten how to gather this list up.

I’ve tried using CustomFieldValue, CustomFieldValues, CustomField and
CustomFields. I can’t for the life of me figure out how to do this. Can
someone point me in the right direction?

This is what I’m working with:
#!/usr/bin/perl

use warnings;
use strict;
use lib ‘/usr/local/rt3/lib’;
use lib ‘/usr/local/rt3/local/lib’;
use RT;
use RT::Users;

RT::LoadConfig(); ## Loading RT config
RT::Init(); ## Initialise RT

my $cf_name = “Environment”;
my @envs;

my $cf = RT::CustomFieldValues->new(RT::SystemUser);
$cf->LimitToCustomField($cf_name);

while (my $cfval = $cf->Next() ){
print $cfval->Name . “\n”;
};

Using Data::Dumper I have the following output:
$VAR1 = bless( {
’_open_parens’ => {},
‘alias_count’ => 0,
‘where_clause’ => ‘’,
‘order_by’ => [
{
‘ORDER’ => ‘ASC’,
‘FIELD’ => ‘SortOrder’,
‘ALIAS’ => ‘main’
},
{
‘ORDER’ => ‘ASC’,
‘FIELD’ => ‘Name’,
‘ALIAS’ => ‘main’
},
{
‘ORDER’ => ‘ASC’,
‘FIELD’ => ‘id’,
‘ALIAS’ => ‘main’
}
],
‘table’ => ‘CustomFieldValues’,
‘tables’ => ‘’,
‘is_limited’ => 1,
‘order’ => ‘’,
‘user’ => bless( {
’_Class’ => ‘RT::CurrentUser’,
’_SB_Record_Primary_RecordCache_key’ => ‘id=1’,
‘table’ => ‘Users’,
‘values’ => {
‘creator’ => ‘1’,
‘comments’ => ‘Do not delete
or modify this user. It is integral to RT’s internal database structures’,
‘state’ => undef,
‘webencoding’ => undef,
‘realname’ => ‘The RT System
itself’,
‘password’ => ‘NO-PASSWORD’,
‘authsystem’ => undef,
‘homephone’ => undef,
‘id’ => ‘1’,
‘timezone’ => undef,
‘lang’ => undef,
‘name’ => ‘RT_System’,
‘contactinfosystem’ => undef,
‘zip’ => undef,
‘emailencoding’ => undef,
‘lastupdated’ => ‘2004-05-06
17:46:38’,
‘signature’ => undef,
‘externalauthid’ => undef,
‘address1’ => undef,
‘workphone’ => undef,
‘emailaddress’ => undef,
‘freeformcontactinfo’ => undef,
‘city’ => undef,
‘gecos’ => undef,
‘organization’ => undef,
‘country’ => undef,
‘mobilephone’ => undef,
‘nickname’ => undef,
‘externalcontactinfoid’ => undef,
‘address2’ => undef,
‘created’ => ‘2004-05-06
17:46:38’,
‘pgpkey’ => undef,
‘pagerphone’ => undef,
‘lastupdatedby’ => ‘0’
},
‘fetched’ => {
‘creator’ => 1,
‘comments’ => 1,
‘state’ => 1,
‘webencoding’ => 1,
‘realname’ => 1,
‘password’ => 1,
‘authsystem’ => 1,
‘homephone’ => 1,
‘id’ => 1,
‘timezone’ => 1,
‘lang’ => 1,
‘name’ => 1,
‘contactinfosystem’ => 1,
‘zip’ => 1,
‘emailencoding’ => 1,
‘signature’ => 1,
‘lastupdated’ => 1,
‘externalauthid’ => 1,
‘address1’ => 1,
‘workphone’ => 1,
‘emailaddress’ => 1,
‘freeformcontactinfo’ => 1,
‘city’ => 1,
‘gecos’ => 1,
‘organization’ => 1,
‘country’ => 1,
‘mobilephone’ => 1,
‘nickname’ => 1,
‘externalcontactinfoid’ => 1,
‘address2’ => 1,
‘created’ => 1,
‘pgpkey’ => 1,
‘lastupdatedby’ => 1,
‘pagerphone’ => 1
}
}, ‘RT::CurrentUser’ ),
‘limit_clause’ => ‘’,
‘DBIxHandle’ => bless( {
‘dsn’ => ‘dbi:mysql:dbname=rt3_devel’,
‘DisconnectHandleOnDestroy’ => undef,
‘StatementLog’ => []
}, ‘RT::Handle’ ),
‘restrictions’ => {
‘main.CustomField’ => [
{
‘value’ =>
’‘Environment’’,
‘op’ => ‘=’,
‘field’ =>
‘main.CustomField’
}
]
},
‘primary_key’ => ‘id’,
‘auxillary_tables’ => ‘’,
‘must_redo_search’ => 1,
‘itemscount’ => 0,
‘show_rows’ => 0,
‘aliases’ => [],
‘first_row’ => 0
}, ‘RT::CustomFieldValues’ );

I don’t quite get this though. Why won’t it print out the values held in the CF?

Mathew

Mathew Snyder wrote:

I need to get a list of all of the values for one particular custom field. I
had a script which did this after about a week or two of banging my head against
my desk. However, I no longer have this script and am left banging my head
again as I’ve forgotten how to gather this list up.

I’ve tried using CustomFieldValue, CustomFieldValues, CustomField and
CustomFields. I can’t for the life of me figure out how to do this. Can
someone point me in the right direction?

Can anyone help with this? I’ve exhausted all of the ideas I’ve come up with to
sort it out.

Mathew

Matthew, what are you trying to accomplish? Are you trying to come up with
a RT scrip to get these values from within RT, or do you simply need an
outside script to provide these details?

James Moseley

         Mathew Snyder                                                 
         <theillien@yahoo.                                             
         com>                                                       To 
         Sent by:                  RT Users                            
         rt-users-bounces@         <rt-users@lists.bestpractical.com>  
         lists.bestpractic                                          cc 
         al.com                                                        
                                                               Subject 
                                   Re: [rt-users] I forgot how to do   
         08/09/2007 12:16          this                                
         PM                                                            

Mathew Snyder wrote:

I need to get a list of all of the values for one particular custom
field. I
had a script which did this after about a week or two of banging my head
against
my desk. However, I no longer have this script and am left banging my
head
again as I’ve forgotten how to gather this list up.

I’ve tried using CustomFieldValue, CustomFieldValues, CustomField and
CustomFields. I can’t for the life of me figure out how to do this. Can
someone point me in the right direction?

Can anyone help with this? I’ve exhausted all of the ideas I’ve come up
with to
sort it out.

Mathew
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

I need an outside script to provide the details so I can feed them into another
application.

Keep up with me and what I’m up to: http://theillien.blogspot.com

James Moseley wrote:

Matthew;

If you want (needed urgent) you can get your data using dbi and query
the tables directly, the queries you need:
select o.content from ObjectCustomFieldValues o left join CustomFields c
on o.CustomField = c.id where c.Name = ‘Environment’;

I don’t think there is a way within the RT methods to grab all
values for a particular CF, from what I’ve seen it’ll expect a ticket id
(ie pull the value for one ticket) …but I might be wrong … if you are
desperate use dbi as I mentioned above.

Regards;
Roy

Mathew Snyder wrote:

The easiest thing to do would be to write a simple perl script using a DB
perl module, such as DBI, to query to the database using the syntax
provided by Forrest yesterday:

SELECT DISTINCT CustomFieldValues.Name FROM CustomFieldValues JOIN
CustomFields ON CustomFields.id=CustomFieldValues.CustomField WHERE
CustomField.Name=‘YourCustomField’

I beleive you stated yesterday you wanted to avoid installing a perl module
to do this, only wanted to use the API, etc. However, getting this info
via direct SQL query seems to be the most straightforward approach to me.

Unfortunatley, I am not familiar with the API, so I can’t help you there.

James Moseley

         Mathew Snyder                                                 
         <theillien@yahoo.                                             
         com>                                                       To 
                                   James Moseley                       
         08/09/2007 12:27          <jmoseley@corp.xanadoo.com>         
         PM                                                         cc 
                                   RT Users                            
                                   <rt-users@lists.bestpractical.com>  
                                                               Subject 
                                   Re: [rt-users] I forgot how to do   
                                   this                                

I need an outside script to provide the details so I can feed them into
another
application.

Keep up with me and what I’m up to: http://theillien.blogspot.com

James Moseley wrote:

Matthew, what are you trying to accomplish? Are you trying to come up
with
a RT scrip to get these values from within RT, or do you simply need an
outside script to provide these details?

James Moseley

         Mathew Snyder
         <theillien@yahoo.
         com>

To

         Sent by:                  RT Users
         rt-users-bounces@         <rt-users@lists.bestpractical.com>
         lists.bestpractic

cc

         al.com

Subject

                                   Re: [rt-users] I forgot how to do
         08/09/2007 12:16          this
         PM

Mathew Snyder wrote:

I need to get a list of all of the values for one particular custom
field. I
had a script which did this after about a week or two of banging my head
against
my desk. However, I no longer have this script and am left banging my
head
again as I’ve forgotten how to gather this list up.

I’ve tried using CustomFieldValue, CustomFieldValues, CustomField and
CustomFields. I can’t for the life of me figure out how to do this.
Can

That the thing. I’ve done it before. I just don’t have the code I used so I
don’t have anything to look at. Although, I may have simply pulled down all
of the custom field values and told the script to skip those from custom fields
I wasn’t looking for.

Keep up with me and what I’m up to: http://theillien.blogspot.com

Roy El-Hames wrote:

DBI is already installed. RT requires it. I just want to avoid having to use
it instead of keeping everything within the RT API.

Keep up with me and what I’m up to: http://theillien.blogspot.com

James Moseley wrote:

Ok hold on I just looked at your code, what are you looking for the
different values you have set for one custom field ;
ie 1- you have your Enviroment cf as a select one/multi value cf with
different options and you are looking for the different options ??
or 2- are you looking for the values of the Enviromnet cf that have
been set by the users on the different tickets ??

RT::CustomFieldValues used for 1
RT::ObjectCustomFieldValues used for 2 (but sorry still can’t find the
function that may help you)

Roy

Mathew Snyder wrote:

Number 1 is the correct choice. I am trying to get a list of all of the values
within the one custom field. I’m not concerned about what is on the tickets. I
just need the values that populate the Select One custom field.

Keep up with me and what I’m up to: http://theillien.blogspot.com

Roy El-Hames wrote:

I figured it out. I knew that something had to be doing what I was asking
because if I need to alter a custom field, all of the values are populated into
the Modify.html file. Looking at that I discovered that I needed to use the
ValuesObj method of CustomField. That said, I have this:

#!/usr/bin/perl

use warnings;
use strict;
use lib ‘/usr/local/rt3/lib’;
use lib ‘/usr/local/rt3/local/lib’;
use RT;
use RT::Users;
use RT::CustomFieldValues;

RT::LoadConfig(); ## Loading RT config
RT::Init(); ## Initialise RT

my $cf_id = 1;

my $cf = new RT::CustomField(RT::SystemUser);
$cf->Load($cf_id);

my $cfVals = $cf->ValuesObj;

while (my $cfVal = $cfVals->Next){
print $cfVal->Name . “\n”;
}

This gives me the output I was expecting from the get go.

I appreciate all of the input and thoughts. They actually pushed me in the
direction I needed to go even if they weren’t the answer on a silver platter :slight_smile:

Keep up with me and what I’m up to: http://theillien.blogspot.com

Roy El-Hames wrote: