This feature implements a dynamic field of the type Database.
This feature implements a generic dynamic field type, which offers the possibility to gather data from external databases. Such connected datasets can be searched and filtered using additional masks.
Related found and marked datasets can be saved to the particular tickets through the dynamic field.
Database dynamic fields can be created the same way, as the default dynamic fields are created.
The following list of configuration options can be used for this feature.
This feature implements a configuration interface to create dynamic fields of the type Database.
Database dynamic fields can be created the same way, as the default dynamic fields are created. For this switch to the Admin → Ticket Settings → Dynamic Fields view. In this mask you can select the Database field from the ticket drop down box on the left side. Currently it's not possible to use the Database dynamic field in the article context.
Configuration General - Name:
Dynamic fields of the type Database need a unique name just like other dynamic fields, too. This name has to contain only alphanumeric values. This name will be used for internal handling of the field but will not be displayed.
Configuration General - Label:
The label can be individually set and can contain white spaces etc. It will be used as the field label in the different views.
Configuration General - Field order:
The field order allows a administrator to change the order of created dynamic fields. If this configuration get changed the general field order will be adjusted and other dynamic fields will be moved one position back.
Configuration General - Valid:
To use the dynamic field in the configured OTRS views it has to be set as valid. If the field is set to invalid it will disappear from all the configured views but no data will be lost.
Before an external database can be searched and the results be saved at the ticket through the dynamic field, the credentials have to be stored in the configuration of the dynamic field.
Type: The type of the desired database can be selected here. The field supports the default OTRS database types: MySQL, Oracle, PostgreSQL or MSSQL.
SID: This option is only available for Oracle connections and will be shown or hidden automatically. Within this option you have to enter the SID of your Oracle connection.
Driver: This option is only available for ODBC connections and will be shown or hidden automatically. Within this option you have to enter in the hostsystem previously configured ODBC driver to connect to the desired MSSQL database.
Server: The database host (hostname or IP-Address).
Port: The port of the database server.
Database: Defines the desired target database of the DBMS. This database will be used for queries.
Table / View: This table or view will be used for the queries.
User: The username for the database connection.
Password: The user password for the database connection.
Identifier: This select box will be automatically filled through Possible Values (description below). This field represents the value which will be stored in the dynamic field.
Multiselect: If this field is selected, it will be possible to store more than one value to the dynamic field. Those values will be stored comma separated.
CacheTTL: This value defines the period of validity of the database cache in seconds. Equal queries to the database will be answered through the cache (local filesystem) within this period instead of asking the database again.
Searchprefix: This value will be put in the front of every search term while using the autocompletion to search the database. Wildcard characters are supported as well. The searchprefix will be ignored during the detailed search, but it is still possible to use wildcard characters in those masks.
Searchsuffix: This value will be put in the end of every search term while using the autocompletion to search the database. Wildcard characters are supported as well. The searchsuffix will be ignored during the detailed search, but it is still possible to use wildcard characters in those masks.
Result Limit: The entered integer value defines the maximum amount of allowed results during a database search. This includes the autocompletion search as well as the detailed search.
Case Sensitive: If this field is selected, case-sensitivity will take effect on searches.
Possible values: As already explained the possible values will fill up the identifier field automatically, which defines the value that will be stored in the dynamic field. Possible values can be created as much as needed (or at least as many table columns as the database table has). The possible values defines the database columns to search in. It is possible to set the column name, a description (label) the field should have, the needed data type and if the field should be a search- or listfield.
Name: The exact name of the database column which will be requested through the database queries.
Description: The label of the field which will be displayed in the detailed search.
Datatype: The data type which will be stored in the dynamic field. Possible values: TEXT, INTEGER or DATE.
Filter: With the filter field, it is possible to choose a ticket attribute or a dynamic field as a filter for the related column. If the dynamic field is bound to a related ticket, the attributes will be used for the filter mechanism, otherwise the filters will be ignored. If filter will be configured to a table column, only search results matching to the search term and the related ticket attribute on exactly the configured column will be displayed.
Searchfield: Indicates if a field should be included in the search requests.
Listfield: Indicates if a field should be displayed in the results.
Dynamic fields of type Database have to be activated for the several masks in which they should be displayed like the other types of dynamic fields.
This can be done through Admin → System Administration → SysConfig, in which Ticket must be selected on the left hand side.
For every interface area (Frontend), in which the dynamic field of type Database should be displayed, the admin has to configure it to fit his needs. Examples:
Frontend::Agent::Ticket::ViewZoomfor the ticket zoom view.Frontend::Agent::Ticket::ViewPhoneNewfor new phone tickets.Frontend::Agent::Ticket::ViewEmailNewfor new email tickets.In each of these view configurations is an entry called
Ticket::Frontend::AgentTicket*###DynamicField. This configuration defines which dynamic field(s) should get displayed in this view. To add a dynamic field, the internal name of the field has to be filled in the Key field. The field Value can take the values 0 (deactivated), 1 (active) and 2 (active and mandatory).
This feature offers a functionality to store historical data. For this to work it´s necessary to activate and set SysConfig options, as visibly in the following screenshot:
In the configuration option for the SourceDynamicField it´s needed to fill in the already created dynamic (Database) field name, which will
be used to gather the historical data. In the related option TargetDynamicField the field(s) Key have to be filled with the table columns
of the connected external database, which will be readout.
For every column the related target dynamic field has to be configured in the field content. The gathered data will be saved in these
dynamic fields.
If the configuration is ready and active, the configured fields will be readout from the external database, since the source field gets a new value via the configured masks. The data will be searched by it´s stored identifier via an event module and the found values will be stored in the target dynamic fields.
AutoComplete::Agent###DynamicFieldDatabaseSearchGroup:
Framework, Subgroup:Frontend::AgentDefines the config options for the autocompletion feature.
Ticket::EventModulePost###950-StoreHistoricalDataGroup:
Ticket, Subgroup:Core::TicketUpdates dynamic fields, if configured ones will be updated.
DynamicFieldDatabase::StoreHistoricalData###1-SourceDynamicField1Group:
OTRSBusiness, Subgroup:CoreDefines the source dynamic field for storing historical data.
DynamicFieldDatabase::StoreHistoricalData###2-TargetDynamicFields1Group:
OTRSBusiness, Subgroup:CoreDefines the target dynamic fields for storing historical data.
DynamicFieldDatabase::StoreHistoricalData###3-SourceDynamicField2Group:
OTRSBusiness, Subgroup:CoreDefines the source dynamic field for storing historical data.
DynamicFieldDatabase::StoreHistoricalData###4-TargetDynamicFields2Group:
OTRSBusiness, Subgroup:CoreDefines the target dynamic fields for storing historical data.
An exemplary usage of DynamicField Database is as follows:
After the created dynamic fields are activated in the well known masks (like ViewPhoneNew, ViewEmailNew) a new text field
appears with the name, the dynamic field got in the configuration.
In this field it is possible to input searchterms and therefore execute a search over all configured database fields.
Otherwise do a click on the link Detailed search and start a detailed search in which the fields to search in are selected
explicitly.
Since search terms are typed in into the text field, a database search will be started over the configured columns and the result will displayed via an autocompletion below the text field. The more exact the search term is, the more exact will be the result (less result entries).
If the wished value will be displayed in the results, it can be selected via a mouse click or via the keyboard and therefore be added to the dynamic field results.
Via the link Details a popup screen can be accessed, which offers detailed information about the whole result row. This information includes the line headers and the data. This information can be used to get an overview about the rest (of the not configured) columns or to compare data.
The added result entries can be removed via the minus button.
The link Detailed search opens a new modal dialog to start a new database search. In this mask it is possible to select the fields to search on explicitly.
By default the first available field is activated, but it´s also possible to remove available fields or add additional ones. Only activated and filled fields are considered for the search. Wildcard characters '*' are allowed in every single field.
The database search will be executed via the Start search button and the results will be tabular displayed. If the search was successful, the results will be listed and one of the entries can be selected via a mouse click. The value will be added to the list of saved values afterwards.
Independent of using the autocompletion or the detailed search, every single result can just selected ones. If an agent tries to select a value multiple times, a related warning message is displayed.