5

I have an account with 4 contacts, two are direct and two are indirect contacts. I'm trying to retrieve all 4 contacts in an SOQL query but can't seem to get it to work .

For example if I run this query in the developer console I get back all 4 records and it shows the same Account.Id for all records (note i'm using my developer account that only has a single Account and the 4 contacts for these tests.

SELECT Id, Name, Email, Account.Id FROM Contact

But if I ask for the Contacts by specifying the Account

SELECT Id, Name FROM Contact where Account.Id = '<SOME_VALID_ID>'

I only get the two direct contacts . Similarly when I run this query to get all the contacts for an account I also only ever get the two direct contacts back.

SELECT Id, Name, (SELECT AccountId, Name, Email, HomePhone  FROM Contacts where AccountId = '<SOME_VALID_ACC_ID'>) FROM Account WHERE Id = '<SOME_VALID_ACC_ID'>

This query also only returns true if don't constrain the inner SELECT on AccountId. If anyone could let me know if this is possible and how to do it I'd greatly appreciate it.

|improve this question
  • What do you want to get ? The first query is giving you all contacts – sfdcFanBoy Jun 28 '16 at 16:52
  • Are you using the Salesforce functionality that allows you to relate a single contact to multiple accounts? Your usage of 'Direct' and 'Related' contacts makes me think this, but it would be good to confirm. – Derek F Jun 28 '16 at 18:02
  • yeah sorry @sfdcFanBoy - this is in my personal devloper account and there's only a total of 4 contacts in the system. I'd like to get the 4 contacts that are associated with the Account.Id in question. That doesn't seem possible – earthtrip Jun 28 '16 at 19:37
  • @DerekF - yes i'm using the new (I think summer 2016?) feature of relating a single contact to multiple accounts. I'm super new to SalesForce so hopefully I'm not using the wrong terminology here. What's puzzling is that the query works fine in the developer console but not when I run it in Apex code. – earthtrip Jun 28 '16 at 19:39
  • Updated original question to use SF terminology – earthtrip Jun 28 '16 at 19:50
7

After clarifying in a comment on your question, we're working with the Associate Contacts with Multiple Accounts feature, which was made generally available in Summer '16 (API v37.0)

After you've enabled Contacts to be related to multiple Accounts, if you wish to retrieve all the related contacts on an Account, you'll need to query the AccountContactRelation object, which is the junction object Salesforce is using to track the Many-to-Many relationship.

The following queries should work

Using a semi-join:

SELECT Id, Name FROM Contact WHERE Id IN (SELECT ContactId FROM AccountContactRelation WHERE AccountId = <account Id here>)

Using a parent-child subquery

SELECT Id, (SELECT Contact.Name FROM AccountContactRelations) FROM Account WHERE Id = <account Id here>)

The different results that you're getting between the dev console and compiled Apex may be due to your Apex using an API version of 36.0 or below.

|improve this answer
  • As an aside, anyone know a good way to figure out what the API name of a standard sObject is? I ended up needing to go into the workbench to find the object API name for this newly introduced object because the object's label in the web interface (Account Contact Relationships) is not the same as the API name. – Derek F Jun 28 '16 at 20:41
  • You could loop through the global describe and match on the label. for (SObjectType s : Schema.getGlobalDescribe().values()) { if ('Account Contact Relationships'.equals(s.getDescribe().getLabel())) system.debug(s); }. – Adrian Larson♦ Jun 28 '16 at 21:08
  • Do these approaches include the Contact records that are "directly" related (via AccountId, I assume)? – Adrian Larson♦ Jun 28 '16 at 21:09
  • Thanks @DerekF! Worked perfectly. I didn't realize there was a junction object in there. I need to do some more reading up on this apparently. Thanks again! – earthtrip Jun 28 '16 at 21:17
  • 2
    @AdrianLarson Direct is a standard field of the new AccountContactRelation object. I believe that a given Contact can only be directly related to one Account. From what I've seen in my company's org, selecting the option to allow Contacts to be related to multiple Accounts wipes out the AccountId of existing contacts (and uses that to determine which record in AccountContactRelation should have Direct set to true). – Derek F Jun 28 '16 at 21:43
1

If your AccountId is actually populated with the Id of the Account record, you should get a return value from:

SELECT Name Contact WHERE AccountId = '<valid_id>'

Note that it is preferable to use AccountId over Account.Id.

You also don't need to filter your subquery if you go that route. Again, if the lookup is populated, you should see results:

SELECT (SELECT Name FROM Contacts) FROM Account WHERE Id = '<valid_id>'

If you are not seeing results, it is possible that you are running into a permissions issue. That is unlikely, however, since you seem to have record visibility based on your unfiltered query.

It is unclear based on your post what you mean by "direct" versus "related" Contact records, but if by "related" you mean something other than "related via Lookup" the relationship will not be populated and you will need to go a different route.

|improve this answer
  • Thanks for the information Adrian. Yes these are a new feature called Related Contacts (at least that's what I think they're called) where one Contact can be associated with multiple Account's. It's not clear to me how you would get all the contacts in this case. As i mentioned in my comment above the puzzling thing is the raw query works in the Developer console, it just doesn't work when I'm using Apex code. – earthtrip Jun 28 '16 at 19:41

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.