Salesforce: Activity Type reporting

Activity is pretty unique in Salesforce.com, it consists of two objects: Event and Task, but it have almost similar structure. When you add a custom field for Activity, instead of click Event field or Task field, but you will find a menu Activity Custom Fields under Activities menu. In structure level, the field will be added in both Event and Task.

Type is standard field in both Event and Task, you can customize to have same values or different between Event and Task.

When come to reporting, you can run a report with combined data between Event and Task, just select standard report type Tasks and Events. Type field will be shown as Activity Type on the report.

We found an issue when would like to group by Activity Type, if both Event and Task have same values, instead of group them into the same group, it will be group into 2 group, one for Event and one for Task.

Solution:
1. Formula Field
When you create formula field in 'Activity Custom Fields', Type field is not visible for Activity - NOT POSSIBLE.


2. Workflow Rule
- Create a Custom Text field in Activity Custom Field

- Create custom field on Activity
o Name = Type
o Type = Text
o Length = 25

- Create Workflow rule on Event
o Name = Update Type from Event
o Evaluation Criteria = Evaluate the rule when a record is created, and every time it’s edited
o Description = To update custom field Type with event type for reporting purpose
o Rule Criteria = Event: Type NOT EQUAL TO null

- Create Field Update action on Event
o Name  = Update Type from Event
o Field to Update = Event: Type
o Field Data Type = Text
o Formula Value = TEXT(Type)
o Associate this action to above Event workflow and activate the Workflow

- Create Workflow rule on Task
o Name = Update Type from Task
o Evaluation Criteria = Evaluate the rule when a record is created, and every time it’s edited
o Description = To update custom field Type with task type for reporting purpose
o Rule Criteria = Task: Type NOT EQUAL TO null

- Create Field Update action on Task
o Name = Update Type from Task
o Field to Update = Task: Type
o Field Data Type = Text
o Formula Value = TEXT(Type)
o Associate this action to above Task workflow and activate the Workflow

Modify the report to group by custom Type field, see the result below:

If you have any existing data, you need to mass update Event and Task to populate Type to the custom field.

3. Apex Trigger
This is exactly the same logic with using Workflow, since we can use workflow to achieve this, why need to use trigger?


Reference: Reporting on the Activity Type field