[SalesForce] List query results into a string

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'

Best Answer

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;
Related Topic