This post will show you how to build a Report to see the most recent contact made by a User on an Organisation Record. This Report will look at all Activities made against the Organisation Record as well as all outbound emails.

This Report allows you to see which Organisations may need to be contacted as a part of your Account Management / Sales Process and highlight any Organisations that may require additional attention.

Step 1: Build a report based on Organisations

To build this Report first you will need to go Start > New > Report > Create a blank report and select Organisations. 

Step 2: Add relevant Columns

On the Details tab you will need to add the following two Columns:

• Organisation Reference
• Name

You may also want to add any additional columns that you will need in the Report for your own requirements.

Step 3: Add Additional Calculated Columns

You will then need to add the following Calculated Columns, these columns will concatenate information from both Emails and Activities and put them into a single column.

Assigned to:

IF(IF(related_emails.status = 'n/a' OR related_emails.status = 'failed','',IFNULL(related_emails.created_at,'')) < IFNULL(activities.created_at,''),  activities.assigned_to_name,  related_emails.created_by_user_person_name)

Created at:

IF(IF(related_emails.status = 'n/a' OR related_emails.status = 'failed','',IFNULL(related_emails.created_at,'')) < IFNULL(activities.created_at,''), activities.created_at, related_emails.created_at)

Subject:

IF(IF(related_emails.status = 'n/a' OR related_emails.status = 'failed','',IFNULL(related_emails.created_at,'')) < IFNULL(activities.created_at,''),  activities.name,  related_emails.subject)

Type:

IF(IF(related_emails.status = 'n/a' OR related_emails.status = 'failed','',IFNULL(related_emails.created_at,'')) < IFNULL(activities.created_at,''),  activities.activity_type, 'Email')

Step 4: Create Summary View

You will now need to create a Summary View to show only the most recent of all of the Activities against each Record. You need to add a Grouping Column, for this we recommend that you use the Organisation Reference, not the Name as this is a unique identifier, then add the Name as a value column.

You will now need to add the following Calculated Columns:

Assigned to:

SUBSTRING_INDEX(GROUP_CONCAT(PARENT('Assigned to') ORDER BY PARENT('Created at') DESC SEPARATOR ','), ',', 1)

Created at:

SUBSTRING_INDEX(GROUP_CONCAT(PARENT('Created at') ORDER BY PARENT('Created at') DESC SEPARATOR ','), ',', 1)

Subject:

SUBSTRING_INDEX(GROUP_CONCAT(PARENT('Subject') ORDER BY PARENT('Created at') DESC SEPARATOR 'wB12!3'), 'wB12!3' ,1)

Type:

SUBSTRING_INDEX(GROUP_CONCAT(PARENT('Type') ORDER BY PARENT('Created at') DESC SEPARATOR ','), ',' ,1)

This will now show you only the most recent Activity and the details of it for each Organisation, including who created it, when and what type of Activity it is.