0

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?!

1
  • 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, 2015 at 19:20

2 Answers 2

0

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.

3
  • looks like a maintenance challenge with users coming and going over time
    – cropredy
    Sep 11, 2015 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, 2015 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, 2015 at 12:47
0

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.

1
  • 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, 2015 at 20:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .