We have a scenario of accounts who have contacts where some have one or more records in a custom object named members__c but not all contacts have a member__c record. member__c object has a lookup relationship with the contact object.

How to select all contacts who have at least one member__c record and insert their first and last name in a field named "member__c" on the account object?

  • What have you tried so far in order to get your contacts and where are you having difficulty getting the related members__c info? Are you starting from Contact or from Account? Please show your code. – crmprogdev Jul 13 '16 at 13:43
  • This is my attempt but I get a syntax error. SELECT Account.Name,(SELECT Contact.Name (SELECT BOG_Member__c.Name FROM Member__c) FROM contacts) FROM Account WHERE Account.Name LIKE '%washington%' – Apex N-u-b Jul 13 '16 at 13:45
  • You can't have a subquery within a subquery, thus the syntax error. They need to be separated. – crmprogdev Jul 13 '16 at 13:47

Perhaps aggregate querying will help. Something like below.

List<AggregateResult> aggregates = [
        Contact__r.AccountId accountId,
        Contact__r.FirstName first,
        Contact__r.LastName last
    FROM Members__c WHERE Contact__r.AccountId = :accountid
    GROUP BY Contact__r.AccountId, Contact__r.FirstName, Contact__r.LastName

Map<Id, Account> parentRecords = new Map<Id, Account>();
for (AggregateResult aggregate : aggregates)
    Id parentId = (Id)aggregate.get('accountId');
    String firstName = (String)aggregate.get('first');
    String lastName = (String)aggregate.get('last');
    parentRecords.put(parentId, new Account(
        Id = parentId, Member__c = firstName + ' ' + lastName
update parentRecords.values();
  • 1
    I found my error. Nothing to do with you. It works great. Thanks Adrian. – Apex N-u-b Jul 13 '16 at 14:56
  • @adrianlarson do we really need an aggregate query for this case? – Praveen Jul 13 '16 at 16:27
  • Need? No it just seemed most efficient. I don't think you can do it with just one query otherwise. – Adrian Larson Jul 13 '16 at 17:32
  • @AdrianLarson Why not use List<Member__c> = [SELECT Contact__r.AccountId accountId, Contact__r.FirstName first, Contact__r.LastName last FROM Members__c WHERE Contact__r.AccountId = :accountid] and iterate over it to get the first name,last name of the contact and it's associated contact's accountid?. Just trying to understand the need of using aggregate query here. – Praveen Jul 13 '16 at 18:41

I'd expect your query to look something like the following:

SELECT Id, Name, Member__r, ContactId__r (SELECT Name, AccountId, Member__  
    FROM contacts), (SELECT Id, BOG_Member__c.Name, ContactId, FROM Member__r)
       FROM Account WHERE Account.Name LIKE '%washington%' 

From the above you should be able to create maps of Account.Id to ContactId and ContactId to Member__c.Id

  • My reading of the OP is that Member is a child of Contact and not Account. – Adrian Larson Jul 13 '16 at 14:46
  • @AdrianLarson. Yes, that was my understanding as well. Member is a grandchild of Account, but also related to Contact via the ContactId. It would seem the relationships above would provide the criteria needed to query it because of those two relationships. – crmprogdev Jul 13 '16 at 16:59
  • But then you try to subquery member from account. That's a different architecture. – Adrian Larson Jul 13 '16 at 17:31
  • @AdrianLarson True, but it also has a lookup to Account as well making it a related object with a different child dependency to Account. You might say there's a double bind I think is the correct term for it. – crmprogdev Jul 13 '16 at 18:32
  • Hmm not sure where you see that. Did I miss it in one of the comments? – Adrian Larson Jul 13 '16 at 18:33

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.