[SalesForce] Accounts with many-to-many relationship with accounts — how to create a report on contacts

I'm setting up an org that has a many-to-many relationship between "buyers" and "sellers." I'm using the Account object for both buyers and sellers and set the Type field picklist to have the choices "buyer" or "seller." Then, in order to complete the many-to-many relationship, I created a junction object called "Relationship" with two lookup fields: "Buyer" and "Seller." This works fine on the account level — if BuyerA has relationships with Seller1 and Seller2, then the record for BuyerA has the relationship related list populated with Seller1 and Seller2, and so forth.

However, I want to be able to create a report that goes down to the contact level: For instance, I'd like to be able to have the report show all Seller contacts for BuyerA. So, if BuyerA has relationships with Seller1 and Seller2, and Seller1 has contacts ContactS1a and ContactS1b, and Seller2 has contacts ContactS2a and ContactS2b, the report filtered on BuyerA should show: ContactS1a, ContactS1b, ContactS2a, and ContactS2b. A further requirement is that the report needs to be exportable, so I can't use a joined report.

Is it possible to do this with standard or custom Salesforce reports? If so, how might I go about it — I've tried every angle…custom reports, cross-joins, etc., but can't seem to get the results I need.

Alternatively, is the basic relationship structure flawed? Should I be using a custom object for buyers or sellers and not try to use the account object for both?

Edit: Here is a barebones sketch of the schema. And, if it helps at all to understand my situation, here's what the query for the report would look like in MSSQL:

SELECT c.*
    FROM Account a
        JOIN Relationship r ON r.BuyerId = a.Id
        JOIN Account a2 ON a2.Id = r.SellerId
        JOIN Contact c ON c.AccountId = a2.Id
    WHERE a.Name = 'BuyerA'

schema

Best Answer

It's a few months later and I finally found the solution to this problem -- and it turns out it's already built into Salesforce (although I believe it's only available in the enterprise edition and above): Partner Portals. You don't actually have to use the core partner portal functionality, but by activating the Partner object, you automatically get a many-to-many relationship between accounts (which is exactly what I wanted). Here's what I did:

Turn on the Partner object: Setup > Customize > Partners > Settings; then check the box to enable partners and hit save.

Add the Partner Account field to the Account Layout: Setup > Customize > Accounts > Page Layouts; edit the default page layout and just drag the Partner Account field somewhere. It’s actually not editable, but I guess it’s nice to see…or maybe making it visible triggers its functionality (not sure).

To make an account a partner account, you must first create it and save it, and then you'll see a button that says “Manage External Account”; click on it an select “Enable As Partner” – now the Partner Account checkbox will be checked.

Then, the way you link an account with a partner account is you scroll down to the bottom of the account page to Partners related list. Click on “New” and select a partner account, give it a role, and save.

If you want to then run a report showing all contacts for a specific account's partners, you'll need to create a custom report with the primary object being accounts, the secondary object being partners, and the tertiary object being contacts. When you run the report, filter it by the original account, and you'll see all contacts for that account's partners!

Related Topic