Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Hi i have a trigger in my custom object and it hit governor limit due to select statment in my for loop. now i would like to move my statement out. can anyone help? here is my trigger:

trigger CalculateMorgage on Property__c (before insert) 
{
    for(Property__c pp: Trigger.new)
    {       
        List <House_Type__c> ht = [SELECT ID, Property_Type__c  FROM House_Type__c WHERE Range__c =: pp.range__c and Quantity__c =: pp.p_quantity__c];

        if(!ht.isempty())
        {
            for(House_Type__c htt : ht)
            {
                pp.type__c = htt.Property_Type__c ;
            }        
        }
    }
}

in my House_Type__c object it is something like this:

Range__c    Quantity__c     Property_Type__c
R_10            10              AC01
R_10            50              AC02
R_10            500             AC03

so if my quantity is 25 it will return AC02 & AC03 but i need only AC02

share|improve this question
    
What are the field types for Range__c and Quantity__c? –  Bachovski Jul 16 at 2:11
    
Range__c is text while Quantity__c is number –  user2074703 Jul 16 at 2:12
    
And what happens if there are multiple house types found for the same property? Which one do you apply? –  Bachovski Jul 16 at 2:14
    
basically it wont return multiple house type. with my 'Range__c =: pp.range__c and Quantity__c =: pp.p_quantity__c' it will always return 1 result only. i hit limit is because i do data import –  user2074703 Jul 16 at 2:16
add comment

1 Answer 1

See below the bulkified version of your trigger. Not sure if it's going to work as I typed it on the fly and never compiled but you can fix it up and get the idea based on my comments:

trigger CalculateMorgage on Property__c (before insert) 
{
    Set <String> ranges = new Set <String> (); // Set of all ranges from every property in this trigger
    Set <Integer> quantities = new Set <Integer> (); // Set of all quantities from every property in this trigger
    Map <String, Property__c> rangeQuantityProperty = new Map <String, Property__c> (); // Map of concatenated string of range + quantity for the property as a key, and the actual property record as a value

    for(Property__c pp : Trigger.new)
    {
        // For each property in this trigger populate the sets of ranges and quantities and also map the property record to it's Range + Quantity string
        ranges.add(pp.Range__c);
        quantities.add(pp.P_Quantity__c);
        rangeQuantityProperty.put(pp.Range__c + pp.P_Quantity__c, pp);
    }

    // Get all house types in the system that have range that is in the set of ranges above and quantity that is in the set of quantities above
    for (House_Type__c houseType : [SELECT ID, Property_Type__c, Range__c, Quantity__c FROM House_Type__c WHERE Range__c IN :ranges and Quantity__c IN :quantities])
    {
        // Generate concatenated string for the house type range + quantity
        String rangeQuantity = houseType.Range__c + houseType.Quantity__c;

        // Check if we have this range + quantity in the map as a key (if there is a matching property record with the same values)
        if (rangeQuantityProperty.get(rangeQuantity) != null)
        {
            // if there is, then get that record from the map and set the Type__c to this current house type property type
            rangeQuantityProperty.get(rangeQuantity).Type__c = houseType.Property_Type__c;
        }
    }
}
share|improve this answer
    
thanks Bachovski! i will give it a try! –  user2074703 Jul 16 at 2:28
    
Now that's an excellent trigger. For similar scenarios to the OP, (two fields uniquely identify an object), I've found creating a formula field can simplify the logic, or alternatively a text field that's set in a before trigger for that object, House_Type__c in this instance. –  Ralph Jul 16 at 4:03
    
Yeh good point Ralph. I'd do the formula field too in this case! –  Bachovski Jul 16 at 4:05
    
hi, we have modify our logic..now that the select statement will return more than 1 result. so i have change my select statement to > '[SELECT ID, Property_Type__c, Range__c, Quantity__c FROM House_Type__c WHERE Range__c IN :ranges and Quantity__c >= :quantities order by Property_Type__c Limit 1]' and now my map wouldnt work because the keyset is different. any idea? –  user2074703 Jul 16 at 4:12
    
You can't limit the number of SOQL rows to 1 because now you're getting ALL targeted House Types. If you have more than 1 record in the system with the same Range + Quantity, then it's bad data modelling issue, you'll need to revisit that and maybe model it differently or add another 3rd parameter that will make those combinations unique so that you always have 1 key-value pair. –  Bachovski Jul 16 at 4:20
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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