Dynamic field question

[montoxic]

Hi,

I wonder if it possible to add a dynamic field in the ticket, for example in free fields. And where you can enter a value for example, Mobile. and that value is added to the database. Then in the same field or another field. YOu can search, for example if i type in mo, mobile should show up as an option.

You can say that it is a two things that i want to do.

* Add value to database in dynamic field
* be able to select/search value in dynamic field from any ticket.

Regards.

[eandrex]

If I understood correctly, then this *should* do the trickt

for your first need: Create a ticket dynamicfield of type "Text"

your second point: Install this addon(make sure it matchs your otrs version) http://opar.perl-services.de/dist/Dynam ... teDB-1.3.0 and configure it to connect to your OTRS DB instance.. and the query should select the values that are stored in the dynamic field created in the previous step

[montoxic]

Thanx, that is exacly what i am looking for, will test it tonight.

[montoxic]

Hi, i have almost get it to work. My only problem now is that i only want dynamic_fields with field_id=90 to be searched.


If i jus do a SQL QUERY, the query that i want is this:

SELECT value_text
FROM `dynamic_field_value`
WHERE field_id = '90'

My questions are what to put here.

DatabaseTable: dynamic_field_value
DatabaseFieldKey: id
DatabaseFieldValue: ?
DatabaseFieldSearch: ?

Regards

Montoxic

[montoxic]

Hi again,

I am not trying to use that OTRS addon no more, i get it to work, but i want to do some more things in the future and are trying to do this insteed:

I am trying to getting it to work using this guide viewtopic.php?f=60&t=17033.

But i don´t get the script to work

System info: Ubuntu 14.04, Apache, MySQL, OTRS 4.0.12


extdata.pl

Code: Select all

#!/usr/bin/perl -w
use strict;
use warnings;

#use CGI qw(:standard);
use JSON; #install JSON via cpan/ppm if you want to use it instead

print header (-type => 'application/json');

#code to query data

# use ../../ as lib location
use FindBin qw($Bin);
use lib "$Bin/../..";
use lib "$Bin/../../Kernel/cpan-lib";
use lib "$Bin/../../Custom";

use Kernel::Config;
use Kernel::System::Encode;
use Kernel::System::Log;
use Kernel::System::Main;
use Kernel::System::DB;


   my %CommonObject = ();
   $CommonObject{ConfigObject} = Kernel::Config->new();
   $CommonObject{EncodeObject} = Kernel::System::Encode->new(%CommonObject);
   $CommonObject{LogObject}    = Kernel::System::Log->new(
    LogPrefix => 'ExtData',
    %CommonObject,
   );
   $CommonObject{MainObject}   = Kernel::System::Main->new(%CommonObject);
   $CommonObject{DBObject}     = Kernel::System::DB->new(
      %CommonObject,
     DatabaseDSN  => 'DBI:odbc:database=otrs;host=localhost;',
     DatabaseUser => 'extdata',
     DatabasePw   => 'xxxxxxxx',
     Type         => 'mysql',
   );

   my $query = param('q') || '';
   my $like = '%' . $query . '%';
   my $ResultAsArrayRef = $CommonObject{DBObject}->Prepare(
      SQL => "SELECT name FROM customer_company",
      Bind => [ \$like ],
      Order => 'asc',
      Limit => 10
   );
#  my $json_text = to_json($ResultAsArrayRef);
#  print $json_text;
   while ( my @Row = $CommonObject{DBObject}->FetchrowArray()) {
   print '<option value="' . $Row[0] . '">'. $Row[1] . "</option>\n";
}
1;

When i try to run the script i get Internal Server Error. Apache logs says.

You may correct me if am am wrong, but isnt "Kernel::OM is not defined," about the template?

Apache Error log

Code: Select all

[Fri Sep 25 13:11:19 2015] -e: print() on unopened filehandle header at /opt/otrs/bin/cgi-bin/extdata.pl line 8.
[Fri Sep 25 13:11:19.020612 2015] [:error] [pid 27762] $Kernel::OM is not defined, please initialize your object manager at /opt/otrs/Kernel/System/Log.pm line 61.\n\tKernel::System::Log::new('Kernel::System::Log', 'LogPrefix', 'ExtData', 'ConfigObject', 'Kernel::Config=HASH(0x7ff04ac65028)', 'EncodeObject', 'Kernel::System::Encode=HASH(0x7ff04a9979c0)') called at /opt/otrs/bin/cgi-bin/extdata.pl line 28\n\tModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_extdata_2epl::handler('Apache2::RequestRec=SCALAR(0x7ff04ac46990)') called at /usr/lib/perl5/ModPerl/RegistryCooker.pm line 206\n\teval {...} called at /usr/lib/perl5/ModPerl/RegistryCooker.pm line 206\n\tModPerl::RegistryCooker::run('ModPerl::Registry=HASH(0x7ff04ac1b820)') called at /usr/lib/perl5/ModPerl/RegistryCooker.pm line 172\n\tModPerl::RegistryCooker::default_handler('ModPerl::Registry=HASH(0x7ff04ac1b820)') called at /usr/lib/perl5/ModPerl/Registry.pm line 31\n\tModPerl::Registry::handler('ModPerl::Registry', 'Apache2::RequestRec=SCALAR(0x7ff04ac46990)') called at -e line 0\n\teval {...} called at -e line 0\n

[eandrex]

You can get the same "functionality" by registering a public module. Something like this:

Kernel/Config/Files/MyModule.xml

Code: Select all

<otrs_config version="1.0" init="Framework">
<ConfigItem Name="PublicFrontend::Module###MyModule" Required="0" Valid="1">
        <Description Translatable="1">Frontend module registration for the public interface.</Description>
        <Group>Framework</Group>
        <SubGroup>Frontend::Public::ModuleRegistration</SubGroup>
        <Setting>
            <FrontendModuleReg>
                <Description>MyModule</Description>
                <Title>MyModule</Title>
                <NavBarName></NavBarName>
            </FrontendModuleReg>
        </Setting>
    </ConfigItem>
</otrs_config>

Kernel/Modules/MyModule.pm

Code: Select all

package Kernel::Modules::MyModule;

use strict;
use warnings;

our $ObjectManagerDisabled = 1;

sub new {
    my ( $Type, %Param ) = @_;

    # allocate new hash for object
    my $Self = {%Param};
    bless( $Self, $Type );

    return $Self;
}

sub Run {
    my ( $Self, %Param ) = @_;

    my $LayoutObject = $Kernel::OM->Get('Kernel::Output::HTML::Layout');
	
    my $DBObject = $Kernel::OM->('Kernel::System::DB');
	#Maybe use Kernel::System::CustomerCompany instead?
    $DBObject->Prepare(
		SQL => 'SELECT name from customer_company', 
	);
    my @Companies = ();
	while(my @Row = $DBObject->FetchrowArray()){
        push @Companies, $Row[0];
    }
    
    my $JSONString = $LayoutObject->JSONEncode(
        Data => \@Companies
    );
    
    return $LayoutObject->Attachment(
        Type => 'inline',
        ContentType => 'application/json',
        Charset => 'utf8',
        Content => $JSONString,
    );
}

1;

Now you can use your public interface to get it
http://otrs/public.pl?Action=MyModule

Note: All of this has not been tested, but it is the general idea

Edit:

Note2: This module will return a JSON object instead of a lot of "<option>..</option>", so in order to use it in your template
you can write something like

Code: Select all

$.getJSON('public.pl',null,function(data){
    $.each(data, function(index, value){
        $("#DynamicField_MyDynamicField").append($("<option>"+value+"</option>"));
    });
});

[montoxic]

Big Thanks for the tip, i will look into this and get back.

[eandrex]

i just realised that the $.getJSON wont work(assuming that the perl code is correct and the otrs configuration is right)

this one should work

Code: Select all

$.getJSON('public.pl',{ Action : 'MyModule' },function(data){
    $.each(data, function(index, value){
        $("#DynamicField_MyDynamicField").append($("<option>"+value+"</option>"));
    });
});

[montoxic]

Hi again,

thanks again.

I am trying to get it to work in AgentTicketCustomer.tt

