Show UserFirstName in Dashlets!

[davidappleby]

I've seen so many people asking for this and responses saying that it can't be done but I've done it!

Step 1:
Take a copy of OTRS\Kernel\System\TicketSearch.pm and paste it into OTRS\Custom\Kernel\System\TicketSearch.pm
Any files copied like this automatically get used instead of the default. Prevents it from being overwritten when upgrading.

Step 2:
Edit OTRS\Custom\Kernel\System\TicketSearch.pm:
Just underneath

Code: Select all

    # add ticket flag table
    if ( $Param{TicketFlag} ) {
        my $Index = 1;
        for my $Key ( sort keys %{ $Param{TicketFlag} } ) {
            $SQLFrom .= "INNER JOIN ticket_flag tf$Index ON st.id = tf$Index.ticket_id ";
            $Index++;
        }
    }

add

Code: Select all

	# add customer user table
	my $SQLFrom .= "LEFT JOIN customer_user cu ON st.customer_user_id = cu.login ";

	# add user table for owners
	my $SQLFrom .= "INNER JOIN users ou ON st.user_id = ou.id ";
	
	# add user table for responsible
	my $SQLFrom .= "INNER JOIN users ru ON st.responsible_user_id = ru.id ";

then add to this list

Code: Select all

    # other ticket stuff
    my %FieldSQLMap = (
        TicketNumber          => 'st.tn',
        Title                 => 'st.title',
        CustomerID            => 'st.customer_id',
        CustomerUserLogin     => 'st.customer_user_id',

like so:

Code: Select all

    # other ticket stuff
    my %FieldSQLMap = (
        TicketNumber          => 'st.tn',
        Title                 => 'st.title',
        CustomerID            => 'st.customer_id',
        CustomerUserLogin     => 'st.customer_user_id',
        CustomerUserFirstName => 'cu.first_name',
        CustomerUserLastName  => 'cu.last_name',
        OwnerFirstName        => 'ou.first_name',
        OwnerLastName         => 'ou.last_name',
        ResponsibleFirstName  => 'ru.first_name',
        ResponsibleLastName   => 'ru.last_name',
    );

This joins on the users and customer_users tables and maps the first and last names to proper $QData names

Step 3:
Edit your .dtl file (e.g. OTRS\Kernel\Output\HTML\[ThemeName]\AgentDashboardTicketGeneric.dtl
Now instead of only having $QData{"Owner"} or $QData{"Responsible"} which just show user logins, you can now use $QData{"OwnerFirstName"} etc. as defined in TicketSearch.pm

Let me know if you found this useful! I'll be answering questions.

[pizzadood]

Thanks for the info! I don't know if I'm in the right direction but... can you apply this to values from dynamic fields?

[davidappleby]

Thanks for the info! I don't know if I'm in the right direction but... can you apply this to values from dynamic fields?

I don't know - that whole area is a lot more complicated. You'd have to have a look through ticketsearch.pm - there's some code relating to dynamic fields is in there...

Here's some code pulled from "Kernel/Output/HTML/Standard/AgentTicketOverviewSmall.dtl":

Code: Select all

# example of how to use fixed dynamic field blocks for customizations
# Note: Field1 and Field2 are the names of the fields and had to be replaced with the actual
# field names
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field1 -->
#                    <th class="DynamicField Last $QData{"CSS"}">
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field1_Sortable -->
#                        <a name="OverviewControl" href="$Env{"Baselink"}Action=$Env{"Action"};$Data{"LinkSort"};SortBy=DynamicField_$LQData{"DynamicFieldName"};OrderBy=$LQData{"OrderBy"}">$Text{"$Data{"Label"}"}</a>
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field1_Sortable -->
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field1_NotSortable -->
#                        <span>$Text{"$Data{"Label"}"}</span>
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field1_NotSortable -->
#                    </th>
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field1 -->
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field2 -->
#                    <th class="DynamicField Last $QData{"CSS"}">
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field2_Sortable -->
#                        <a name="OverviewControl" href="$Env{"Baselink"}Action=$Env{"Action"};$Data{"LinkSort"};SortBy=DynamicField_$LQData{"DynamicFieldName"};OrderBy=$LQData{"OrderBy"}">$Text{"$Data{"Label"}"}</a>
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field2_Sortable -->
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field2_NotSortable -->
#                        <span>$Text{"$Data{"Label"}"}</span>
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field2_NotSortable -->
#                    </th>
#<!-- dtl:block:OverviewNavBarPageDynamicField_Field2 -->

This seems to show how to display dynamic fields in ticket grids. I haven't tried it out myself but it looks like there's something you could use in there.

[skullz]

thanks for the tutorial..
but i have a little problem her..

1. I have an open ticket.



2. After applying this tutorial, my open ticket is gone.




Is there something wrong??


EDIT: Nevermind..Promblem solved..i used another way by editing ticket.pm

[davidappleby]

What way did you use, out of interest?

[bwesaala]

Hi davidappleby,

Thank you for your tutorial. However I could not get it to work. I see the extra column is being created but no data is being pulled from the database so the coulmn remains empty.

Path to my changed TicketSearch.pm is:
OTRS_HOME/Custom/Kernel/System

I added the following lines as per your instructions:

Code: Select all

 # add customer user table
    my $SQLFrom .= "LEFT JOIN customer_user cu ON st.customer_user_id = cu.login";

    #add user table for owners
    my $SQLFrom .= "INNER JOIN users ou ON st.user_id = ou.id";

    #add user table for responsible
    my $SQLFrom .= "INNER JOIN users ru ON st.responsible_user_id = ru.id";

I run the otrs.RebuildConfig.pl script to load the changes but it still did not work. Any idea where I could be going wrong.

Thanks

Brian

[ndhvu275]

Hi

Maybe for a long time. But it's now very helpful for me to getting start customization with OTRS

Btw, one more thing is diffirent as my challenge. Would you please to show the coding to add filter functionality of this field. Or do you have some post like my asking. Please help me

Thanks in adv

Vu Nguyen

MOD Note: Please ask questions in the main forums. HowTos are moderated and need to be approved. They're not intended for question/answers