Sort tickets by new article

[Mothra]

Just checked in the API, and it seems the TicketSearch method does not support sorting or ordering results by whether they have a new article. Therefore, if you have 40 or so open & unlocked tickets in a queue view, or are only seeing the top 5 open tickets in a dashlet, you can't show the most important tickets (i.e. those to which customers have responded) at the top.

I've logged an enhancement request to get this added, but I was just wondering if anyone else had encountered this issue and managed to implement a fix/workaround... before I dive into the code myself to come up with a temporary solution?

[Alexander Halle]

Hi,

is this related to bug 5658, can you post the bug id ? I would suggest to post the idea on otrsteam.ideascale.com instead of an enhancement bug.

Regards

Alexander

[Mothra]

No, I hadn't actually seen bug 5658 - the answer is a little dispiriting too (Really? Sorting data in a database by a particular value is not technically possible?.

For reference, the bug I raised was 6449.

[Mothra]

I would suggest to post the idea on otrsteam.ideascale.com instead of an enhancement bug.

I hadn't come across this before. I've registered and posted the suggestion, but does anyone at OTRS really look at this? I've seen plenty of similar sites become places where good ideas go to die...

[Mothra]

Been digging around in the database and found the following:


* Users (not tickets) have unread articles
* ticket_flag is linked to ticket as "0 to many"
* article_flag is linked to article as "0 to many" (and article is linked to ticket as "many to 1")
* A ticket shows a gold star if there's an entry in ticket_flag with 'Seen' value of 0 and your user id.
* A ticket shows a silver star if there's an entry in ticket_flag with 'Seen' value of 0 and someone else's user id, OR if there is no entry in ticket_flag at all.
* Articles show as unread, unless there's a corresponding entry with 'Seen' value of 1 against your user id.
* Zooming on a ticket and reading the article adds entries to the article_flag table, showing the article as read (article_key = 'Seen', article_value = 1). When you read the last article for a ticket, instead of adding an entry to article_flag, an entry is added to ticket_flag to mark the ticket as read (ticket_key = 'Seen', ticket_value = 1). At this point, when you go back to the ticket list view, the star has disappeared, but only for you - not anyone else.

So, unfortunately, all you can deduce from the database is that a customer response has not been read by everyone - there's no easy way to determine just that it has been read by someone.

[crythias]

Are you saying ticket_flag Seen 0 isn't set on a ticket_flag when a new article arrives?

(I'm sorry if I missed the important part of Mothra's post)

[Mothra]

Are you saying ticket_flag Seen 0 isn't set on a ticket_flag when a new article arrives?

(I'm sorry if I missed the important part of Mothra's post)

No. If any of the ramble above implied that, it's because I thought at the time that this sometimes was the case for certain types of article. However, my final summary still stands - the only thing you can assume by scanning the ticket_flag table is that someone, somewhere hasn't read all of the articles.

[Mothra]

Seems I may have been right...

when a new ticket is created via the Customer UI, an entry is added to ticket_flag with Seen = 1 (which means all articles have been read) and user id of "1".

When the agent replies to that ticket, the same entry is updated to Seen = "0", still associated with user id "1". If the agent reads all the articles on the ticket, you get an additional entry with Seen = 1, associated to the agent's id.

If a customer replies to the ticket, no new entry is added to ticket_flag, but the second entry above is changed to Seen =0.

Complicated, no?

[crythias]

That so far seems normal.

Did a specific user have it unseen?
SELECT COUNT(*) FROM ticket_flag where ticket_key="SEEN" AND ticket_value="0" AND create_by=userId AND ticket_id=ticketid;

How many people who have ever looked at the ticket have it unseen?
SELECT COUNT(*) FROM ticket_flag where ticket_key="SEEN" AND ticket_value="0" AND ticket_id=ticketid;

Who has ever looked at the ticket unseen?
SELECT create_by FROM ticket_flag where ticket_key="SEEN" AND ticket_value="0" AND ticket_id=ticketid;

Who hasn't looked at the ticket ever? (can't easily be done unless you cycle through the userIds of all your agents.)

The real issue seems to be that unless an agent has touched the ticket, he won't know if the ticket has new unseen?

[Alexander Halle]

I hadn't come across this before. I've registered and posted the suggestion, but does anyone at OTRS really look at this? I've seen plenty of similar sites become places where good ideas go to die...

otrsteam.ideascale.com is a new service actively used by OTRS employees and users to collect and evaluate the ideas. This is to prevent the dying of good ideas and for a better overview than enhancement bugs. I think chances are high to see popular ideas implemented in new OTRS releases but this takes some time of course.

[crythias]

A workaround that was suggested off-list: Create a new state/status of "Responded" as type open. You'd set that as follow-up state in the Sysconfig and you can sort by that state in Tickets, status view.

[Valsimot]

A workaround that was suggested off-list: Create a new state/status of "Responded" as type open. You'd set that as follow-up state in the Sysconfig and you can sort by that state in Tickets, status view.

Great idea - but what would have to be changed? Adding "responded" to Ticket::ViewableStateType doesn't suffice as it doesn't show up in the ticket state list, also, where would I have to set the translation (i.e. into German)?

[crythias]

what would have to be changed

Admin tab
States
Add State
Name: reagierten (?)
State Type: Open

Then
Edit Config Settings in Ticket -> Core::PostMaster
PostmasterFollowUpState
reagierten

[Valsimot]

what would have to be changed

Then
Edit Config Settings in Ticket -> Core::PostMaster
PostmasterFollowUpState
reagierten

I used "beantwortet" . and thank you explaining the inner workings!
Now I'm looking for a way to implement it in several languages (I'll open another thread if I don't find and answer in the docs and forums), but I have a question about the second step: Why is it neccessary to change Core::PostMaster PostmasterFollowUpState as well? The addition of the ticket state "answered/beantwortet" already did the trick and I can choose that state when replying and create a search template that shows these tickets (modified, whether I'm owner/responsible). The second modification seems like every ticket would automatically receive the state "open", whether I reply to it or not?

[crythias]

Why is it neccessary to change Core::PostMaster PostmasterFollowUpState as well?

PostMasterFollowUp can be used to "flag" when a customer has replied to the ticket (via email). ... Basically, a reminder by state who updated the ticket last - Agent or Customer.