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.