Using external data source to fill dynamic field drop down

[hvosdrecomm]

We are in the process of testing OTRS. We would like to have two drop down boxes in a ticket where we can select an item which will be used in some way later.

These drop down boxes must be populated by data from an external database. I read the following in the feature list of OTRS 3.1.

New custom field types (e.g. custom field type dropdown with an external data source) can be added with small effort as the fields are created in a modular, pluggable way.

This sounds exactly what we need but I cannot find any instruction for this feature in the actual manual where configuring dynamic fields is explained. Is there documentation available for this, if so, where can I find it?

Thank you for your assistance.

[crythias]

You'll need to query that yourself. Any way that you could do it in a standard html page you can implement on the respective .dtl

[hvosdrecomm]

Hi crythias,

For my understanding. To implement this we need to customize the whole feature or does it still involve the dynamic fields functionality in OTRS?

And with regard to the implementation. Basically we need to add a new attribute to the Ticket object in OTRS. And in the frontend that attribute can be set using a drop down box?

And populating the drop down box must be done by adding a custom method for the Ticket object where the data is retrieved from an external database using an MySQL query and passed to a template (.dtl) file?

Would this be the correct way to go about it?

Thank you for your time.

[hvosdrecomm]

I found this topic with a solutions for a similar problem. I tried to implement this but I get the following message in the error.log file of Apache:

Code: Select all

[Thu Aug  9 12:05:16 2012] DotProject.pm: Subroutine new redefined at /opt/otrs//Kernel/System/DotProject.pm line 8.
[Thu Aug  9 12:05:16 2012] DotProject.pm: Subroutine ProjectList redefined at /opt/otrs//Kernel/System/DotProject.pm line 27.
[Thu Aug  9 12:05:16 2012] AgentTicketCompose.pm: Subroutine new redefined at /opt/otrs//Kernel/Modules/AgentTicketCompose.pm line 33.
[Thu Aug  9 12:05:16 2012] AgentTicketCompose.pm: Subroutine Run redefined at /opt/otrs//Kernel/Modules/AgentTicketCompose.pm line 81.
[Thu Aug  9 12:05:16 2012] AgentTicketCompose.pm: Subroutine _GetNextStates redefined at /opt/otrs//Kernel/Modules/AgentTicketCompose.pm line 1399.
[Thu Aug  9 12:05:16 2012] AgentTicketCompose.pm: Subroutine _Mask redefined at /opt/otrs//Kernel/Modules/AgentTicketCompose.pm line 1412.
[Thu Aug  9 12:05:16 2012] AgentTicketCompose.pm: Subroutine _GetFieldsToUpdate redefined at /opt/otrs//Kernel/Modules/AgentTicketCompose.pm line 1797.
[Thu Aug 09 12:05:16 2012] [error] [Thu Aug  9 12:05:16 2012] -e: Got no MainObject! at /opt/otrs//Kernel/System/DB.pm line 98.\n

I made the following changes to OTRS to get a list of all projects from a dotProject database.

• Created a new kernel package for dotProject functionality:
  
  Kernel/System/DotProject.pm
  

  Code: Select all

  package Kernel::System::DotProject;
  
  use strict;
  use warnings;
  
  use Kernel::System::DB;
  
  sub new {
      my ( $Class,%Param ) = @_;
      my $Self = bless {}, $Class;
  
      for my $Object ( qw(LogObject ConfigObject EncodeObject) ) {
          $Self->{$Object} = $Param{$Object} or die "Need $Object!";
      }
  
      $Self->{DBObject} = Kernel::System::DB->new(
          %{$Self},
          DatabaseDSN => 'DBI:mysql:<name>=;<host>',
          DatabaseUser => '<user>',
          DatabasePw => '<pass>',
      );
  
      return $Self;
  }
  
  # ProjectList returns a hash with all projects in dotProject
  sub ProjectList {
      my ($Self) = @_;
  
      my $SQL = 'SELECT project_id, project_name FROM projects WHERE project_company = 1';
      return if !$Self->{DBObject}->Prepare(
          SQL => $SQL,
      );
  
      my %List;
      while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
          $List{ $Row[0] } = "$Row[1]";
      }
  
      return %List;
  }
  
  1;

