A customer had a requirement from their sales users to be able to filter the communication screen to show all Emails, both inbound and outbound.

You can see below that I have managed this by creating the option to filter 'Emails Only'.

Emails get stored in the communication table through a variety of different system actions. Emails can be stored through the Outlook integration, by the Advanced Email Manager and of course outbound emails that are sent through the interface either to an individual or to a group or a set of contacts found through the search screens.

The obvious way that emails are identified is through the value held in the comm_action field. This is either 'EmailIn' or 'EmailOut' but there is another field comm_type that is used to hold information about a communication.

  • Emails that are created through marketing actions, and via the normal individual sending of emails are marked with a comm_type value of 'Email'.
  • Emails that are sent from a mass email from the search screen are marked as 'Task' in the comm_type field in the database.

This is storage of values in the comm_type field is inconsistent across Sage CRM's actions but it is a useful starting point.

You can add 'Email' as an option within the translations by going to

Administration -> Customisation -> Translations

and adding a new translation

  • Caption Code: Email
  • Caption Family: comm_type
  • Caption Family Type: Choices
  • US/UK Translation: Emails Only

You will then have the ability to filter communications by the type 'Emails' BUT some records may be missed.

You can solve that by creating an escalation rule that automatically checks communication records that are have a comm_action of either 'EmailIn' or 'EmailOut' that are of comm_type 'Task' and use the action 'Set Column Value' to change comm_type to be 'Email'.

But in order to do that...you will need to first make it possible to 'see' and 'edit' the comm_type field in the administration screens - by default this is a hidden system column. You need to do this in the database by changing the values of the record that describes the field in the custom_edits table. You can run this query...

[code language='sql']
update custom_edits
set colp_system = null,
colp_allowedit = null
where colp_colname = 'comm_type';
[/code]

Once you have done that you will need to refresh the system meta data.

Go to

Administration -> System -> Metadata

and refresh all.

Once the meta data has been refreshed you can then add the escalation rule.

Administration -> Advanced Customisation -> Escalation

I created an escalation rule based on the Communication table called 'update EmailIn/EmailOut as type email'

I gave it the trigger clause

comm_type = 'Task' and Comm_Action in ('EmailIn','EmailOut') and comm_datetime <#T

And the action 'Set Column Value' to be 'Emails Only'.