Custom Dashlet showing Tickets with specific Dynamic Fields

[tpsupport]

Hi there,

We have recently upgraded OTRS 3.0.8 to 3.1.7 where FreeText fields have been migrated to Dynamic Fields. Now with this change I needed to modify our custom Dashlet again that shows Tickets from a Ticket Search based on specific attributes. This Dashlet was created using a XML file. I have followed this article to create it: http://blog.otrs.org/2010/09/26/keep-an ... customers/.
It worked great with the Freetext fields but I cannot get it back to work with Dynamic Fields.

Here are the old attributes:
States=open;States=pending reminder;TicketFreeTextKey3=Support Escalation;TicketFreeText3=Yes

These are the new ones:
States=open;States=pending reminder;DynamicField_TicketFreeTextKey3=Support Escalation;DynamicField_TicketFreeText3=Yes

I have used the developers manual for OTRS 3.1 to find out which variables I should use just like I did with OTRS 3.0 by looking at the function TicketSearch().

Code: Select all

# DynamicFields
        #   At least one operator must be specified. Operators will be connected with AND,
        #       values in an operator with OR.
        #   You can also pass more than one argument to an operator: ['value1', 'value2']
        DynamicField_FieldNameX => {
            Equals            => 123,
            Like              => 'value*',                # "equals" operator with wildcard support
            GreaterThan       => '2001-01-01 01:01:01',
            GreaterThanEquals => '2001-01-01 01:01:01',
            LowerThan         => '2002-02-02 02:02:02',
            LowerThanEquals   => '2002-02-02 02:02:02',
        }

Can you tell me what I did wrong here and how to fix it? Thank you in advance!

[bwesaala]

Hi there,

Any success with your issue. I am trying to do exactly the same thing. I have implemented a dynamic field and I am trying to implement a dashlet that shows only tickets with this field active.

Let me know if you have made any progress. Will update in case I make some progress.

Thanks

Brian

[tpsupport]

Hi Brian,

No luck so far but I really need to get this addressed soon. I think I am going to open a bug report for it.


Regards, Rick

EDIT: Bug created: http://bugs.otrs.org/show_bug.cgi?id=8772

[crythias]

from Kernel/Output/HTML/DashboardTicketGeneric.pm:

Code: Select all

        # check if parameter is a dynamic field and capture dynamic field name (with DynamicField_)
        # in $1 and the Operator in $2
        # possible Dynamic Fields options include:
        #   DynamicField_NameX_Equals=123;
        #   DynamicField_NameX_Like=value*;
        #   DynamicField_NameX_GreaterThan=2001-01-01 01:01:01;
        #   DynamicField_NameX_GreaterThanEquals=2001-01-01 01:01:01;
        #   DynamicField_NameX_LowerThan=2002-02-02 02:02:02;
        #   DynamicField_NameX_LowerThanEquals=2002-02-02 02:02:02;

[bwesaala]

Hi crythias,

Thanks for this pointer. I would appreciate a little more instruction. I have a dynamic field with the name 'duedate'. I edited the DashboardTicketGeneric as follows

Code: Select all

# check if parameter is a dynamic field and capture dynamic field name (with DynamicField_)
        # in $1 and the Operator in $2
        # possible Dynamic Fields options include:
        #   DynamicField_NameX_Equals=123;
       DynamicField_NameX_Like=duedate;
        #   DynamicField_NameX_GreaterThan=2001-01-01 01:01:01;
        #   DynamicField_NameX_GreaterThanEquals=2001-01-01 01:01:01;
        #   DynamicField_NameX_LowerThan=2002-02-02 02:02:02;
        #   DynamicField_NameX_LowerThanEquals=2002-02-02 02:02:02;

and ran the otrs.RebuildConfig.pl.

My dashboard went blank. Commented out the line and everything was back to normal. Did I do it right?

Thanks again,

Brian

[crythias]

The code didn't tell you to modify the code. The code told you how to use your DynamicField as a query/filter.

If you have a dashboard module that needs to show tickets with specific DynamicFields, as per the title of this post, you already should have the module customized and you know how to specify the criteria *in general* from SysConfig. The missing piece is how to include a DynamicField as an additional criteria.

In the same place in SysConfig you'd include attributes for the criteria, use an appropriate DynamicField_YOURFIELDNAMEHERE_Equals=duedate

