You need to sign in to do that
Don't have an account?
Too many SOQL queries: 101 - No queries in For loop
I modified an existing trigger on assets to also update the account team if a custom field on the asset has a value of 1. The trigger works as expected when I test it in the Sandbox however, it fails with too many SOQL queries when I execute the Test Class. The line it's failing on isn't inside of a for loop and should only be returning one record. Can someone please explain what I've done wrong?
The trigger is failing on this line:
// get the assignment group id for the VersaDoc Project Managers group
list<Assignment_Group_Name__c> asgnGroupNameIDs = [SELECT Id FROM Assignment_Group_Name__c WHERE Name = 'Support - VersaDoc Project Managers' limit 1];
Here's my trigger:
trigger trgAsset on Asset (after insert, after update, after delete) { if (trigger.isInsert || trigger.isUpdate){ /*Trigger will update the Client_Since__c field on the account with the first purchase date for all assets. If the Asset has VersaDoc and the account doesn't have a VersaDoc Project Account Team Member, it will assign the next VersaDoc Project manager from the assignment groups to the Account Team.*/ // create a set of all the unique accountIds Set<id> accountIds= new Set<id>(); Set<id> accountTeamAccountIds = new Set<id>(); // set of accountIDs that have VersaDoc in the asset for (Asset a : Trigger.new){ accountIds.add(a.AccountId); if (a.HasVersadoc__c == 1 || a.HasVersadocStudio__c == 1 ) { accountTeamAccountIds.add(a.AccountId); } } // create map of accountids and minimum purchase date map<ID, Date> acctSinceDate = new map<ID, Date>(); AggregateResult[] groupedResults = [Select AccountId, MIN(PurchaseDate) MinPurchaseDate From Asset where AccountID in :accountIds GROUP BY AccountId]; for (AggregateResult ar : groupedResults) { acctSinceDate.put(String.valueof(ar.get('AccountId')), Date.valueof(ar.get('MinPurchaseDate')) ); } // create a map of accounts to update map<String, Account> accts = new map<String, Account>([Select Id, Client_Since__c from Account Where Id in :acctSinceDate.keySet()]); // update Account ClientSince field with MinimumPurchaseDate value //iterate over the list of accounts and assign the ClientSince date for (Account acct : accts.values()){ acct.Client_Since__c = acctSinceDate.get(acct.Id); } if (accts.size()>0) update(accts.values()); // get the assignment group id for the VersaDoc Project Managers group list<Assignment_Group_Name__c> asgnGroupNameIDs = [SELECT Id FROM Assignment_Group_Name__c WHERE Name = 'Support - VersaDoc Project Managers' limit 1]; Assignment_Groups__c[] asgnGroups = new List<Assignment_Groups__c>([SELECT Group_Name__c, User__c, Last_Assignment__c, Millisecond__c FROM Assignment_Groups__c WHERE Group_Name__c in :asgnGroupNameIds AND Active__c = 'True' AND User_Active__c = 'True' ORDER BY Last_Assignment__c, Millisecond__c] ); Integer groupCount = asgnGroups.size(); if (asgnGroups.isEmpty()) return; //loop through list of accounts and get the accounts w/o VersaDoc Project Manager AccountTeamMember[] NewMembers = new AccountTeamMember[]{}; //list of new team members to add AccountShare[] newShare = new AccountShare[]{}; //list of new shares to add Map<id, Account> acctsToUpdate = new Map<id, Account>([Select a.Id, (Select Id, AccountId From AccountTeamMembers WHERE TeamMemberRole = 'VersaDoc Project Manager' limit 1) From Account a Where a.Id in :accountTeamAccountIds]); Integer cnt = 0; for (Account a : acctsToUpdate.values()){ if (a.AccountTeamMembers == null || a.AccountTeamMembers.isEmpty()) { AccountTeamMember TeamMemberAd=new AccountTeamMember(); TeamMemberAd.AccountId=a.id; TeamMemberAd.UserId=asgnGroups[cnt].User__c; TeamMemberAd.TeamMemberRole = 'VersaDoc Project Manager'; NewMembers.add(TeamMemberAd); datetime now = datetime.now(); asgnGroups.get(cnt).Last_Assignment__c=now; asgnGroups.get(cnt).Millisecond__c = now.millisecondGMT(); cnt ++; if (cnt == groupCount){ cnt = 0;} } } //insert any valid members then add their share entry if they were successfully added Database.SaveResult[] lsr = Database.insert(NewMembers,false); Integer newcnt=0; for(Database.SaveResult sr:lsr){ if(!sr.isSuccess()){ Database.Error emsg = sr.getErrors()[0]; system.debug('\n\nERROR ADDING TEAM MEMBER:'+emsg); }else{ newShare.add(new AccountShare(UserOrGroupId=NewMembers[newcnt].UserId, AccountId=NewMembers[newcnt].Accountid, AccountAccessLevel='Edit',OpportunityAccessLevel='Edit')); } newcnt++; } //insert the new shares Database.SaveResult[] lsr0 =Database.insert(newShare,false); Integer newcnt0=0; for(Database.SaveResult sr0:lsr0){ if(!sr0.isSuccess()){ Database.Error emsg0=sr0.getErrors()[0]; system.debug('\n\nERROR ADDING SHARING:'+newShare[newcnt0]+'::'+emsg0); } newcnt0++; } // update assignment groups with their LastAssignmentDate update asgnGroups; } else if (trigger.isDelete){ // create a set of all the unique accountIds Set<id> accountIds = new Set<id>(); for (Asset a : Trigger.old) accountIds.add(a.AccountId); // create map of accountids and minimum purchase date map<ID, Date> acctSinceDate = new map<ID, Date>(); AggregateResult[] groupedResults = [Select AccountId, MIN(PurchaseDate) MinPurchaseDate From Asset where AccountID in :accountIds GROUP BY AccountId]; for (AggregateResult ar : groupedResults) { acctSinceDate.put(String.valueof(ar.get('AccountId')), Date.valueof(ar.get('MinPurchaseDate')) ); } // create a map of accounts to update map<string, Account> accts = new map <string, Account>([Select Id, Client_Since__c from Account Where Id in :accountIds]); for (Account acct : accts.values()){ // if there's a value in the acctSinceDate map, update the Client_Since__c date to that date, otherwise set to null. if(acctSinceDate.ContainsKey(acct.Id)){ acct.Client_Since__c = acctSinceDate.get(acct.Id); }else { acct.Client_Since__c = null; } } update(accts.values()); } }
Here's my test class:
@isTest private class testTrgAsset { static testMethod void myUnitTest() { // create user to run the test as Profile p = [select Id from profile where name = 'Standard User']; UserRole r = [select Id from userrole where name = 'Sales Assistant']; User u = new User(lastname='testing', alias='test123', email='testing123@noemail.com', username='testing123@noemail.com', profileid = p.Id, userroleid = r.id, emailencodingkey='utf-8', Office_Code__c='US0000', LanguageLocaleKey='en_US', localesidkey='en_US', timezonesidkey='America/Los_Angeles'); insert u; System.runAs(u) { // Switch to the runtime Test.StartTest(); performCreateAssignmentGroups(u.Id, p.Id, r.id); performCreateAccounts(u.Id); performCreateAssets(u.Id); performCreateAdditionalAssets(u.Id); performUpdateOriginalPurchaseDate(u.Id); performDeleteAssets(u.Id); performCreateVersaDocAssets(u.Id); Test.StopTest(); } } public static void performCreateAssignmentGroups(string createdby, string profileid, string userrole){ integer i=0; List<User> users = new list<User>(); for (i=0; i<3; i++){ User u = new User( lastname = 'testing'+i, alias='test'+i, email='testing'+i+'@noemail.com', username='testing'+i+'@noemail.com', emailencodingkey='utf-8', Office_Code__c = 'US000', profileid = profileid, userroleid = userrole, LanguageLocaleKey='en_US', localesidkey='en_US', timezonesidkey='America/Los_Angeles', IsActive=true); users.add(u); } insert users; //create an assignment group name Assignment_Group_Name__c agn = new Assignment_Group_Name__c(Name = 'Support - VersaDoc Project Managers'); insert agn; Assignment_Groups__c[] NewMembers = new Assignment_Groups__c[]{}; //list of new assignmentgroup members to add for (User u : users){ datetime now = datetime.now(); Assignment_Groups__c a = new Assignment_Groups__c( Group_Name__c=agn.Id, User__c = u.Id, Active__c = 'true', Last_Assignment__c= now); NewMembers.add(a); } insert NewMembers; } public static void performCreateAccounts(string createdby){ list<Account> accts = new list<Account>(); User u = [select Id from User where Id =: createdby]; integer i = 0; // create 200 accounts for (i=0; i < 200; i++ ){ Account acc = new Account( Name='Testing Account ' +i, Industry='Printers', Type = 'Client', CreatedBy = u); accts.add(acc); } insert accts; } public static void performCreateAssets(string createdby){ list<Asset> assts = new list<Asset>(); list<Account> accts = new list<Account>([Select id from Account where CreatedById =: createdby]); Product2 prod = [select Id from Product2 where Name = 'PrinterPresence Silver Level']; date now = date.today(); // create an asset for each account for (Account acct : accts ){ Asset a = new Asset( Product2 = prod, Name = 'PrinterPresence Silver Level', AccountID = acct.Id, Status = 'Purchased', PurchaseDate = now, RMR__c = 160.00, Price = 2000.00); assts.add(a); } insert assts; //check results of accounts to be sure ClientSince date was updated to the PurchaseDate list <Account> accs = [Select Id, Client_Since__c from Account Where CreatedById =: createdby]; for (Account a : accs){ System.assertEquals(now, a.Client_Since__c); } } public static void performCreateAdditionalAssets(string createdby){ list<Asset> assts = new list<Asset>(); list<Account> accts = new list<Account>([Select id from Account where CreatedById =: createdby]); list <Asset> insertedAsst = [Select Id from Asset where CreatedbyId =: createdby]; Product2 prod = [select Id from Product2 where Name = 'PrinterPresence Gold Level']; date now = date.today(); //update previous asset to Attritioned-Upgrade for (Asset ass : insertedAsst){ ass.Status = 'Attritioned-Upgrade'; ass.UsageEndDate = System.today(); } update insertedAsst; // create an asset for each account for (Account acct : accts ){ Asset a = new Asset( Product2 = prod, Name = 'PrinterPresence Gold Level', AccountID = acct.Id, Status = 'Purchased-Upgrade', PurchaseDate = now, RMR__c = 210.00, Price = 500.00); assts.add(a); } insert assts; //check results of accounts to be sure ClientSince date was updated to the PurchaseDate list <Account> accs = [Select Id, Client_Since__c from Account Where CreatedById =: createdby]; for (Account a : accs){ System.assertEquals(now, a.Client_Since__c); } } public static void performUpdateOriginalPurchaseDate(string createdby){ date now = date.today(); // get a list of the original assets list <Asset> insertedAsst = [Select Id from Asset where CreatedbyId =: createdby and Status = 'Attritioned-Upgrade']; //update original asset purchase date for (Asset ass : insertedAsst){ ass.PurchaseDate = now; } update insertedAsst; //check results of accounts to be sure ClientSince date was updated to the new PurchaseDate list <Account> accs = [Select Id, Client_Since__c from Account Where CreatedById =: createdby]; for (Account a : accs){ System.assertEquals(now, a.Client_Since__c); } } public static void performDeleteAssets(string createdby){ list<Account> accts = new list<Account>(); User u = [select Id from User where Id =: createdby]; integer i = 0; // create 100 accounts for (i=0; i < 100; i++ ){ Account acc = new Account( Name='Testing Account ' +i, Industry='Nonprofit', Type = 'Client', CreatedBy = u); accts.add(acc); } insert accts; // insert one asset for each of the new accounts list<Asset> assts = new list<Asset>(); list<Account> insertedAccts = new list<Account>([Select id from Account where CreatedById =: createdby and Industry='Nonprofit']); Product2 prod = [select Id from Product2 where Name = 'Nonprofit Silver Level']; Product2 prod2 = [select Id from Product2 where Name = 'PrinterPresence Silver Level']; // create an asset for each account for (Account acct : accts ){ Asset a = new Asset( Product2 = prod, Name = 'Nonprofit Silver Level', AccountID = acct.Id, Status = 'Purchased', PurchaseDate = date.newinstance(2012, 01, 01), RMR__c = 110.00, Price = 3000.00); assts.add(a); } insert assts; // delete assets for all accounts list <Asset> AssetToDelete = [Select Id from Asset where CreatedbyId =: createdby and (Name = 'Nonprofit Silver Level' or name = 'PrinterPresence Silver Level')]; delete AssetToDelete; //check results of accounts to be sure ClientSince date was updated to empty for the newly created accounts list <Account> accs = [Select Id, Client_Since__c from Account Where CreatedById =: createdby and Industry='Nonprofit']; for (Account a : accs){ System.assertEquals(Null, a.Client_Since__c); } //check results of accounts to be sure ClientSince date was updated to the new PurchaseDate list <Account> accs1 = [Select Id, Client_Since__c from Account Where CreatedById =: createdby and Industry='Printers']; for (Account a1 : accs1){ System.assertEquals(System.today(), a1.Client_Since__c); } } public static void performCreateVersaDocAssets(string createdby){ // add PrinterPresence Platinum level Asset (Has VersaDoc) date now = date.today(); list<Account> accts = new list<Account>([Select id from Account where CreatedById =: createdby]); Product2 product = [Select p.Name, p.Id From Product2 p where p.name = 'PrinterPresence Platinum Level']; list<Asset> assets = new list<Asset>(); for(Account a : accts){ Asset ass = new Asset( Name=product.Name, Product2Id=product.Id, Status='Purchased', Price=7500.00, RMR__c=310.00, AccountId=a.Id, PurchaseDate=now); assets.add(ass); } insert assets; //check results of inserted accounts to be sure a VersaDoc Project Manager was assigned list <Account> accs = [Select Id, AccountNumber from Account Where CreatedById =: createdby and Type = 'Client' and Name like 'Testing Account%']; Map<id, Account> acctTeams = new map<id, Account>([Select a.Id, (Select Id, AccountId From AccountTeamMembers WHERE TeamMemberRole = 'VersaDoc Project Manager' limit 1) From Account a Where a.Id in :accts]); for (Account a : acctTeams.values()){ System.assertNotEquals(a.AccountTeamMembers, Null); } } }
Thanks!
Could be bacuase of sub Query In following statement. Not Quite sure but for every record sub Query could be considered as fired. You can actually debug this.
Map<id, Account> acctsToUpdate = new Map<id, Account>([Select a.Id, (Select Id, AccountId From AccountTeamMembers WHERE TeamMemberRole = 'VersaDoc Project Manager' limit 1) From Account a Where a.Id in :accountTeamAccountIds]);
mark this as answer if this helps you so that this can help others too.
Thanks,
Yoganand
As you are updating account, have you verified that any trigger written on account object.
It will also consider those queries.
How can I debug this to see where the queries are being generated? I do have a trigger on the accounts as well. How do you prevent the too many SOQL queries in general. I know you shouldn't put queries inside of for loops and I haven't done that. What other solutions are there?
Thanks for the help.
You can try to deactivate another trigger on the account object and then run this trigger to see what happens.
Let me know if you are able to find it out or not. Every account update will also call trigger on the account object.
Try inactivating triggers on account object.
if still your code is crossing governor limits in that case just check out the developer console to find out which method is getting called.
Thanks for the responses. I've put this project on hold for the week and will try again next week.