4

I have a requirement that I need to display all Account Team Members for a specific Account in a text field for reporting. using this post as a guide, I can a list of the results but I cannot figure out how to takes the result and convert it to a string that I can put into the field. I started working in the dev console so I can test out the syntax quickly. Example:

Set<ID> accId = new Set<Id>();
accId.add('001A000001LFBFT');
String dan =[SELECT User__r.FirstName,
                    User__r.LastName
             FROM NPD_Account_Team__c
             WHERE Account__c IN :accId
             ORDER BY User__r.FirstName DESC].User__r.FirstName;
System.debug('dan is:: ' + dan);

This compiles but when run it gives the error:

List has more than 1 row for assignment to SObject.

Ideally what I want to happen is that if I have Account Team members as: Tom Dick Harry

I would have a string that would say 'Dick, Harry, Tom'

0

3 Answers 3

7

When you have more that one potential record you cannot access the fields without providing an index. Doing it blindly as such is not advisable and will only get the first record anyway.

[SELECT User__r.FirstName,
                        User__r.LastName
                 FROM NPD_Account_Team__c
                 WHERE Account__c IN :accId
                 ORDER BY User__r.FirstName DESC][0].User__r.FirstName

You should iterate over the records and add them to the string as such:

Set<ID> accId = new Set<Id>();

//Populate accId with values

Map<Id,Set<String>> accountMap = New Map<Id,Set<String>>(); //Ensures no duplicates

for(NPD_Account_Team__c aTeam : [SELECT User__r.FirstName,
                    User__r.LastName
             FROM NPD_Account_Team__c
             WHERE Account__c IN :accId
             ORDER BY User__r.FirstName DESC]){

        Set<String> userSet = accountMap.get(aTeam.Account__c);
        if(userSet == null) userSet = New Set<String>();
        userSet.add(aTeam.User__r.FirstName);
        accountMap.put(aTeam.Account__c,userSet);
     }
    System.debug('usersString is:: ' + string.join(New List<String>(usersSet),','); //Need to convert to list before joining
}

Account[] accTBU = New Account[]{};

for(Id aId : accountMap.keySet()){
     accTBU.add(
         New Account(
                Id=aId,
                USERFIELD = string.join(
                                New List<String>(accountMap.get(aId)),
                                ','
                             )
     );
}

update accTBU;
9
  • Does the Set automatically detects duplicates or is needed by calling the contains() method? Feb 21, 2017 at 15:55
  • @MartinLezer - A set is unique values by definition. If you have ["A"] and do set.add("A") you will be left with ["A"]. the only drawback it you have to convert to a list before joining. Of course if the OP want "Fred,Fred,Fred" then don't use set
    – Eric
    Feb 21, 2017 at 15:56
  • 1
    Omg. I'm using the Set class wrong for 2 years.... I was thinking that the contains method was useful to verify that the Set doesn't contain a value before adding it... Feb 21, 2017 at 15:58
  • @MartinLezer - Map keys obviously work the same Map<String,String> having {"A" : "B"} and put("A", "C"); will be left with {"A" : "C"}.
    – Eric
    Feb 21, 2017 at 15:59
  • Yes now you say it, that's obvious. I knew it for the Map. But I never made the link with the keys used as a Set class. Feb 21, 2017 at 16:02
8

For this you will need to loop over the multiple records returned by the query. One way to do that is:

String[] names = new String[] {};
for (NPD_Account_Team__c t : [
        SELECT User__r.FirstName, User__r.LastName
        FROM NPD_Account_Team__c
        WHERE Account__c IN :accId
        ORDER BY User__r.FirstName DESC
        ]) {
    names.add(t.User__r.FirstName);
}

String s = String.join(names, ', ');
4
  • Nice solution ! :D Feb 21, 2017 at 15:49
  • @MartinLezer Looks like we agree...
    – Keith C
    Feb 21, 2017 at 15:50
  • I prefer our solution than @Eric's. :D Feb 21, 2017 at 15:51
  • @MartinLezer - Ha, I was in middle of editing, pasted placeholder code while I updated lol - Needed to change it to set to remove duplicates
    – Eric
    Feb 21, 2017 at 15:52
3

Here's the best solution I could think of:

Set<ID> accId = new Set<Id>();
accId.add('001A000001LFBFT');
List<String> accountNames = List<String>();
for(NPD_Account_Team__c account : [SELECT User__r.FirstName,User__r.LastName FROM NPD_Account_Team__c WHERE Account__c IN :accId ORDER BY User__r.FirstName DESC]){
   accountNames.add(account.User__r.FirstName);
}
System.debug(accountNames.join(','));

As you can see, first, you have to query all the accounts to store their first names in an array.

Then, you call the join function of the array.

You must log in to answer this question.

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