Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
FinneyFinney 

HELP Please - Too many SOQL queries: 101

Hi,
 
I am in need of a help regarding a coding error. Can you please help me.
 This is the error System.Exception: Too many SOQL queries: 101
 
 I have been struggling with this error for the past one month. I have tried everything
 
 
Please help me.
 
Thanks and Kind Regards
 
Finny B
FinneyFinney
trigger CreatePayment on pb__Agreement__c (before update) {
  List<pb__Agreement__c> ags = new List<pb__Agreement__c>();
  List<ID> agIds =New List<ID> ();
  
  Date d = system.today();
    integer a = d.year();
    String currentyear = String.valueof(a);
  integer i=1;
  for (pb__Agreement__c  ag : trigger.new) {
      ags.add(ag); 
   agIds.add(ag.pb__inventoryId__c);
  }
 
    //update invventory stage/status
    List<pb__inventoryItem__c> inv = [select Agency_Management_Fee__c,Deposit_Amount__c,Tenancy_Expiration_Date__c,Tenancy_Start_Date__c, pb__ItemStatus__c, Property_Managed__c, ItemStage__c,PM_Agreement__c from pb__inventoryItem__c where Id IN :agIds LIMIT 1];
     for(pb__inventoryItem__c invv : inv)
       {
        for(pb__Agreement__c agg: ags)
    // creates the account payments, agent transactions and updates the relatvie invventories
    if((agg.management_contract_signed__c == true || agg.tenancy_contract_signed__c == true || agg.Stage__c == 'Agreement Signed and Passed to Conveyance') && agg.Payment_Created__c == false){
      
      
       pb__PaymentTransaction__c pay = new pb__PaymentTransaction__c();
      pay.pb__AgreementId__c = agg.id;
      if (agg.pb__AgreementType__c == 'Sale'){
        pay.Buyer_Agency_Fee__c = agg.Buyer_Agency_Fee__c;
        pay.Seller_Agency_Fee__c = agg.Seller_Agency_Fee__c;
        pay.Buyer_Conveyance_Fee__c = agg.Buyer_Conveyance_Fee__c;
        pay.Seller_Conveyance_Fee__c = agg.Seller_Conveyance_Fee__c;
        pay.Deposit_Amount__c = agg.Deposit_Amount__c;
 
        DateTime dt = agg.Transfer_Date__c;
        Date myDate = date.newinstance(dT.year(), dT.month(), dT.day());
        pay.pb__FixedDate__c = myDate;
        
        //update invventory status
        invv.ItemStage__c = 'Sold by Smith & Ken';
        invv.pb__ItemStatus__c = 'Sold by Smith & Ken';
        invv.pb__IsAvailable__c = false;
      }
      
      if (agg.pb__AgreementType__c == 'Lease'){
        pay.Landlord_Commision__c = agg.Landlord_Commision__c;
        pay.Tenant_Commission__c = agg.Tenant_Commission__c;
        pay.pb__FixedDate__c = agg.pb__AgreementDate__c;
        
        //update invventory Status
        if (invv.Property_Managed__c == true){
          invv.ItemStage__c = 'Rented & Managed by Smith & Ken';
          invv.pb__ItemStatus__c = 'Rented & Managed by Smith & Ken';
          invv.pb__IsAvailable__c = false;
        }
        if (invv.Property_Managed__c == false){
        invv.ItemStage__c = 'Rented by Smith & Ken';
        invv.pb__ItemStatus__c = 'Rented by Smith & Ken';
        invv.pb__IsAvailable__c = false;
      
        }
      }
      
      if (agg.pb__AgreementType__c == 'Property Management' && agg.is_pm_Agreement__c == 'Yes'){
        pay.pb__FixedDate__c = agg.pb__AgreementDate__c;
        invv.Property_Managed__c = true;
        invv.PM_Agreement__c = true;
        invv.Agency_Management_Fee__c = agg.Management_Fee__c;
        invv.Tenancy_Start_Date__c = agg.Tenancy_Start_Date__c;
        invv.Tenancy_Expiration_Date__c = agg.Occupied_Until__c;
        invv.Deposit_Amount__c = agg.Deposit_Amount__c;        
        if(agg.Tenant_Occupied__c == 'Yes'){
          invv.ItemStage__c = 'Rented & Managed by Smith & Ken';
          invv.pb__ItemStatus__c = 'Rented & Managed by Smith & Ken';
        }
        
        if (agg.Tenant_Occupied__c == 'No'){
          invv.ItemStage__c = 'Pending Paperworks from Owner';
          invv.pb__IsAvailable__c = false;
          invv.pb__ItemStatus__c = 'Pending Paperworks from Owner';
        }  
      }
    
      pay.Agent_1__c = agg.Agent_1__c;
      pay.Agent_2__c = agg.Agent_2__c;
      pay.Agent_3__c = agg.Agent_3__c;
      pay.Agent_4__c = agg.Agent_4__c;
      pay.Share_Agent3__c = agg.Share_Agent3__c;
      pay.Share_Agent1__c = agg.Share_Agent1__c;
      pay.Share_Agent2__c = agg.Share_Agent2__c;
      pay.Share_Agent4__c = agg.Share_Agent4__c;
      
      pay.pb__SequenceNumber__c = i;
      i++;
      agg.Payment_Created__c = true;
      
      //ag will get updated implicitly 
      //update ag;
      
      insert pay;
    
    
FinneyFinney
  //generate transactions in the system
      if (agg.Agent_1__c <> null){
        pb__BankTransaction__c tran = new pb__BankTransaction__c();
        tran.pb__PaymentId__c = pay.id;
        tran.pb__TransactionAmount__c = agg.Share_Agent1__c;
        tran.User__c = agg.Agent_1__c; 
        tran.pb__IsTransferred__c = true;
        insert tran;
        
        S_K_Transactions__c sk = new S_K_Transactions__c();
        sk.Agreement__c = agg.Id;
        sk.OwnerId = agg.Agent_1__c;
        sk.Amount__c = agg.Share_agent1__c;
        sk.Purchase_Price__c = agg.pb__PurchasePrice__c;
        sk.Annual_Rent__c = agg.pb__AnnualRent__c;
        sk.Deal_Closed__c = agg.pb__AgreementType__c;
        insert sk;
        
        //update anuual target achieved for agent
        List <My_Stats__c> m = [select achieved__c from My_Stats__c where
        ownerid = :agg.Agent_1__c and Year__c = :currentyear];
        if (m.size()>0){
          m[0].achieved__c += sk.Amount__c;
          update m[0];
          
          //update SKTransaction to link to MYStats
          sk.my_stats__c = m[0].id;
          update sk;
        }
        else{
          My_Stats__c mc = new My_Stats__c();
            mc.Name = agg.Agent_1_Name__c;
          mc.achieved__c = sk.Amount__c;
          mc.Agent__c = sk.OwnerId;
          mc.OwnerId = sk.OwnerId;
          mc.type__c = sk.Deal_Closed__c;
          insert mc;
          
          //update SKTransaction to link to MYStats
          sk.my_stats__c = mc.id;
          update sk;
        }
      }
      
      if (agg.Agent_2__c <> null){
        pb__BankTransaction__c tran2 = new pb__BankTransaction__c();
        tran2.pb__PaymentId__c = pay.id;
        tran2.pb__TransactionAmount__c = agg.Share_Agent2__c;
        tran2.User__c = agg.Agent_2__c; 
        tran2.pb__IsTransferred__c = true;
        insert tran2;
        
        S_K_Transactions__c sk1 = new S_K_Transactions__c();
        sk1.Agreement__c = agg.Id;
        sk1.OwnerId = agg.Agent_2__c;
        sk1.Amount__c = agg.Share_agent2__c;
        sk1.Purchase_Price__c = agg.pb__PurchasePrice__c;
        sk1.Annual_Rent__c = agg.pb__AnnualRent__c;
        sk1.Deal_Closed__c = agg.pb__AgreementType__c;
        insert sk1;
        
        //update anuual target achieved for agent
        List <My_Stats__c> m1 = [select achieved__c from My_Stats__c where
        ownerid = :agg.Agent_2__c and Year__c = :currentyear];
        if (m1.size()>0){
          m1[0].achieved__c += sk1.Amount__c;
          update m1[0];
          //update SKTransaction to link to MYStats
          sk1.my_stats__c = m1[0].id;
          update sk1;
        }
        else{
          My_Stats__c mc1 = new My_Stats__c();
          mc1.Name = agg.Agent_2_Name__c;
          mc1.achieved__c = sk1.Amount__c;
          mc1.Agent__c = sk1.OwnerId;
          mc1.OwnerId = sk1.OwnerId;
          mc1.type__c = sk1.Deal_Closed__c;
          insert mc1;
          
          //update SKTransaction to link to MYStats
          sk1.my_stats__c = mc1.id;
          update sk1;
        }
      }
      
      if (agg.Agent_3__c <> null){
        pb__BankTransaction__c tran3 = new pb__BankTransaction__c();
        tran3.pb__PaymentId__c = pay.id;
        tran3.pb__TransactionAmount__c = agg.Share_Agent3__c;
        tran3.User__c = agg.Agent_3__c; 
        tran3.pb__IsTransferred__c = true;
        insert tran3;
        
        S_K_Transactions__c sk2 = new S_K_Transactions__c();
        sk2.Agreement__c = agg.Id;
        sk2.OwnerId = agg.Agent_3__c;
        sk2.Amount__c = agg.Share_agent3__c;
        sk2.Purchase_Price__c = agg.pb__PurchasePrice__c;
        sk2.Annual_Rent__c = agg.pb__AnnualRent__c;
        sk2.Deal_Closed__c = agg.pb__AgreementType__c;
        insert sk2;
        
        //update anuual target achieved for agent
        List <My_Stats__c> m2 = [select achieved__c from My_Stats__c where
        ownerid = :agg.Agent_3__c and Year__c = :currentyear];
        if (m2.size()>0){
          m2[0].achieved__c += sk2.Amount__c;
          update m2[0];
          //update SKTransaction to link to MYStats
          sk2.my_stats__c = m2[0].id;
          update sk2;
        }
        else{
          
          My_Stats__c mc2 = new My_Stats__c();
          mc2.Name = agg.Agent_3_Name__c;
          mc2.achieved__c = sk2.Amount__c;
          mc2.Agent__c = sk2.OwnerId;
          mc2.OwnerId = sk2.OwnerId;
          mc2.type__c = sk2.Deal_Closed__c;
          insert mc2;
          
          //update SKTransaction to link to MYStats
          sk2.my_stats__c = mc2.id;
          update sk2;
        }
      }
      
      if (agg.Agent_4__c <> null){
        pb__BankTransaction__c tran4 = new pb__BankTransaction__c();
        tran4.pb__PaymentId__c = pay.id;
        tran4.pb__TransactionAmount__c = agg.Share_Agent4__c;
        tran4.User__c = agg.Agent_4__c; 
        tran4.pb__IsTransferred__c = true;
        insert tran4;
        
        S_K_Transactions__c sk3 = new S_K_Transactions__c();
        sk3.Agreement__c = agg.Id;
        sk3.OwnerId = agg.Agent_4__c;
        sk3.Amount__c = agg.Share_agent4__c;
        sk3.Purchase_Price__c = agg.pb__PurchasePrice__c;
        sk3.Annual_Rent__c = agg.pb__AnnualRent__c;
        sk3.Deal_Closed__c = agg.pb__AgreementType__c;
        insert sk3;
        
        //update anuual target achieved for agent
        List <My_Stats__c> m3 = [select achieved__c from My_Stats__c where
        ownerid = :agg.Agent_4__c and Year__c = :currentyear];
        if (m3.size()>0){
          m3[0].achieved__c += sk3.Amount__c;
          update m3[0];
          //update SKTransaction to link to MYStats
          sk3.my_stats__c = m3[0].id;
          update sk3;
        }
        else{
          My_Stats__c mc3 = new My_Stats__c();
          mc3.Name = agg.Agent_4_Name__c;
          mc3.achieved__c = sk3.Amount__c;
          mc3.Agent__c = sk3.OwnerId;
          mc3.OwnerId = sk3.OwnerId;
          mc3.type__c = sk3.Deal_Closed__c;
          insert mc3;
          
          //update SKTransaction to link to MYStats
          sk3.my_stats__c = mc3.id;
          update sk3;
        }
      }
      
  
      //mark the offer signed agreement
      pb__Offer__c p = new pb__Offer__c();
      p = [Select id, pb__HasSignedAgreement__c, pb__Status__c from pb__Offer__c where id = :agg.pb__OfferId__c];
      p.pb__HasSignedAgreement__c = true;
      p.pb__Status__c = 'Agreement Signed';
      update p;
      
      //update invventory object
      update invv;
    }
  } 
 }
digamber.prasaddigamber.prasad

Hi,

 

Looks too long here, could you please send me your code to my email id digamberlucky@gmail.com
FinneyFinney

I have done it my friend.

 

Thanks

 

Finney