3

I have wrote a trigger that sets a value to a custom field WeightResult__c on a OpportunityLineItem record, each OpportunityLineItem has a Supplier, which is a lookup to a custom object Waste_Supplier__c object, this object stores the values I need for the WeightResult__c field.

This trigger works but it is not 'Bulkified' and causes issues with query limits when doing a mass update to products.

//Original Trigger
trigger GetBinWeight on OpportunityLineItem (before update) {    

for (OpportunityLineItem binWeight : Trigger.new) {

    string ProductID = binWeight.Product2Id;
    Product2 p = [SELECT ID, Weight_Field__c from Product2 where ID = :ProductID];

    if(p.Weight_Field__c == null){  
        binWeight.WeightResult__c = null;
    }

    else{
        string SupplierID = binWeight.Waste_Supplier__c;
        string WeightField = p.Weight_Field__c;

        if(SupplierID == null){

            binWeight.WeightResult__c = null;

            binWeight.Total_Weight_Per_Year__c = null;

        }
        Else{

            List<Waste_Supplier__c> wasteSuppList = Database.query('SELECT ' + WeightField + ' FROM Waste_Supplier__c WHERE Waste_Supplier__c.ID = :SupplierID limit 1');        

            if(! wasteSuppList.isEmpty() ){

                integer TheValue2 = integer.valueOf(wasteSuppList[0].get(WeightField));

                if(TheValue2 == null){

                    binWeight.WeightResult__c = null;

                    binWeight.Total_Weight_Per_Year__c = null;
                }

                ELSE{
                    binWeight.WeightResult__c = TheValue2;

                    binWeight.Total_Weight_Per_Year__c = (binWeight.WeightResult__c * binWeight.Total_Lifts_Per_Year__c);
                }
            }                

        }
    }
}

}

When bulkifying a trigger I have read that queries must be taken outside of loops which I have attempted to do below (and allows me to update many records at once) but am running into issues where it is using the same value for every product when updating the WeightResult__c field. Here is my attempt at bulkifying the above trigger:

//Attempt to Bulkify
trigger GetBinWeight on OpportunityLineItem (before update) {    

Set<id> productIds = new Set<id>();
Set<String> supIds = new Set<String>();

for (OpportunityLineItem binWeight : Trigger.new){ 

    productIds.add(binWeight.Product2Id);
    supIds.add(binWeight.Waste_Supplier_ID__c);

}

Product2 p = [SELECT ID, Weight_Field__c
              FROM Product2 
              WHERE ID in :productIds 
              LIMIT 1];

string WeightField = p.Weight_Field__c;

if(p.Weight_Field__c == null){

}
else
{ 

    List<Waste_Supplier__c> wasteSuppList = Database.query('SELECT ' + p.Weight_Field__c + ' FROM Waste_Supplier__c WHERE Waste_Supplier__c.ID IN :supIds limit 1');


    for (OpportunityLineItem binWeight : Trigger.new) {

        if(p.Weight_Field__c == null){
            binWeight.WeightResult__c = null;
        }
        else
        {

            string SupplierID = binWeight.Waste_Supplier__c;

            if(SupplierID == null){

                binWeight.WeightResult__c = null;

                binWeight.Total_Weight_Per_Year__c = null;

            }
            else
            {

                if(! wasteSuppList.isEmpty() ){

                    integer TheValue2 = integer.valueOf(wasteSuppList[0].get(WeightField));

                    if(TheValue2 == null){

                        binWeight.WeightResult__c = null;

                        binWeight.Total_Weight_Per_Year__c = null;
                    }
                    else
                    {

                        binWeight.WeightResult__c = TheValue2;

                        binWeight.Total_Weight_Per_Year__c = (binWeight.WeightResult__c * binWeight.Total_Lifts_Per_Year__c);
                    }
                }                
            }
        }
    }
}

}

I believe my problem lies in looping through the products from Product2 correctly. This is my first attempt at using Sets and one of the first triggers I have wrote so there are more than likely some silly errors that I am overlooking so any advice is greatly appreciated.

6

You are doing it wrong. First you collect the ids of products you have linked in Opportunitylineitems. Next you use those ids to get the products. Finally you can loop over your opportunitylineitems again and use the products you have queried instead of querying in your loop.

//Original Trigger
trigger GetBinWeight on OpportunityLineItem (before update) {    

    Set<Id> prodids = new Set<Id>();
    for (OpportunityLineItem binWeight : Trigger.new) {
        prodids.add(binWeight.Product2Id);
    }

    Map<Id, Product2> mapProducts = new Map<Id,Product2>([SELECT ID, Weight_Field__c from Product2 where ID IN :prodids]);

    for (OpportunityLineItem binWeight : Trigger.new) {
        Product2 p = mapProducts.get(binWeight.Product2Id);

        //your logic here
    }

}
  • @Neilsm That worked perfectly, thank you! I am now getting the correct values for each product but am facing one more issue. I have a list within the for loop that references Product2 p and I don't know of a way to reference this outside of the loop. List<Waste_Supplier__c> wasteSuppList = Database.query('SELECT ' + p.Weight_Field__c + ' FROM Waste_Supplier__c WHERE Waste_Supplier__c.ID IN :supIds limit 1'); The value for p.Weight_Field__c is dependent on the product and will vary for each record processed so it must be dynamic and it can't remain in the loop due to g limits. – Liam Mar 6 '15 at 11:15
  • Hi @Liam, I don't really understand what you are trying to achieve with that. Can you explain a little what you want from this? – Nielsm Mar 6 '15 at 12:16
  • Hi @Nielsm, I have a field called Weight_Field__c on Product2 which is a text field and contains the string value of a field on my Waste_Supplier__c object. For example, we have a product called 1100 Litre Bin (General Waste) and the value for Weight_Field__c on this product is X1100_generalwaste_weight_food__c shown here: i.imgur.com/bAdU4ja.png This means for an 1100 product the list query would read SELECT X1100_generalwaste_weight_food__c FROM Waste_Supplier__c WHERE Waste_Supplier__c.ID IN :supIds limit 1'); (Continued below...) – Liam Mar 6 '15 at 13:22
  • Similarly we have a product called 660 Litre Bin (General Waste) and the value for Weight_Field__c on this product is X660_generalwaste_weight_food__c which means for a 660 product the list query would read SELECT X660_generalwaste_weight_food__c FROM Waste_Supplier__c WHERE Waste_Supplier__c.ID IN :supIds limit 1'); – Liam Mar 6 '15 at 13:29
  • I don't see a reason to have field names like that. And I think that it is a very bad practice with regards to reporting etc... Can't you find a way to make your object/fieldnames more generic so you can reuse them? If so I think you can even built your app using simple formula's instead of a rather complex trigger. – Nielsm Mar 6 '15 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.