HELP Please - System.Exception: Too many SOQL queries: 101

Question

I have been struggling with this error for the past one month.

We had no issue with this code till a month ago but now it is creating a problem.

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;

                //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;
            }
    }
}

Can someone help me fix this code?

Answer 1

The trouble with your code is that you have 5 SOQL query inside the for loop You need to move these query outside the loop and prepare all needed data in a maps for further using inside the loop

you might use the following utility method. Before invocation this method you need to query all data which will be used inside the loop, after that you can split the result of this query to map and use it in the loop.

class CommonException extends Exception {}

public static Map<Id, List<sObject>> splitListByKey(List<sObject> sourceList, String key) {
    if (sourceList == null) {
        throw new CommonException('ERROR: splitListByKey(sourceList, key) got incorrect first parameter.');
    }
    if (String.isBlank(key)) {
        throw new CommonException('ERROR: splitListByKey(sourceList, key) got incorrect second parameter.');
    }
    Map<Id,List<sObject>> result = new Map<Id,List<sObject>>();
    List<sObject> tmpObjs;
    for (sObject obj: sourceList) {
        tmpObjs = new List<sObject>();
        if (obj.get(key) != null && result.containsKey((Id)obj.get(key))) {
            tmpObjs = result.get((Id)obj.get(key));
            tmpObjs.add(obj);
            result.put((Id)obj.get(key), tmpObjs);
        } else if (obj.get(key) != null) {
            tmpObjs.add(obj);
            result.put((Id)obj.get(key), tmpObjs);
        }
    }
    return result;
}