0

We're trying to create what seemingly should be an easy report (and can't get it to work) where we measure the total number of user activities in the Contact object over the past 7 days by user vs the total number of contacts the user owns over 7 days. The activities and contacts measured can only be of three certain status as is indicated in the contact record, Contact.Status__c (we don't want to measure activities for current customers). This last bit is where the complication stems from.

Here's what we've tried:

  1. A simple joined report against the Activities and Contacts. This doesn't work because there is no common User name to group across the report blocks. (We have the user name for the person that created the activity and have the contact owner name, they're not the same though because you sometimes have people creating activities on someone else's contact and we want to count those activities)

  2. Another joined report, except this time we created two custom report types. UserWithActivities (User is the primary, Activities is the sub) and UserWithContacts (User is the primary, Contact is the sub). This at first worked quite well because it gave us the common User we needed to sort the report type. The problem, however, is that we need to sort the activities to only include those activities on Contacts with a certain status (as noted above). The Activity object does not have a referenceable link (from what I can tell) to the Contact parent such that we can pull that status into the report. This is frustrating because the "Activity.Name" field has a direct reference to the Contact object, we just can't get it to go the next level.

  3. This is where we got to when I decided to post here. I think you can solve 2 by creating a lookup field on the Activity object to the Contact parent. Such fields are now possible by request under Winter '15. So we put in the request and now we have it. However, when creating the lookup field, it's blank, so I now need to create a workflow rule that says every time an activity is created, copy the SF id in the Activity.Name to the lookup Field (Activity.LookupToContact__c = Activity.Name) so we can reference that status field in the contact (LookupToContact__r.Status__c).

There's got to be an easier way to do this, right? (other than writing a vf page with a SOQL query in it).

Edit: Anyone have any suggestions on how to get the Activity to refer back to the Contact object to complete #3 without having to use the workflow rule I mentioned?

Edit 2: Well, turns out you can't run a workflow update on a Lookup field on an activity object. So that idea is out.

  • 1
    If I'm understanding your need correctly, a joined report won't get you what you need. You're effectively asking to group by two different fields, Contact.CreatedBy and Activity.AssignedTo. This is not supported in joined reports. I'm surprised that #2 is getting you what you need. – techbusinessman Dec 11 '14 at 1:08
  • #2 isn't exactly what we need, but using the User object as the parent solves the need part of the way. Do you have any suggestions on how to have the Activity refer back to the Contact in the report for #3? – StartingAgain Dec 11 '14 at 5:36
0

Trying to use the new Flows feature available to solve this and that would work except there's a bug in the implementation. The bug is a known issue that can be found here:

https://success.salesforce.com/issues_view?id=a1p300000008XjeAAE

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.