0

Below is my code snippet.Need to remove SOQL from for loop.

for(Invoice_CU__c inv:Trigger.new){
List<Exchange_Rate__c> abc = [SELECT Exchange_Rate_to_Euro__c FROM Exchange_Rate__c where Exchange_Rate_to_Euro__c<> NULL and Exchange_Rate_to_Euro__c<> 0 and Currency_ISO_Code__c=:inv.CurrencyIsoCode and Exchange_Rate_Start_Date__c<=:inv.Invoice_Date__c ORDER by Exchange_Rate_Start_Date__c DESC LIMIT 1];
inv.Currency_Conversion_Rate__c = abc[0].Exchange_Rate_to_Euro__c;
}
0

Try below code -

List<Exchange_Rate__c> abc = [SELECT Exchange_Rate_to_Euro__c 
                                   FROM Exchange_Rate__c 
                                   where Exchange_Rate_to_Euro__c<> NULL 
                                   and Exchange_Rate_to_Euro__c<> 0 
                                   ORDER by Exchange_Rate_Start_Date__c
                                 ];

Boolean limit1 = false;

for(Invoice_CU__c inv:Trigger.new){
    for(Exchange_Rate__c ExchangeRateRecord : abc ){
        if(limit1 == false && ExchangeRateRecord.Currency_ISO_Code__c == inv.Currency_ISO_Code__c && ExchangeRateRecord.Exchange_Rate_Start_Date__c <= inv.Invoice_Date__c){
                inv.Currency_Conversion_Rate__c = ExchangeRateRecord.Exchange_Rate_to_Euro__c;
                limit1 = true;
        }

    }

limit1 = false;
}
5

Your best bet to find the records you want will probably be by dynamic SOQL. However, full optimization will require that your Exchange_Rate__c table has both an Exchange_Rate_End_Date__c field, and that no values in the database overlap chronologically for the same currency.

From there, you can build your filters:

String[] filters = new String[0];
Time midnight = Time.newInstance(0, 0, 0, 0);

for(Invoice_CU__c record: Trigger.new) {
    DateTime recordDate = DateTime.newInstance(record.Invoice_Date__c, midnight);
    String dateAsString = recordDate.format('yyyy-MM-dd');
    filters.add('Exchange_Rate_Start_Date__c <= '+dateAsString +' AND '+
                'Exchange_Rate_End_Date__c >= '+dateAsString +' AND '+
                'Currency_ISO_Code__c = \''+record.CurrencyIsoCode+'\'');
    // Note: String.escapeSingleQuotes not necessary in prior filter because
    // it is a restricted picklist. Normally, you should use this method.
}

Next, you can use a special variant of the "DateRange" class that I use to find arbitrary dates within a range in a map:

class DateRangeWithIso {
    Date startDate, endDate;
    String isoCode;
    DateRangeWithIso(Date first, Date last, string code) {
        startDate = first;
        endDate = last;
        isoCode = code;
    }
    DateRangeWithIso(Date firstAndLast, string code) {
        startDate = endDate = firstAndLast;
        isoCode = code;
    }
    public Boolean equals(Object rangeObj) {
        DateRangeWithIso other = (DateRangeWithIso)rangeObj;
        return other.isoCode == isoCode && 
            startDate <= other.endDate && endDate >= other.startDate;
    }
    public Integer hashCode() {
        return 1;
    }
}

The next step is to populate a Map with your DateRangeWithIso:

Map<DateRangeWithIso, Exchange_Rate__c> exchangeRates = 
    new Map<DateRangeWithIso, Exchange_Rate__c>();

for(Exchange_Rate__c record:(Exchange_Rate__c[])Database.query(
    'SELECT Exchange_Rate_to_Euro__c, '+
    'Exchange_Rate_Start_Date__c, Exchange_Rate_End_Date__c, '+
    'CurrencyIsoCode FROM Exchange_Rate__c '+
    'WHERE ('+String.join(filters, ') OR (')+')'
    )) {
    exchangeRates.put(
        new DateRangeWithIso(
            record.Exchange_Rate_Start_Date__c, 
            record.Exchange_Rate_End_Date__c, 
            record.CurrencyIsoCode), 
        record);
}

Finally, you can look up the result value by going through your list again:

for(Invoice_CU__c record: Trigger.new) {
    Exchange_Rate__c exrate = exchangeRates.get(
        new DateRangeWithIso(record.Invoice_Date__c, record.CurrencyIsoCode));
    if(exrate != null) {
        record.Currency_Conversion_Rate__c = exrate.Exchange_Rate_to_Euro__c;
    }
}

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.