8

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

  • A question about your data model: would you describe a Contact as the buyer and seller in a transaction buying on behalf of an Account? (or does the Account buy directly from another Account?) It seems that the transaction between buyer and seller - your junction object - would more naturally be between two Contact records in the way that you describe it. The 'Type' of buyer and seller wouldn't be defined on a picklist, it would be inferred by whether a contact was in the Buyer lookup or the Seller lookup on that Junction record. – Mark Pond Dec 1 '13 at 0:35
  • By "Type List", you do mean RecordType don't you? You likely need a RecordType for contacts too! Can you show us a schema with just Accounts, contacts and your junction object isolated? (Owner/User might be helpful too) I suspect that would be very helpful for anyone who might be able to assist you. An no, off hand, I don't think custom objects would be the way to go. I suspect you need to know both what type of accounts and what type of contacts you have if you want to report on them in this fashion. – crmprogdev Dec 1 '13 at 6:12
  • @Mark Pond: The contacts for buyers and sellers buy and sell on behalf of their respective accounts. So, for instance, the report might be for a mailer informing the sellers associated with BuyerA that BuyerA is going out of business. Of course, the mailer would need to go to people (i.e., contacts). I think having the junction object between contacts rather than accounts would make things overly complicated for the users since a single buyer company might have many employees (contacts), and a single seller company might have many employees (contacts). But it might work. – djackiem Dec 1 '13 at 9:28
  • @crmprogdev: By "Type" I mean the actual standard Type field on the Account object. Eventually I will likely add RecordTypes, but for now I'm just trying to get the basic relationships in place. I've added a sketch of my schema to the original question -- it's very basic. Coming from a SQL background, the idea of adding repetitive data to the Contacts object feels wrong to me, but it seems like I always end up having to commit some SQL sins in Salesforce. – djackiem Dec 1 '13 at 9:36
3

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!

1

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

  • Thank you for your response and all of the helpful information. However, if I create a junction object between Accounts and Contacts, I then have some difficulties with the the most basic level of reporting that I need, which is simply to be able to see all the buyer accounts for a particular seller account and vice versa. My original (pseudo) junction object handled that perfectly. What if I keep my original (pseudo) junction object between accounts, and then add another junction object between account and contact and use a custom controller or an Apex trigger to manage those records? – djackiem Dec 1 '13 at 22:35
  • That would seem like a reasonable approach. You have nothing to lose by trying it. After all, the current one is already built and wouldn't seem to impact the new one. Alternatively, you might try a junction box between the existing one and contacts. I'd have to think about it, but it might establish the many to many that you're really looking for. – crmprogdev Dec 2 '13 at 14:01

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.