2

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?

3
  • 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
    Commented Jul 13, 2016 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
    Commented Jul 13, 2016 at 13:45
  • You can't have a subquery within a subquery, thus the syntax error. They need to be separated.
    – crmprogdev
    Commented Jul 13, 2016 at 13:47

2 Answers 2

2

Perhaps aggregate querying will help. Something like below.

List<AggregateResult> aggregates = [
    SELECT
        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();
4
  • 1
    I found my error. Nothing to do with you. It works great. Thanks Adrian.
    – Apex N-u-b
    Commented Jul 13, 2016 at 14:56
  • @adrianlarson do we really need an aggregate query for this case?
    – Praveen
    Commented Jul 13, 2016 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
    Commented Jul 13, 2016 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
    Commented Jul 13, 2016 at 18:41
1

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

8
  • My reading of the OP is that Member is a child of Contact and not Account.
    – Adrian Larson
    Commented Jul 13, 2016 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
    Commented Jul 13, 2016 at 16:59
  • But then you try to subquery member from account. That's a different architecture.
    – Adrian Larson
    Commented Jul 13, 2016 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
    Commented Jul 13, 2016 at 18:32
  • Hmm not sure where you see that. Did I miss it in one of the comments?
    – Adrian Larson
    Commented Jul 13, 2016 at 18:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .