We have a custom object that we use to replace Activities and in this object, a user can track which other employees attended a meeting via a lookup(User). (There are 4 fields). The owner of the record is the generally the first attendee and there could be 4 others... When creating a report, I want to see how many meetings a user attended, either as the owner or an attendee, how do I do this? Right now, if I group it by Owner, it doesn't show me the other meetings. I thought maybe I need a formula field on the object but don't know how to go about writing it. (I'm not a formula rockstar). I tried a User/Object report type and that didn't work... I tried using filters and report formulas on in the report, and bucketing doesn't work because its essentially 5 fields to bucket. Help?!

  • so a user U could be present as an owner or in attendee1__c, attendee2__c, attendee3__c, or attendee4__c field and from one event to the next user U could be in any of these 5 fields? – cropredy Sep 11 '15 at 19:20

There is probably a better way, but this way should work. If you have a lot of users, though, it will not be efficient.

On your Activity__C object, create a formula field for each user who could be listed as an attendee. I would probably label the fields with the user's name.

You can use a formula like this to get the count. You will need to modify it to match the number of fields and your naming conventions. This also assumes that you are using a lookup for your Activity fields.

if(owner.username = [email] || attendee_1__c.username = [email] || attendee_2__c.username = [email], 1, 0)

With this, if the set user was listed in any of the fields, the formula for their name becomes a 1. Otherwise, it is a 0. Then, you can build a report that shows those fields as columns. The grand total of each person will be listed at the bottom of the report (or in the sub counts if using a summary style report.

  • looks like a maintenance challenge with users coming and going over time – cropredy Sep 11 '15 at 20:28
  • Yes, I was going to say the same. We have only 85 users, not all of them would need to be part of this report. There aren't a lot but the maintenance might be a pain although we don't have high turnover so it could also work. – Heidi Sep 13 '15 at 20:43
  • This is probably by far the least desired solution to your predicament. I simply listed it because it would work. – smckitrick Sep 14 '15 at 12:47

Because of your database design using a SFDC report isn't going to be practical as one would normally make Activity__c have a child record called Attendee__c and each attendee listed there. Then reporting would be easy.

I'm going to assume that you aren't an Apex developer and are looking for a point-and-click solution. I'm also going to assume you don't have Conga Composer, an app exchange tool that could do this report via a clever Excel template.

You could do the following (some ugliness involved):

  1. Create a formula field (text 255): Attendee_List__c (assumes average name len < 50 chars)
  2. The formula field is the concatenation of the Owner.name, semi-colon, attendee_1__r.name, semi-colon, attendee_2__r.name, ...
  3. Run a report on attendee_List__c
  4. In the browser, type in a user's name in the search box, the browser will tell you the number of occurrences for that name

If you want to see a report of # of meetings by user, then take the output of #4 into Excel and then split Attendee_List__c on semi-colon and put results into one column. See this Excel tip using TRANSPOSE function (or Google for others).

Then you can use Excel pivot table to count meetings by Attendee.

  • Thanks for the detailed comment! I will see what I can do when I'm back in the office, I'm out this week. Your assumption was right, I'm not a developer and we don't have Conga. :) – Heidi Sep 13 '15 at 20:40

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.