[tpsupport]

from Kernel/Output/HTML/DashboardTicketGeneric.pm:

Code: Select all

        # check if parameter is a dynamic field and capture dynamic field name (with DynamicField_)
        # in $1 and the Operator in $2
        # possible Dynamic Fields options include:
        #   DynamicField_NameX_Equals=123;
        #   DynamicField_NameX_Like=value*;
        #   DynamicField_NameX_GreaterThan=2001-01-01 01:01:01;
        #   DynamicField_NameX_GreaterThanEquals=2001-01-01 01:01:01;
        #   DynamicField_NameX_LowerThan=2002-02-02 02:02:02;
        #   DynamicField_NameX_LowerThanEquals=2002-02-02 02:02:02;

This finally makes sense now and works like a charm as well! Thank you so much for the help, I am going to look into the pm files first in the future.


Regards, Rick

[bwesaala]

No success for me.

I am trying to implement a dashlet that shows only tickets that have the due date set. Implemented Due date as dynamic field. Only certain tickets have due date set as it is not a mandatory field.

I tried this:
StateType=open;StateIDs=10;DynamicField_duedate_Equals=duedate(I susupect this value should be a boolean to check if due date is set or not)

Thanks for your help.

Brian

[crythias]

Brian,

I thought the examples given would have tipped you off regarding date utilization, but here goes:
DynamicField_duedate_GreaterThanEquals=2012-01-01 01:01:01;

What makes sense for your implementation is up to you.

[bwesaala]

Thank you very much crythias.

Slightly embarrassed by my questions now, but I have discovered quite a bit trying to get that working.

Thanks again!

[huschfor2]

Hi,

Ive got a dynamicfield called "Info". Its a CheckBox.
Can u tell me the Code for the Dashlet-Atribute?

Another dynamicfield called "Location" (Textfield) worked with following Code

Code: Select all

DynamicField_Location_Equals='OGO';

I tried different things yet but nothing worked!

Code: Select all

DynamicField_Info_Equals='1';
DynamicField_Info_Equals='checked';
DynamicField_Info_Equals='Checked';
DynamicField_Info_Equals='True';
DynamicField_Info_Equals='true';

DynamicField_Info_Equals=1;
DynamicField_Info_Equals=checked;
DynamicField_Info_Equals=Checked;
DynamicField_Info_Equals=True;
DynamicField_Info_Equals=true;

[crythias]

Backend/Checkbox.pm says:

Code: Select all

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

    if ( $Param{Operator} eq 'Equals' ) {
        my $SQL = " $Param{TableAlias}.value_int = ";
        $SQL .= $Self->{DBObject}->Quote( $Param{SearchTerm}, 'Integer' ) . ' ';
        return $SQL;
    }

    $Self->{'LogObject'}->Log(
        'Priority' => 'error',
        'Message'  => "Unsupported Operator $Param{Operator}",
    );

    return;
}
 

From what I'm seeing, DynamicField_checkboxField_Equals=1 *should* work. Checked boxes store "1" in value_int which is an integer.
It's quoted...

DB.pm:

Code: Select all

    # quote integers
    if ( $Type eq 'Integer' ) {
        if ( $Text !~ /^(\+|\-|)\d{1,16}$/ ) {
            $Self->{LogObject}->Log(
                Caller   => 1,
                Priority => 'error',
                Message  => "Invalid integer in query '$Text'!",
            );
            return;
        }
        return $Text;
    }
 

So if there's an error, it should log.
but if I'm reading this correctly, it insists on having a + or - prepended to 1 to 16 digits, which means:

Code: Select all

DynamicField_checkboxField_Equals=+1

should work.

[reneeb]

@crythias: no "(\+|\-|)" means a "+", a "-" or nothing (I think you missed the last "|")

[crythias]

Fair enough, then it should be working as is.

[huschfor2]

Thanks

Code: Select all

DynamicField_Info_Equals=+1;

works!

[reneeb]

Huh... that sounds like a bug.

[huschfor2]

Now Im looking for the code of an unckecked Box...

Code: Select all

DynamicField_Info_Equals=+0;
DynamicField_Info_Equals=-1;
DynamicField_Info_Equals=0;

didnt work...

[crythias]

not checked won't have a value in the data field, so not checked search won't be useful.