+ Start a Discussion
renuamirenuami 

Get count for each query in for loop

Trigger Newbie question

 

The below scenario is working for one record...Please Advise for mass updates scenario.

 

What i am trying to accomplish is,Under Standard Account Object,  Travel is the Custom Object. Intl_Travel_account__c is the look up for Account in Travel Custom Object.

 

For one Account there can be more than 1 Travel records.

Below logic is written for AfterInsert method. But this is working for only one record.

 

There will be scenarios where  Intl_Travel_account__c field on travel Object will be updated with some other Account value via data loader. Below will be the information i will be updating via data loader.

 

When updating, the code should check to see if there are any Travel records with status__c=true under that Account,  if there are any then Intl_Travelled__c field on Account should be Updated.

 

Travel__c.id

Intl_travelAccount__c

0001

ABC1

0002

ABC2

0003

ABC3

0004

ABC4

 

 

 

 The issue with the below code is, for all the Travel records, Intl_Travel_Account__cfield is updating with the last value (ABC4) in the CSV file above.

public static void AfterInsertMethod(Travel[] tt) { List<Account> TravelAcctList = new List<Account>(); Account travelAcct; for( Travel__c t: tt) { travelAcct =new Account(Id=t.Intl_Travel_account__c); } Integer tcount=[select count() from Travel__c where Intl_Travel_account__c=:travelAcct.Id and t.status__c=true limit 10]; if(tcount>0) { travelAcct.Intl_Travelled__c='Travelled'; } else { travelAcct.Intl_Travelled__c=''; } TravelAcctList.add(travelAcct); update TravelAcctList ; }

 

 Please advise...Thanks in advance

renuamisha

 

Best Answer chosen by Admin (Salesforce Developers) 
BritishBoyinDCBritishBoyinDC
You can try putting something there, but before doing that, try using travel__r rather than travels__r - if the object is travel__c, there shouldn't be an extra s when you reference the relationship.

All Answers

BritishBoyinDCBritishBoyinDC

I've suggested a fix below, but this code will fail after 20 records, because you have a SOQL query in the for loop, and you are limited to 20 SQOL queries per trigger.  To make it bulk safe, you'd need to put all the data in a map, and look at the list size of  the child Travel object for each account to update the account.

 

However, if I understand your code correctly, you could accomplish the same thing with a rollup summary (count where status = true) and then a formula field on the account that = true when count of rollup summary field > 0 

 

Anyway, from a simple code point of view, I think your trigger needs to look like the update below (you were only ever querying for an account once in the for loop and you had the update call inside the for loop - so it only updated the final account in the loop)

 

 

