+ Start a Discussion
astroastro 

Sets datatype throwing a List has more than 1 row for assignment to SObject error

I have a trigger which occurs on update or insert on the account object. Upon deploying the trigger i kept hitting governer limits so I tried to utilize IN clauses for my soql queries instead of the = statements which I used to write.

 

In changing my code to be more efficient I am now getting an unexpected error from a Sets list.  I thought that Sets were to hvae only unique values in them? so when I query the Set in my soql statement how is it that I'm getting the error that it is returning more than one result?  Here are the pertenant pieces of my code if you need more let me know:

 

 

//Created SETS to hold unique values for recordTypeId's, AccountId's and parentId's

Set<Id> recTypeIds = new Set<Id>();

Set<Id> accIds = new Set<Id>();

Set<id> prntIds = new Set<id>();

 

//Iterate through trigger.new and assign accountId, parentId and

//recordType id to their respective SETS

for(Account acc : Trigger.new){

 

accIds.add(acc.id);

 

prntIds.add(acc.ParentId);

 

recTypeIds.add(acc.RecordTypeId);

}

 

then later on in the code I make an assignment from a SOQL query, this following line is where I get the error:

 

 

//New instance of RecordType to be used to populate the record type field

//on the customer lookup object

RecordType rType;

rType = [select name from RecordType where id IN :recTypeIds];

 

 The way I interprate the error is that there are duplicate RecordTypeId's in the recTypeId's data Set.  I'm not sure how this can be the case however?  In my first snippet of code, are dupes pushed into my recTypeIds set?  I thought it would be rejected or atleast throw me an error.

 

Thanks to all who view and respond. 

 

 

Message Edited by astro on 03-10-2009 05:05 PM
Message Edited by astro on 03-10-2009 05:21 PM
Best Answer chosen by Admin (Salesforce Developers) 
JimRaeJimRae

Interesting.  Well, you might consider making maps of each object based on it's id, and pulling the data that way instead.

 

 

//Created Sets and MAPS to hold unique values for recordTypeId's, AccountId's and parentId's Set<Id> recTypeIds = new Set<Id>(); Set<Id> accIds = new Set<Id>(); Set<id> prntIds = new Set<id>(); //Iterate through trigger.new and assign accountId, parentId and //recordType id to their respective Map for(Account acc : Trigger.new){ accIds.add(acc.id); prntIds.add(acc.ParentId); recTypeIds.add(acc.RecordTypeId); } //include any other needed fields in the select statement. Map<Id,RecordType> recTypes = new Map<Id,RecordType>([select id,name from Recordtype where id IN :recTypeIds]); Map<Id,Account> accts = new Map<Id,Account>([select id, name from account where id IN :accIds]); Map<Id,Account> prntaccts = new Map<Id,Account>(select id,name from account where id IN :prntIds]); //TODO rest of processing here, using the maps to get the data // i.e. to get an account name accts.get(trigger.new[i].id).name // to get the recordtype id, recTypes.get(trigger.new[i].id).id

 Hope that helps

 

All Answers

JonPJonP

The error is that a SELECT statement can return multiple values.  Try:

 

 

RecordType[] rTypes = [select name from RecordType where id IN :recTypeIds];

 

 

 

astroastro

Hi JonP thank's for responding!

 

I know that select statements can return more than one value and your correct that would fix my issue.  But I don't see how a SET, in the manner that I'm utilizing it would return more tahn one value?

 

there should only be one matching ID in the SET to be returned not several.

 

Hope that made things more clear 

JonPJonP

You are iterating through a list of Accounts and putting all their recordTypeIds into a Set.  Why would you expect the Set to contain only only recordTypeId?  That would only be true if all of your Accounts in Trigger.new had the same record type.

 

Have you inspected the the contents of your Set using debug statements?

 

 

astroastro

Maybe I don't understand how the sets work then?  I thought that they didn't allow duplicate values, so if several accounts used the same recordtypeId's, the set would reject the duplicate and still maintain it's unique integrity? Maybe this is not the case.

 

I can never get my debug statements to work under chrome or ffox or ie.  I haven't tried in a while though so I will do so.  And I don't expect the set to contain only one recordTypeId, I expect there to be 1 recordTypeId in the set for all unique recordTypeId's that exist throughout ALL accounts.  

 

so maybe I wasn't clear on what I was trying to do, I wanted to take all recordTypeId's from all accounts involved in this trigger, remove the duplicates and keep the unique id's to be used later.

 

I hope I've been clearer, if I'm going about this the wrong way any help in the right direction would be greatly appreciated.

 

Thank you for replying so quickliy 

Message Edited by astro on 03-10-2009 07:37 PM
astroastro

