SQL query on DropDown dynamic fields

[srenon]

Hello,

I want to extract by SQL the value given by a dynamic dropdown field. I saw that the value, is stored in the dynamic_field table in the field config which is a longblob.

How to extract the text value of the list (selected by the user when the ticket is created) using the key of the dropdown field stored in dynamic_field_value.value_text.

Thanks for your help

[crythias]

You want the result as attached to a ticket or an article or the list that belongs to a dynamic field?

[srenon]

Easier,

How to have the two red circle items with SQL
Click on the picture to see it full size.


knowing that "Théme abordé" comes from a dropdown dynamic field

[crythias]

Let me ask the question again:
Do you want:
1) The result of the filled field
or
2) The list the field presents?

[srenon]

The result of the filled field given by

Code: Select all

SELECT ticket.*, dynamic_field.name, dynamic_field.label, dynamic_field_value.value_text from tickets left join dynamic_field_value on (dynamic_field_value.object_id=ticket.id) left join dynamic_field on (dynamic_field_value.field_id = dynamic_field.id AND dynamic_field.object_type = "Ticket")

Gives the index key of the string that was selected in the dropdown list, but not the selected string.

The list presented in the field is stored as longblob in dynamic_field.config

For example consider a dropdown containing list of colors

key | value
01 | yellow
02| red
03 | blue

how make a query which return ticket number and the color, not the key of the color?.

[crythias]

Gives the index key of the string that was selected in the dropdown list, but not the selected string.

so make the key equal to the string.

[srenon]

actually works!

Thanks

[pushreply]

Hi, could you help me:
let's say, I have this SQL Query:

1.

Code: Select all

SELECT ticket.*, dynamic_field.name, dynamic_field.label, dynamic_field_value.value_text from ticket left join dynamic_field_value on (dynamic_field_value.object_id=ticket.id) left join dynamic_field on (dynamic_field_value.field_id = dynamic_field.id) where dynamic_field.object_type="Ticket" and curdate() = date_add(date(ticket.create_time), interval 7 day);

2. I have a dynamic value: "Ticket1WeekOverdue" as Dropdown

How to display the query result (as a list in the dropdown)?
I'm using OTRS 3.2.4 - mysql (in Win 7)

Thank you!

[crythias]

@pushreply: viewtopic.php?f=60&t=17033