Question:
I want to create a report that includes the first message on a ticket. I've tried using 'SELECT tickets_messages.message', but that doesn't work. How can I include message data in my report?
Answer:
The issue here is that you can only reference one table in your query's FROM clause. So if you are selecting fields from the 'tickets' table (e.g. your SELECT statement includes 'tickets.id', 'tickets.subject' etc.), you can't also select from 'tickets_messages'.
Instead, you have to use table cross-referencing to get message content using:
SELECT tickets.messages.message
One ticket can have many messages, so doing this will return multiple rows for each ticket that has more than message. To fix this, you can use 'GROUP BY tickets.id' so that only one row is displayed per ticket (containing the first message on the ticket).
Here's an example query that combines 'tickets' and 'tickets_messages' content:
SELECT tickets.date_created, tickets.subject, tickets.messages.message, tickets.department
FROM ticketsWHERE tickets.date_created = %LAST_WEEK%
SPLIT BY tickets.department
GROUP BY tickets.id
The message content often includes HTML formatting added by the user's email client, so it's not always easy to read. To remove this, you can export the results as CSV and use your spreadsheet program's "find and replace" function combined with wildcards/regular expressions to strip HTML tags & character entities like ' '.
