Accounts with many-to-many relationship with accounts -- how to create a report on contacts

Question

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'

Answer 1

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!

Answer 2

What you're showing is not what we think of as a Junction object in the SF documentation. You may find it helpful to look at the Force.com Fundamentals Documentation on Creating Many to Many relationships. Instead you have a custom object that's a look-up on Accounts. A junction object, literally a junction between two different objects, would exist if you placed it between say Accounts and Contacts. This is probably why you're unable to get the relationships between Buyers and contacts you seek and unable to query the results you're looking for. Its also why RecordTypes would be helpful here.

You may find that using a RecordType just for the type of Account will suffice since the contact is related to the Account. However, both have an owner and may not necessarily have the same Owner. As an example, the contact could be considered a different kind of contact depending on their function within an organization and be given a different RecordType for that reason by an organization (decision maker, project lead, dealer, end user, etc). Their association with the Account is sufficient for them to be found via a query.

The important thing here is that the junction object allows both objects to be viewed as being related and their relationships seen as a many to many to relationship. I hope this helps you toward resolving your issue.

BTW, using SOQL, you wouldn't be doing a JOIN, instead we'd be looking at WHERE clauses. JOIN means something different in SF and isn't used in queries. You may want to look at the SOQL and SOSL language reference material. I suggest you bookmark the following page which provides links to all the latest SF reference materials: Salesforce Developers Site