okay got my debugs working through chrome.  Indeed there are unique values within the recTypeIds Set variable.

 

So why would [select name from account where id in :recTypeIds] return more than one value!?

 

I am obviously missing something fundamental.  I thought the soql statement above would only pulll out the name from the account for which there was a matching id in recTYpeIds!?

 

I am very confused ................@_@ 

JimRaeJimRae

If you have accounts with more than one recordtype, your set would contain one unique version of each type.

For example, if your Account list looks like this:

 

Account 1 , Recordtype1

Account 2, recordtype1

Account 3, recordtype2

Account 4, recordtype3

 

your set would look like this: {recordtype1,recordtype2,recordtype3}

 

Can you describe your business scenario in more detail?

It appears you are trying to get an account name based on the record type, is there any other condition that would make that account name unique?

astroastro

Hi JimRae, 

 

thank you for contributing.  Okay I see what your saying about sets now, I thought that was how they were working and it seems to be working fine.

 

To give you more background on the business case:  Each account in this environment has an associated Customer Lookup custom object.  Everytime an account is created or updated a customer lookup custom object is created and several fields from the account are passed onto this customer lookup object (recordType being one of them).

 

This trigger fires on insert or update.  The first thing I do is pull out all unique id's, recordTypeIds and parentId's (another item used referenced in the customer lookup object).  Afterwards, I use these sets for everywhere in my code where I was making [select xyz from account where id = trigger.new[i].id]; or [select xyz from recordType where id = trigger.new[i].recordtypeId ] etc. calls... instead I'm using  seletct statements with the IN clause.

 

In all cases where I changed my older inefficient soql queries  to the new ones which use IN everything worked as expected.  Except for the one statement Rtype =  [select name from RecordType where id in :recTypeIds];

  

it returns more than one recordType name and I have no idea how that could be possible.  Also I should mention that I am returning the account, the parentId and the recordTypeId associated to each account involved in the trigger.

 

 I hope that outlined the problem a little clearer.  Again thank you for the help 

Message Edited by astro on 03-11-2009 06:02 AM
 *edit* I think i'm starting to see my problem the for loop i use to do all my work in after I create my sets is as follows:  
 for(integer i=0;i< trigger.new.size;i++){
customer lookup info = trigger.new[i].info; 
 
whenever I use a [select xyz from abc where id in :mySet];  How does it know which id to use? it's most likely returning the same amount of records back as my Set size.
 
so if my set contained the following results:
mySet = {id:1,id:2,id:3} and I went [select id from account where id in : mySet], it should return an array of 3 account id's.  Not sure why i don't get errors and how I'm getting the correct results from my other select queiries if this is how these statements work.
 Perhaps I should be using Maps instead?  I'm finding that documentation isn't very clear, or maybe i'm just dense :) 
Message Edited by astro on 03-11-2009 06:17 AM
JimRaeJimRae

Interesting.  Well, you might consider making maps of each object based on it's id, and pulling the data that way instead.

 

 

//Created Sets and MAPS to hold unique values for recordTypeId's, AccountId's and parentId's Set<Id> recTypeIds = new Set<Id>(); Set<Id> accIds = new Set<Id>(); Set<id> prntIds = new Set<id>(); //Iterate through trigger.new and assign accountId, parentId and //recordType id to their respective Map for(Account acc : Trigger.new){ accIds.add(acc.id); prntIds.add(acc.ParentId); recTypeIds.add(acc.RecordTypeId); } //include any other needed fields in the select statement. Map<Id,RecordType> recTypes = new Map<Id,RecordType>([select id,name from Recordtype where id IN :recTypeIds]); Map<Id,Account> accts = new Map<Id,Account>([select id, name from account where id IN :accIds]); Map<Id,Account> prntaccts = new Map<Id,Account>(select id,name from account where id IN :prntIds]); //TODO rest of processing here, using the maps to get the data // i.e. to get an account name accts.get(trigger.new[i].id).name // to get the recordtype id, recTypes.get(trigger.new[i].id).id

 Hope that helps

 

This was selected as the best answer
astroastro

Thank you JimRae that helped alot.  I'm using maps in conjunction with sets and I"m hoping that keeps me under the governer limits.

 

Is this how sf developers usually stay under governer limits for these types of situations?

 

Thank's again JimRae! 

JimRaeJimRae
No problem.  That is definitely what I do to try to stay under the governor limits.
ishuishu
Message Edited by ishu on 11-11-2009 02:14 AM
ishuishu

Hi Astro

 

can u share your code for above trigger i am f acing same problem with sets and not able to handle the maps to get my sql working.

 

if you dont want to post please mail me ishu_kumar2003@yahoo.com

 

thanks for any help..................