public static void AfterInsertMethod(Travel[] tt){ List<Account> TravelAcctList = new List<Account>(); Account travelAcct; for( Travel__c t: tt) { travelAcct =new Account(Id=t.Intl_Travel_account__c); Integer tcount=[select count() from Travel__c where Intl_Travel_account__c=:travelAcct.Id and t.status__c=true limit 10]; if(tcount>0) { travelAcct.Intl_Travelled__c='Travelled'; } else { travelAcct.Intl_Travelled__c=''; } TravelAcctList.add(travelAcct); }//This has to be executed outside the for loop... update TravelAcctList ;}

 

 

 

renuamirenuami

Thank you for the response.

 

I did already try the logic you posted and as you mention it failed for mass updates.

 

I am also trying to use Map<>. But i am not at all moving forward with any idea of how to use Maps. I did go throuh the documention

 

Can you please give me some idea to start with Maps?

 

Thanks a bunch!!!!

BritishBoyinDCBritishBoyinDC

This post is doing almost the same thing as you need to do. 

 

You'll see how it puts all the accounts in a set, and then uses a single SOQL query to query for Accounts and the Child Object, and then loop through each account returned from the list of child obejcts being updated and assigns the child object results to a list. In your case, if you limit the child query to only t.status__c=true then you can just a list.size() check on the list, and assign the value to the account as required.

dmchengdmcheng
See this link for some Apex supplemental documentation in wiki format:  http://wiki.developerforce.com/index.php/Apex_Supplemental_Documentation
renuamirenuami

Thanks for both of you. Now i understand quite a bit.

 

I am getting the below error 

 

Didn't understand relationship 'Travels__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. 

 

I searched the documentation and boards and everyone are telling that, we need to use object plural name and append __r to it. Thats what i did too.....

 

Please Advise!!!Thanks

 

public static void AfterInsertMethod(Travel[] tt)
{
Set<String> AccountIDs = new Set<String>();
List<Account> updAccounts = new List<Account>();

for(Travel__c tl: trigger.new)
{
if(!AccountIDs.contains(tl.Intl_Travel_account__c))
AccountIDs.add(tl.Intl_Travel_account__c);
}

Account[] Accounts = new List<Account>();
Accounts = [SELECT Id,Intl_Travelled__c, (SELECT Id, Intl_Travel_account__c FROM Travels__r WHERE Status__c= true ) FROM Account WHERE Id IN :AccountIDs];

Map<Id, Travel__c []> AcctLics = new Map<Id, Travel__c[]>();

for (Account a :Accounts)
{
if(a.size()>0)
AcctLics.put(a.Id, a.Travels__r);
}

Account[] acts = new List<Account>();

for (Account a :Accounts)
{
acts = AcctLics.get(a.Id);
a.Intl_Travelled__c='Travelled';
updAccounts.add(a);
}
update updAccounts;
}

 

 

 

renuamirenuami
Am i on the right track???? Please Advise
dmchengdmcheng
Did you try a.Travel__c instead of __r?
BritishBoyinDCBritishBoyinDC

Are you using Eclipse? If so, use the schema generator to check that you are using the right relationship name...also check what the relationship name is set on the lookup field to Account  on the custom Travel__c .

 

 Re your code, this looks wrong:

  

Account[] acts = new List<Account>();

for (Account a :Accounts)
{
acts = AcctLics.get(a.Id);
a.Intl_Travelled__c='Travelled';
updAccounts.add(a);
}

 

 It should be building a list of the child objects (look carefully at the post linked to above) 

 

 

Map <Id, Travel__c []> AcctLics = new Map<Id, Travel__c[]>(); for (Account a :Accounts) { AcctLics.put(a.Id, a.Travels__r); }Travel__c[] tacts = new List<Travel__c>(); for (Account a :Accounts) { tacts = AcctLics.get(a.Id);if (tacts.size()> 0) { a.Intl_Travelled__c='Travelled'; updAccounts.add(a);}} update updAccounts;}

 

 

 

 

 

 

renuamirenuami

Yes i did try with a.Travel__c. BUt the error i am getting is in the query line.

 

I am using salesforce UI not eclipse.

 

Here are the details of lookup field for Account on Travel__c

 

Data type: LookUp

Field Label: International Travel Account

Field Name: Intl_Travel_Account

Related To: Account

Child Relationship Name : (empty nothing is defined) Do i need to give name here?

Related List Label: Travel (International)

 

public static void AfterInsertMethod(Travel[] tt) { Set<String> AccountIDs = new Set<String>(); List<Account> updAccounts = new List<Account>(); for(Travel__c tl: trigger.new) { if(!AccountIDs.contains(tl.Intl_Travel_account__c)) AccountIDs.add(tl.Intl_Travel_account__c); } Account[] Accounts = new List<Account>(); Accounts = [SELECT Id,Intl_Travelled__c, (SELECT Id, Intl_Travel_account__c FROM Travels__r WHERE Status__c= true ) FROM Account WHERE Id IN :AccountIDs]; Map <Id, Travel__c []> AcctLics = new Map<Id, Travel__c[]>(); for (Account a :Accounts) { AcctLics.put(a.Id, a.Travels__r); } Travel__c[] tacts = new List<Travel__c>(); for (Account a :Accounts) { tacts = AcctLics.get(a.Id); if (tacts.size()> 0) { a.Intl_Travelled__c='Travelled'; updAccounts.add(a); } } update updAccounts; }

 

 

 

BritishBoyinDCBritishBoyinDC
You can try putting something there, but before doing that, try using travel__r rather than travels__r - if the object is travel__c, there shouldn't be an extra s when you reference the relationship.
This was selected as the best answer
renuamirenuami

Hello BritishBoyinDC

 

Thanks for all the help on this. All the credit goes to you.

 

 keyed in Travel__r in the child relationship name  on lookup field. That resolved the issue.

 

With your help now i got some idea on how to use Maps.I looked at some of your other posts the explanation you gave helped me in understanding Maps. For all my Apex coding i will try using Maps

 

Thanks again!!! :smileyhappy:

ciao

BritishBoyinDCBritishBoyinDC
No problem - I find I use Maps more than anything else to ensure my triggers are 'bulk safe', so happy to help.