Is it possible to make all dynamic field values of the same ticket same row

English! place to talk about OTRS development, programming and coding
Post Reply
franceswong0129
OTRS newbie
Posts: 12
Joined: 22 Jan 2019, 10:32
OTRS Version?: ITSM 6

Is it possible to make all dynamic field values of the same ticket same row

Post by franceswong0129 » 18 Feb 2019, 05:47

Dear all,

By using the Static report, i can generate a template based on SQL. However, i would like to put all values of the dynamic fields from the same ticket to be generated on the same row.

For example,
[Ticket id 1 ] [Dynamic_Field1_value] [Dynamic_Field2_value]
[Ticket id 2 ] [Dynamic_Field1_value] [Dynamic_Field2_value]

I have tried to used the following code to get all the ticket id within the period of time.

Code: Select all

my $SQL1 ="select id,tn from ticket where create_time >= '$StartDate' and create_time <= '$EndDate' and queue_id = 8 order by id asc";
    $DBObject->Prepare(SQL => $SQL1);

  while (my @Row = $DBObject->FetchrowArray()) {
        push @Data, [$Row[1]];
    }
However I cannot reuse the items inside @Data.

Code: Select all

push @DataToOutput, [$Data[0]];
it will only give the array ref but not the content.

Is that possible with the help of perl code ?

Best regards,
Frances Wong
Last edited by franceswong0129 on 25 Feb 2019, 05:22, edited 1 time in total.

skullz
OTRS wizard
Posts: 180
Joined: 24 Feb 2012, 03:58
OTRS Version?: OTRS 6

Re: Is it possible to make all dynamic field values of the same ticket same row

Post by skullz » 18 Feb 2019, 11:27

dynamic field data is save under dynamic_field_value table..
your sql should include LEFT JOIN statement to this dynamic field table

and then

Code: Select all

push @Data, \@Row;

franceswong0129
OTRS newbie
Posts: 12
Joined: 22 Jan 2019, 10:32
OTRS Version?: ITSM 6

Re: Is it possible to make all dynamic field values of the same ticket same row

Post by franceswong0129 » 25 Feb 2019, 05:42

Dear skullz,

Thank you so much for your reply! I tried the following code but turns out the excel is empty any ideas on this? Thx!!!

Code: Select all

    my $SQL1 ="SELECT *  FROM
    dynamic_field_value left join
    dynamic_field on dynamic_field_value.field_id = dynamic_field.id,
    ticket where create_time >= '$StartDate' and create_time <= '$EndDate' and queue_id = 8 order by id asc";
    $DBObject->Prepare(SQL => $SQL1);

  while (my @Row = $DBObject->FetchrowArray()) {
        push @DataToOutput, \@Row;
    }
Best Regards,
Frances Wong

Post Reply