Coming soon: Your brand new Help Center & Community! Get a sneak-peek here

How to distinguish internal comments and forwarded messages on a database level?

I need on a database level distinguish between internal comments and forwarded messages. From what I've seen, both of them have slevel  = 1 so how can they be distinguished?

The reason I am asking is because a customer wanted a pane in the view ticket screen that only shows internal comments, not forwarded messages. And I cannot see in the documentation how this can be done but we are doing it somehow in the user interface.

RE: How to distinguish internal comments and forwarded messages on a database level?

Hi Patrik,

I took this case as a fun saturday research quest. :)

My research indicates that the definition of a "comment" seems to be based on a combined state rather than an id or a flag.

To better understand the combined state we first need to understand a couple of logic "facts".

  • A message can be internal or external
  • A message can have zero or more recipients 
  • The internal flag, [ej_message.sLevel] = 1, is what makes the message have the red dashed border
  • A message viewed as a comment, is in fact an internal message without any recipients
  • The recipients of a message are stored as one or more records in the table [message_header]
  • The recipient record in the table [message_header] seems to be indicated by the string "To" in the [message_header.name]-field

So, as far as my research indicates, the technical defintion of a comment is:

A message flagged as internal without any related records in the [message_header]-table where the record contains "To" in the [message_header.name]-field.

With pure SQL, where the owning ticket has ID=40, this could be defined as below:

select * from crm7.EJ_MESSAGE m
where m.ticket_id = 40
and m.slevel = 1
and m.id not in (select mh.message_id from crm7.MESSAGE_HEADER mh where mh.message_id = m.id and mh.name = 'To')

To get the same result using a SearchEngine in CRMScript, you will probably have to transform the query a bit and maybe split it up in multiple queries. But this is the basic concept at least.

Also, you might have to run some further tests regarding other possible recipient-records like maybe "Cc" and "Bcc". I've have only made a basic overlook. :)

Good luck!

Von: Marcus Svenningsson 9. Mai 2021

RE: How to distinguish internal comments and forwarded messages on a database level?

Hi Marcus,

It is then as I suspected. Also noticed that comments did not have a record in the message_header table.

So now I need to figure out if this can be translated to "simple values" in a message element in a custom screen.

Von: Patrik Larsson 10. Mai 2021

RE: How to distinguish internal comments and forwarded messages on a database level?

Hi Patrik,

Your first statement is actually not true, it is probably just a assumption based on your own test-environment.

A "comment" can have records in the message_header-table, they just can't be records with "to" in the name field. All my comments in SO's own test environment (9.1 R03) had at least 3-4 records in the message_header-table. They may be related to other extra fields or something, it isn't obvious why they are created in that environment. But it at least indicates that a "comment" can have other kinds of related records and still be viewed as a "comment". So you cannot use that as a valid state. 

In a completely custom screen, a custom SearchEngine or an Archive-query, that returns a collection of MessageId's to view, could be used.

But based on your other post (see link below), that might not be what you are looking for.

https://community.superoffice.com/en/technical/Forum/rooms/topic/superoffice-product-group/customer-service/custom-screen-showing-only-internal-comments-not-forwarded-messages/

If your intent is to get the screen element "View elements/Messages" to filter out only comments, you are facing a "black box", that might be fully documented, or not. There might be some simple flag that you can filter on that isn't documented, or not. This part probably needs to be answered by somebody with insight in the underlying source code such as Michel, Sverre or Stian. :)

 

Von: Marcus Svenningsson 10. Mai 2021

RE: How to distinguish internal comments and forwarded messages on a database level?

Hi,

You have somewhat overlapping threads running, and I will try to answer in one place.

I think Marcus's assumptions are correct that you will face some difficulty solving your exact use case simply based on "Access level" on messages. We have several scenarios where messages can end up as "Internal" but have recipients ;

  • Forwarded messages
  • Inbound responses from contacts not directly connected to request
  • Custom screens 
  • etc etc

Core purpose for this field is to restrict what is shown on Customer centre (but not limited to).

Think perhaps it would be easier for you to create some other way of detecting what messages you should show on your "custom" tab. 

Von: Michel Krohn-Dale 10. Mai 2021

RE: How to distinguish internal comments and forwarded messages on a database level?

Hi,

 

I just ran into a similar situation, where I will have to distinguish a comment from an internal message.

Far as I know, a comment will never have a corresponding row in the outbox table.

Comments will also be written by an agent, so ej_message.customer_id should be -1.

Would it not be possible to fetch all comments in a request by running something similar? Or am I missing something here? :)

#setLanguageLevel 3;

SearchEngine outboxMessages;
outboxMessages.addField("outbox.message_id");
outboxMessages.addCriteria("outbox.ticket_id","Equals", ticketId);

String outboxMsgId;

for(outboxMessages.select(); !outboxMessages.eof(); outboxMessages.next())
{
  outboxMsgId += outboxMessages.getField(0) +",";
}

SearchEngine commentMsgId;
commentMsgId.addField("ej_message.id");
commentMsgId.addCriteria("ej_message.id", "OperatorNotIn", outboxMsgId);
commentMsgId.addCriteria("ej_message.customer_id", "Equals", "-1");
commentMsgId.addCriteria("ej_message.ticket_id" , "Equals", ticketId);
commentMsgId.execute();

print(commentMsgId.getField(0));

 

Von: Petter Näslund 11. Jun 2021