1

I am struggling to build a report that links and Opportunity to a Contact to a custom object. Here are the basics:

Opportunities are actually jobs and the account id is the business. Each opp has a custom field named Particpant__c [lookup(contact)] that links the Opportunity (in this case a job) to the person who has the position (Participant__c). I then have a custom table (Dogs__c) that assigns each Contact to their dogs (Master[Contact]-Detail[Dogs__c]). For instance:

Acme Vet (Account) would have a Thursday job (Opportunity) for Bob Loblaw to walk four dogs.

What I can't seem to sort out in Salesforce or SOQL is how to run a report that shows Opportunities, Contacts (Participant__c) and Dogs so that I could see:

Opportunity    Participant    Dog
Thursday Job    Bob Loblaw    Sissy
Thursday Job    Bob Loblaw    Missy
Thursday Job    Bob Loblaw    Prissy

Obviously, I would prefer to summarize as
Thursday Job    Bob Loblaw    Sissy, Missy, Prissy

But I'll take what I can get :) I've taken a look at joined reporting and creating a new report type, but I can't seem to get the right combo of things to make the report work. I know in SQL/MySQL this would be along the lines of:

SELECT Opportunity.Name, Contact.Firstname, Contact.Lastname, Dogs__c.Name
FROM Opportunity JOIN Dogs__c ON Opportunity.Particpant__c = Dogs__c.Contact_ID__c

But I'm baffled as to how to make it work here.

Thanks, KMT

1

A custom report type whose base object is Opportunity can reference both Account (lookup) and the Contact (participant lookup). However, it can't go down from Contact to Dogs.

One workaround to this would be to have an after insert/update/delete/undelete trigger on Dogs that rolled up all Dogs on a given Contact into a Text Area Long field on Contact. This new field, say `RSF Dogs' then becomes available to the custom report type and you can then create your report to show lines like:

Thursday Job Bob Loblaw Sissy, Missy, Prissy

Assuming you are au fait with APEX triggers, there are numerous examples that can be located by googling. The estimable @sfdcfox has a good one here: https://developer.salesforce.com/forums/ForumsMain?id=906F00000008yWuIAI

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.