List query results into a string

Question

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'

Answer 1

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;

Answer 2

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, ', ');

Answer 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.