• Modified the controller for the AgentTicketCompose view.
  
  Kernel/Modules/AgentTicketCompose.pm
  
  Inserted new line on line 28 to include the new kernel package.
  

  Code: Select all

  use Kernel::System::DotProject;

  Inserted new line on line 58 to create new instance of the DotProject object (not entirely sure not familiar with Perl).
  

  Code: Select all

  $Self->{DotProjectObject} = Kernel::System::DotProject->new(%Param);

  Inserted code to generate contents for the drop down list on line 1785. I did this within the "sub _Mask" part.
  

  Code: Select all

  my %DotProjectProjectList = $Self->{DotProjectObject}->ProjectList();
  $Param{DotProjectProjectSelect} = $Self->{LayoutObject}->BuildSelection(
      Name => 'DotProjectID',
      Data => \%DotProjectProjectList,
      PossibleNone => 1,
  );

• Modified the view to get drop down list
  
  Kernel/Output/HTML/Standard/AgentTicketCompose.dtl
  
  Inserted code to generate drop down list on line 364.
  

  Code: Select all

  <label for="DotProjectProject">Project:</label>
  <div class="Field">
      $Data{DotProjectProjectSelect}
  </div>
  <div class="Clear"></div>

Can somebody help me trace the problem and perhaps assist in finding a solution?

[crythias]

customize the whole feature

does it still involve the dynamic fields functionality in OTRS?

It depends. Part of your question is how to provide dynamic data to a field. That is standard (?) jQuery/javascript if you want to do it client side/ajax like.
The other part of your question is where to store the data (what field). It would involved dynamic fields if you want to store the data in a dynamic field.

we need to add a new attribute to the Ticket object in OTRS

ok. Dynamic Field may be useful.

in the frontend that attribute can be set using a drop down box?

Yes. There's no real magic in how to assign a value to a form's input, whether it's text or select, the result is the field with name="name" gets a value upon form submit.

populating the drop down box must be done by adding a custom method for the Ticket object where the data is retrieved from an external database using an MySQL query and passed to a template (.dtl) file?

It doesn't have to be a custom method. It could simply (?) be an ajax jquery but yes, placed inside a .dtl file.

Would this be the correct way to go about it?

It's *a* way to go about it.

[hvosdrecomm]

This would be first step in our customization process. I just had a discussion with my colleague about the dynamic fields. In the end we need to have 2 drop down lists where contents of the second drop down list would depend on the first one. So you first would select a project and then the second drop down list would be populated with a list of tasks for the selected project.

Would this still be possible with dynamic fields functionality? My guess is that it won't and that we need to modify the table for the Ticket object and add an additional column for the project ID and task ID and also add the attributes to the Ticket kernel package in OTRS with the appropriate getters and setters (again not sure how this would work in Perl and OTRS).

When selecting a project a AJAX request must be executed to set the project ID attribute (like is done with modifying the ticket status I believe). Then the value of that attribute must be used to generate the drop down list for the project tasks. And finally we want to have a dynamic field with an URL (we have this configured) but that URL must contain the project ID and task ID values that were set for a ticket. This is where both the drop down lists come into place.

So to sum it up we need to have 2 drop down boxes where the selected values will be used to build the query string of an dynamic field URL.

[crythias]

we need to have 2 drop down lists where contents of the second drop down list would depend on the first one.

ok. So do other people.

So you first would select a project and then the second drop down list would be populated with a list of tasks for the selected project.

Well, ... that's an interesting predicament. Let's say a project is a Queue and a task is a Service and an ACL is used to show services for a queue

Or you could get more complicated and build it yourself.

[ksbuchanan]

Agree (http://doc.otrs.org/3.1/en/html/feature ... l#id608937) - OTRS reports "New custom field types (e.g. custom field type dropdown with an external data source) can be added with small effort as the fields are created in a modular, pluggable way."

...can be added with small effort.

That is very interesting - and yet there isn't ANYONE (seemingly) that can show how this "small effort" is accomplished!! I'm trying to find something a way to do this...and it seems to be SO elusive!!

Is there anyone that can help?

[crythias]

viewtopic.php?f=60&t=17033