8

I need to loop through Accounts and then on all of the Contacts for those Accounts. I need to find what is the address with the most occurrences in Contacts for each Account and update the Account with that address. I can loop through Accounts and Contacts just fine but getting the address and finding the one that most Contacts have is getting tricky. This is my code so far:

public static void completeaddresses(){
    List<Account> acctList = new List<Account>();
    acctList = [SELECT Id, BillingState, BillingPostalCode, BillingCity,BillingStreet, BillingCountry, BillingAddress, (SELECT Id, contact.MailingAddress, contact.MailingStreet, contact.MailingCity, contact.MailingState, contact.MailingPostalCode, contact.MailingCountry FROM account.contacts WHERE contact.MailingStreet!='' OR contact.MailingCity!='' OR contact.MailingPostalCode!='' OR contact.MailingCountry!='' OR contact.MailingState!='') FROM Account WHERE BillingState ='' AND BillingPostalCode ='' AND BillingCity = '' AND BillingStreet = ''];

    Map<Address, Integer> addresses = new Map<Address, Integer>();
    List<Address> addressList = new List<Address>();

    for (Account acct:acctList) {
        for (Contact cont:acct.contacts) {
            if (!addresses.containsKey(cont.MailingAddress)) {
                addresses.put(cont.MailingAddress,0);
            }
            Integer currentCount = addresses.get(cont.MailingAddress) + 1;
            addresses.put(cont.MailingAddress, currentCount);
        }
        addressList.addAll(addresses.keySet());
        addressList.sort();
    }
}

The issue is, it sorts by key not by value, if I could sort by value, then I can update the account and I'd be done. I'd appreciate if someone can let me know if I can sort by value and how. Thanks.

14

Your best bet is to write a wrapper for this.

public class AddressWrapper implements Comparable {
    public Address address;
    public Integer counter;
    public AddressWrapper(Address address, Integer counter) {
        this.address = address;
        this.counter = counter;
    }
    public Integer compareTo(Object other) {
        return counter-((AddressWrapper)other).counter;
    }
}

Which you would then populate:

AddressWrapper[] addressList = new AddressWrapper[0];
for(Address key: addresses.keyset()) {
    addressList.add(new AddressWrapper(key, addresses.get(key)));
}
addressList.sort();
// addressList[0].address contains the most used address

I've edited the changes together and cleaned up the code; this code runs in execute anonymous:

public class AddressWrapper implements Comparable {
    public Address address;
    public Integer counter;
    public AddressWrapper(Address address, Integer counter) {
        this.address = address;
        this.counter = counter;
    }
    public Integer compareTo(Object other) {
        return counter-((AddressWrapper)other).counter;
    }
}

public static void completeaddresses() {
    Account[] acctList = [
        SELECT BillingState, BillingPostalCode, BillingCity, BillingStreet, BillingCountry, BillingAddress, 
            (SELECT MailingAddress, MailingStreet, MailingCity, MailingState, MailingPostalCode, MailingCountry 
             FROM account.contacts WHERE MailingStreet <> NULL OR MailingCity <> NULL OR
                  MailingPostalCode <> NULL OR MailingCountry <> NULL OR MailingState <> NULL)
        FROM Account
        WHERE BillingState <> NULL AND BillingPostalCode <> NULL AND BillingCity <> NULL AND BillingStreet <> NULL];

    for (Account acct:acctList) {
        Map<Address, Integer> addresses = new Map<Address, Integer>();
        for (Contact cont:acct.contacts) {
            if (!addresses.containsKey(cont.MailingAddress)) {
                addresses.put(cont.MailingAddress,0);
            }
            Integer currentCount = addresses.get(cont.MailingAddress) + 1;
            addresses.put(cont.MailingAddress, currentCount);
        }
        AddressWrapper[] addressList = new AddressWrapper[0];
        for(Address key: addresses.keyset()) {
            addressList.add(new AddressWrapper(key, addresses.get(key)));
        }
        addressList.sort();
        // addressList[0].address contains the most used address
    }
}
11
  • @sfdcfox - Would an aggregate query on contact grouped by accountid and address counting the Contact Ids be easier? Maybe you can't group by the compound field. hmm – Eric Jun 27 '17 at 6:21
  • @Eric Nope, can't group by address, but I suppose you could create a formula to return a text value you could group by? This code is probably about as close as one is going to get to doing this, and it's probably fast enough for most purposes. – sfdcfox Jun 27 '17 at 11:24
  • Always learn so much from you and your code. – Eric Jun 27 '17 at 14:09
  • @sfdcfox for some reason this code always grabs the first address it finds, I have an account with 5 contacts, 1 contact's address is empty, 2 have the same address, and the other 2 have random addresses, it always grabs the same one. I added more Contacts into the same account and all of them have the same address but this code doesn't grab the address with the most occurrences, not sure what I'm doing wrong – The Ghost Jun 27 '17 at 15:27
  • I have 10 contacts with the same address and they all have a counter = 1 – The Ghost Jun 27 '17 at 15:38

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.