And this part is really where i lack the knowledge of how to do it, where to put the code.

1. Have created MyModule.xml
2. Have created MyModule.pm
3. Have created a dynamicfield called DynamicField_sccompany
3. Has tried to paste the get json code into the AgentTicketCustomer.tt without any luck. I think it i just some syntax error

AgentTicketCustomer.tt

Code: Select all

# --
# AgentTicketCustomer.tt - provides HTML for AgentTicketCustomer.pm
# Copyright (C) 2001-2015 xxx, http://otrs.com/
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you

[% InsertTemplate("AgentCustomerSearch.tt") %]

[% RenderBlockStart("Customer") %]
<div class="LayoutPopup ARIARoleMain">
    <div class="Header">
        <h1>[% Translate("Change customer of ticket") | html %]: [% Data.TicketNumber | html %] - [% Data.Title | html %]</h1>
        <p class="AsteriskExplanation">[% Translate("All fields marked with an asterisk (*) are mandatory.") | html %]</p>
        <p>
            <a class="CancelClosePopup" href="#">[% Translate("Cancel & close window") | html %]</a>
        </p>
    </div>

    <div class="Content">
        <div class="LayoutFixedSidebar SidebarLast">
            <div class="SidebarColumn">
                <div id="CustomerInfo" class="WidgetSimple">
                    <div class="Header">
                        <h2>[% Translate("Customer Information") | html %]</h2>
                    </div>
                    <div class="Content">
                        [% Data.Table %]
                    </div>
                </div>
            </div>

            <div class="ContentColumn">
                <div class="Content">
                    <form action="[% Env("CGIHandle") %]" id="MainForm" method="post" name="compose" class="Validate">
                        <input type="hidden" name="Action" value="[% Env("Action") %]"/>
                        <input type="hidden" name="Subaction" value="Update"/>
                        <input type="hidden" name="TicketID" value="[% Data.TicketID | html %]"/>
                        <input type="hidden" name="SelectedCustomerUser" id="SelectedCustomerUser" value="[% Data.SelectedCustomerUser | html %]"/>
                        <fieldset class="TableLike FixedLabel">
                            <label for="CustomerAutoComplete" class="Mandatory"><span class="Marker">*</span> [% Translate("Customer user") | html %]:</label>
                            <div class="Field">
                                <input id="CustomerAutoComplete" type="text" name="CustomerUserID" value="[% Data.CustomerUserID | html %]" class="W75pc Validate_Re
quired [% Data.CustomerUserIDInvalid | html %]" autocomplete="off" />
                                <div id="CustomerAutoCompleteError" class="TooltipErrorMessage"><p>[% Translate("This field is required.") | html %]</p></div>
                                <div id="CustomerAutoCompleteServerError" class="TooltipErrorMessage"><p>[% Translate("This field is required.") | html %]</p></div>
                            </div>
                            <div class="Clear"></div>

                            <label for="CustomerID" class="Mandatory"><span class="Marker">*</span> [% Translate("CustomerID") | html %]:</label>
                            <div class="Field">
                                <input type="text" id="CustomerID" name="CustomerID" value="[% Data.CustomerID | html %]" class="W75pc Validate_Required [% Data.Cus
tomerIDInvalid | html %]"/>
                                <div id="CustomerIDError" class="TooltipErrorMessage"><p>[% Translate("This field is required.") | html %]</p></div>
                                <div id="CustomerIDServerError" class="TooltipErrorMessage"><p>[% Translate("This field is required.") | html %]</p></div>
                            </div>
                            <div class="Clear"></div>
                            <div class="Field">
<!-- put the button here and not into the footer, because the CustomerTickets table can be very long -->
                                <button class="CallForAction Primary" id="Submit" accesskey="g" title="[% Translate("Submit") | html %] (g)" type="submit" value="[%
 Translate("Submit") | html %]"><span>[% Translate("Submit") | html %]</span></button>
                            </div>
                        </fieldset>
                    </form>
                </div>
            </div>
            <div class="Clear"></div>
        </div>

        <div id="CustomerTickets"></div>

    </div>
    <div class="Footer"></div>
</div>
[% RenderBlockEnd("Customer") %]