External Data Get drop down list options

Dont create your support topics here! No new topics with questions allowed!

Moderator: crythias

User avatar
crythias
Moderator
Posts: 9920
Joined: 04 May 2010, 18:38
OTRS Version?: 4.0.x
Location: SouthWest Florida, USA
Contact:

External Data Get drop down list options

Postby crythias » 01 Sep 2012, 14:20

***PLEASE NOTE*** It's not recommended to handle this via javascript/ajax in the browser. It's much more sane and safer to handle this server-side as much as possible. This is a workaround, but a better answer should be to use database connections and queries directly to DynamicFields (also, it will better handle the other fields' ajax refresh of Dynamic Fields). I'm not expecting that to occur in stock OTRS at any time in the future, though.

"What does this do?"
it's a very VERY rudimentary get data from external data source cgi. You can use it with jQuery (you will probably need to load jquery in HTMLHead.dtl) for data access to that thing you want - dynamicfields external data.

"How do I use it?"
create a file in /otrs/bin/cgi-bin, maybe call it "extdata.pl" ... give it execute permissions chmod +x 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=123;host=localhost;',
   #  DatabaseUser => 'user',
   #  DatabasePw   => 'somepass',
   #  Type         => 'mysql',
   );

   my $query = param('q') || '';
   my $like = '%' . $query . '%';
   my $ResultAsArrayRef = $CommonObject{DBObject}->Prepare(
      SQL => "SELECT id, login FROM users WHERE login like ?",
      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; 


test how it works from your otrs: http://otrsserver/otrs/extdata.pl?q=ro
Of course, you don't have to send a query parameter for filter, you could just make a fixed SELECT entry.

"What do I do with that?"
First, this is proof of concept, and it's my first go at it. It uses OTRS API to handle the calls, but generally all you will need is to provide the credentials and adjust the select to what you're looking for.

Then, you'll need something to call it.

A sample select is like this:

Code: Select all

<select id="myselect" name="DynamicField_fieldName">
</select>

and jquery code at the bottom (?)

Code: Select all

    <script type="text/javascript">
        $.get('extdata.pl', function(data) {
           $('#myselect').append(data);
    });
    </script>


If you need dependent fields, you can use ACL within OTRS or if everything is querying everything else you could bind the master field onchange to query and update the dependent field.

Hope this helps.

Bug fix requests here. Questions on implementation in the standard forums, please?

Also, it *might* be likely that a DynamicField_XXX configured as a dropdown/Select might be problematic if you provide values that aren't in the list.
An option to workaround this is to use a Dynamic Field that's a text input and change an input to a select or to use autocomplete/fuzzy search.
OTRS 4.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

StefanoBoccanera
OTRS expert
Posts: 76
Joined: 23 Feb 2011, 15:43
OTRS Version?: 3.1.5
Real Name: Stefano Boccanera
Company: I.Conseils (self-employee)
Location: Rome (Italy)

Re: External Data Get drop down list options

Postby StefanoBoccanera » 02 Nov 2012, 11:55

Hi crythias

I'd like to use your suggestion, but I have to modify the perl module to get data from one or more text file.
The right text file name depends on a parameter passed by $.get invocation (i.e the value of another dropdown field or a variable value set to a substring of dropdown field value), so how we have to modify the jquery statement to pass the parameter?
In perl module woul d be simple .....

best regards

stefano
Stefano Boccanera

OTRS : 3.1.5
ITSM : 3.1
S.O : RH
RDBMS : Postgres

tnieman
OTRS wizard
Posts: 103
Joined: 14 Nov 2012, 21:59
OTRS Version?: 3.1.6
Real Name: Tom Nieman
Company: WorkForce Software

Re: External Data Get drop down list options

Postby tnieman » 27 Dec 2012, 03:48

crythias,

Looks great, but where do I put the bottom two code boxes?

Tom

User avatar
crythias
Moderator
Posts: 9920
Joined: 04 May 2010, 18:38
OTRS Version?: 4.0.x
Location: SouthWest Florida, USA
Contact:

Re: External Data Get drop down list options

Postby crythias » 30 Dec 2012, 18:53

The select goes exactly where you want to see it, in that dtl.
The jquery is also in that file, at the end.
OTRS 4.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

tnieman
OTRS wizard
Posts: 103
Joined: 14 Nov 2012, 21:59
OTRS Version?: 3.1.6
Real Name: Tom Nieman
Company: WorkForce Software

Re: External Data Get drop down list options

Postby tnieman » 12 Jan 2013, 01:13

Almost there. I am calling the script GetCompanyData.pl, it runs fine.

I added the select into AgentTicketCustomer.dtl

I get a drop down, but it is empty. In IE we get an error that says "$ is undefined".

We researched that error and found info that stated it was most likely that jquery was not installed. We installed that and rebuilt the config. The error is now gone, but there still aren't any values in the drop down.

Any ideas?

Tom

User avatar
crythias
Moderator
Posts: 9920
Joined: 04 May 2010, 18:38
OTRS Version?: 4.0.x
Location: SouthWest Florida, USA
Contact:

Re: External Data Get drop down list options

Postby crythias » 13 Jan 2013, 19:54

tnieman wrote:I am adding this dropdown to the AgentTicketCustomer page. I can see the dropdown, but it doesn't have any values in it.

yep. Won't have any information by default.
tnieman wrote:The javascript is in the dtl file. The pl file does print data when used in a browser (formatted as <option .....).

tnieman wrote:Any ideas how to debug this?


yes. what's the ID of the select box? make sure it matches in the javascript. also look at your javascript console in a better browser (Chrome/Firefox)
OTRS 4.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

tnieman
OTRS wizard
Posts: 103
Joined: 14 Nov 2012, 21:59
OTRS Version?: 3.1.6
Real Name: Tom Nieman
Company: WorkForce Software

Re: External Data Get drop down list options

Postby tnieman » 16 Jan 2013, 01:26

I finally have the list populating, but the selection is not being saved to the dynamic field.

Code: Select all

<label for="CompanyData">$Text{"Ticket on behalf of Company"}:</label>
<div class="Field">
    <select id="companydata" name="DynamicField_FinalCompany">
    </select>
</div>
                             .
                             .
                             .
    <script type="text/javascript">
        $.get("GetCompanyData.pl", function(data) {
           $("#companydata").append(data);
    });
    </script>


User avatar
crythias
Moderator
Posts: 9920
Joined: 04 May 2010, 18:38
OTRS Version?: 4.0.x
Location: SouthWest Florida, USA
Contact:

Re: External Data Get drop down list options

Postby crythias » 16 Jan 2013, 20:59

You probably don't want to manually create a dynamic field entry, but rather enable it in SysConfig.
OTRS 4.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

tnieman
OTRS wizard
Posts: 103
Joined: 14 Nov 2012, 21:59
OTRS Version?: 3.1.6
Real Name: Tom Nieman
Company: WorkForce Software

Re: External Data Get drop down list options

Postby tnieman » 17 Jan 2013, 14:19

I did create FinalCompany in SysConfig -> Dynamic Fields.

I sorry, but I very new to OTRS, Perl, and JavaScript.

Tom

User avatar
crythias
Moderator
Posts: 9920
Joined: 04 May 2010, 18:38
OTRS Version?: 4.0.x
Location: SouthWest Florida, USA
Contact:

Re: External Data Get drop down list options

Postby crythias » 17 Jan 2013, 15:12

Please create your request as a new topic in the general forums. I'll still help you, but the other forums aren't moderated. In the new post, tell me what screen you're using and that you've enabled that field within SysConfig for that screen, and any other relevant information.
OTRS 4.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

damirun
OTRS newbie
Posts: 9
Joined: 24 Oct 2014, 12:56
OTRS Version?: 3.3.3
Location: Italy

Re: External Data Get drop down list options

Postby damirun » 24 Oct 2014, 18:33

I'm using OTRS 3.3.3. and I have tried to do all what crythias explained about to get external data into drop down list but mu dropdown list is empty.

I created the extdata.pl put it into /otrs/bin/cgi-bin.

Put the select code and the javascritp into AgentTicketZoom.dtl.

The dropdownlist is visibile but empty.

Can someone help me please?

Thanks in advance.


Return to “Howtos”

Who is online

Users browsing this forum: No registered users and 1 guest

cron