The following is a part of an AFTER trigger that I have written.
It works fine - on 1 record... but when you get a few more than 1 I am getting the Too Many SOQL Queries error.
The first select (lstFA) is on the parent object (Fixed_Asset__c)
The second select (lstHIST) is a child object of Fixed_Asset__c and I want to only return 0 or 1 records per Fixed_Asset__c - the last one of a certain type (or none at all for that Asset).
Then based on all of that in lstHIST there is a field Assigned_Key__c and depending on the value in that field I have to query either an Account, Contact, Opportunity, User or Fixed_Asset_Holder__c.
The 'for' in this trigger is a ways farther down - but there is no more SOQL after what I show below.
Can anyone assist me in re-working these queries so the trigger works for more than 1 update at a time?
if (trigger.isUpdate){
lstFA = [select Id, Name, AcctSol__Internal_Conversion__c, AcctSol__Transfer_Date__c, AcctSol__Assigned_Date__c,
AcctSol__Account__c,AcctSol__Assigned_Address__c,AcctSol__Assigned__c,AcctSol__Assigned_City__c,
AcctSol__Contact__c,AcctSol__Assigned_Country__c,AcctSol__DepartmentInfo__c,AcctSol__Assigned_Email__c,
AcctSol__Fixed_Asset_Holder__c,AcctSol__Assigned_Mobile__c,AcctSol__Assigned_Name__c,AcctSol__Opportunity__c,
AcctSol__Assigned_Other_Phone__c,AcctSol__Assigned_Phone__c,AcctSol__Assigned_State_Province__c,
AcctSol__Assigned_Postal_Code__c,
AcctSol__Loan_Account__c,AcctSol__Loan_Address__c,AcctSol__Loan_Start_Date__c,AcctSol__Loan_Type__c,
AcctSol__Loan_City__c,AcctSol__Loan_Contact__c,AcctSol__Loan_DepartmentInfo__c,AcctSol__Loan_Country__c,
AcctSol__Loan_Email__c,AcctSol__Loan_Estimated_Return_Date__c,AcctSol__Loan_Mobile__c,AcctSol__Loan_Name__c,
AcctSol__Loan_Opportunity__c,AcctSol__Loan_Other_Phone__c,AcctSol__Loan_Phone__c,AcctSol__Loan_State_Province__c,
AcctSol__Loan_User__c,AcctSol__Loan_Zip_Postal_Code__c,AcctSol__QR_Code__c,AcctSol__QR_Code_Asset_ID__c,
AcctSol__Remaining_Orginal_Cost__c,AcctSol__Original_Cost__c,AcctSol__Insurance_Policy_LU__c,
from AcctSol__Fixed_Asset__c where Id = :Trigger.New];
AcctSol__Fixed_Asset__c OLDFA = Trigger.OldMap.get(lstFA[0].Id);
if(lstFA[0].AcctSol__Internal_Conversion__c == '18U')
{
lstHIST =
[SELECT Id,AcctSol__Address__c,AcctSol__Assigned_Key__c,AcctSol__Assigned_Type__c,AcctSol__Current_Date__c,
AcctSol__Description__c,AcctSol__Email__c,AcctSol__Fixed_Asset__c,AcctSol__Loan_Date__c,AcctSol__Name1__c,
AcctSol__New_User__c,AcctSol__Phone_Number__c,AcctSol__User__c,AcctSol__Return_Date__c,AcctSol__Starting_Date__c
FROM AcctSol__History_Tracking_Fixed_Asset__c
WHERE AcctSol__Fixed_Asset__c=:Trigger.New and AcctSol__Loan_Date__c <> null
ORDER BY Name DESC LIMIT 1];
if(lstHIST.size() > 0)
{
if(lstHIST[0].AcctSol__Assigned_Key__c <> null)
{
if (lstHIST[0].AcctSol__Assigned_Type__c == 'Account')
{
lstAcc = [select id,Name,Phone,BillingCity,BillingCountry ,BillingPostalCode,BillingState,
BillingStreet,Department__c from Account where Account.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
}
else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Contact')
{
lstCon = [select id,Name,Phone,Email,OtherPhone,MobilePhone,Department,MailingCity,MailingCountry,
MailingPostalCode,MailingState,MailingStreet
from Contact where Contact.Name = :lstHIST[0].AcctSol__Assigned_Key__c];
}
else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Asset Holder')
{
lstFAH = [select id,Name,Phone__c,Email__c,Other_Phone__c,Mobile__c,Department__c,City__c,Country__c,
Postal_Code__c,State_Province__c,Address__c
from AcctSol__Fixed_Asset_Holder__c where AcctSol__Fixed_Asset_Holder__c.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
}
else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Opportunity')
{
lstOpp = [select id,Name from Opportunity
where Opportunity.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
}
else if (lstHIST[0].AcctSol__Assigned_Type__c == 'User')
lstUsr = [select id,Name,MobilePhone,Email,Phone,Street,State,PostalCode,Country,City,
Department from User where User.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
}
}
Attribution to: AngiB
Possible Suggestion/Solution #1
List<Id> assignedKeys = new List<Id>{};
Map<Id, Account> assignedObjects = new Map<Id, sObject>{};
if (trigger.isUpdate){
for(Fixed_Asset__c lstFA : trigger.new){
//collect all the assigned keys in a list
if(Trigger.OldMap.get(lstFA[0].Id).AcctSol__Internal_Conversion__c == '18U')
assignedKeys.add(lstFA.AcctSol__Assigned_Type__c);
//select all the accounts which have name in the assigned keys collection above
for(Account acc : [select id,Name,Phone,BillingCity,BillingCountry ,BillingPostalCode,BillingState,BillingStreet,Department__c from Account where Account.Name IN :assignedKeys])
assignedObjects.put(acc.Name, acc);
for (Contact con : [select id, Name, Phone, Email, OtherPhone, MobilePhone, Department,MailingCity,MailingCountry,MailingPostalCode,MailingState,MailingStreet from Contact where Contact.Name IN :lstHIST[0].AcctSol__Assigned_Key__c])
assignedObjects.put(con.Name, con);
... ditto for Opportunities and Asset Holders
}
//Now assignedObjects contains a Map of the key and Object (Acc, Con, ...)
To access Account, just type cast to account
lstAcc = (Account)assignedObjects.get(Key);
Attribution to: techtrekker
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/1921