function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Alex KirbyAlex Kirby 

SOQL Limit & Maps?

Hi All,

I have been working in SF for over a year now and I am trying to tidy up some old code.

I am still unsure of mpas and how to use them, as it stands I have a matrix object that I query to return a value based on a key.

I've done this using SOQL and it works fine, I need to start reducing the number of queries I have used as I am hitting the limit regularly.
  
trigger Net_Mar_Ini on Net_Margin_Calculation__c (before insert) {
    
     string Index;
         
    for(Net_Margin_Calculation__c NM : Trigger.new){
        Index = nm.index__c;
    }  

    //Acquire:
    	list<Cost__c> LV1 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Acquire'];
    //Bad Debt:
    	list<Cost__c>  LV2 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Bad Debt'];
    //Debt Admin:    
   		list<Cost__c> LV3 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Debt Admin'];
    //Lose:
        list<Cost__c>  LV4 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Lose'];
    //Meter:
        list<Cost__c> LV5 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Meter'];
    //Other:
        list<Cost__c> LV6 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Other'];  
    //Register:
        list<Cost__c> LV7 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Register'];       
    //Renew:
        list<Cost__c> LV8 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Renew'];
    //Serve:
        list<Cost__c> LV9 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Serve'];
    //Support:
        list<Cost__c> LV10 = [SELECT VALUE__C FROM Cost__c WHERE Index__c = :Index + 'Support'];
        
    
    for(Net_Margin_Calculation__c NM : Trigger.new){
        
    //Acquire:
       NM.Acquire_MPAN__c = Costs.get(Index).Value__c;
        
        if(LV1.size() < 1){NM.Acquire_MPAN__c = 0; }else{NM.Acquire_MPAN__c = Double.valueOf(LV1[0].get('VALUE__C'));}
    //Bad Debt:     
        if(LV2.size() < 1){NM.Bad_Debt_MWh__c = 0; }else{NM.Bad_Debt_MWh__c = Double.valueOf(LV2[0].get('VALUE__C'));}
    //Debt Admin:
        if(LV3.size() < 1){NM.Debt_Admin_MWh__c = 0; }else{NM.Debt_Admin_MWh__c = Double.valueOf(LV3[0].get('VALUE__C'));}
    //Lose:          
        if(LV4.size() < 1){NM.Lose_MPAN__c = 0; }else{NM.Lose_MPAN__c = Double.valueOf(LV4[0].get('VALUE__C'));}
    //Meter:    
        if(LV5.size() < 1){NM.Meter_MPAN_Annum__c = 0; }else{NM.Meter_MPAN_Annum__c = Double.valueOf(LV5[0].get('VALUE__C'));}
    //Other:  
        if(LV6.size() < 1){NM.Other_MWh__c = 0; }else{NM.Other_MWh__c = Double.valueOf(LV6[0].get('VALUE__C'));}
    //Register:  
        if(LV7.size() < 1){NM.Register_MPAN__c = 0; }else{NM.Register_MPAN__c = Double.valueOf(LV7[0].get('VALUE__C'));}
	//Renew:
        if(LV8.size() < 1){NM.Renew_MPAN__c = 0; }else{NM.Renew_MPAN__c = Double.valueOf(LV8[0].get('VALUE__C'));}
 	//Serve:
        if(LV9.size() < 1){NM.Serve_MPAN_Annum__c = 0; }else{NM.Serve_MPAN_Annum__c = Double.valueOf(LV9[0].get('VALUE__C'));}
	//Support:
        if(LV10.size() < 1){NM.Support_MWh__c = 0; }else{NM.Support_MWh__c = Double.valueOf(LV10[0].get('VALUE__C'));}      
    }

}
Can someone give me a nudge in the right direction so I can change the above and thu learn something new!I need to be able to have the same end result as above but there has to be another way than SOQL queries, maybe a map but as I said I am not sure.

Cheers,

A
Best Answer chosen by Alex Kirby
Pankaj_GanwaniPankaj_Ganwani
Hi Alex,

You can do the following:

After storing the Index value, you can create a set<String> which contains all values such as (Index + 'Acquire', Index + 'Bad Debt') and so on.

Now, use below mentioned SOQL:
for(Cost__c objCost : [select Index__C, Value__c FROM Cost__c WHERE Index__c IN : setcollected])
   mapIndexToCost(objCost.Index, objCost.Value__c);

Now, in for loop which you have used for assigning the values, you can do like this:

if(mapIndexToCose.containskey(objCost.Index + 'Acquire'))
     NM.Acquire_MPAN__c = Double.valueOf(mapIndexToCose.get(objCost.Index + 'Acquire'));
//In the same manner rest of fields will be assigned.


Note: I have done this only for one record only. This is not bulkified yet.

All Answers

Pankaj_GanwaniPankaj_Ganwani
Hi Alex,

You can do the following:

After storing the Index value, you can create a set<String> which contains all values such as (Index + 'Acquire', Index + 'Bad Debt') and so on.

Now, use below mentioned SOQL:
for(Cost__c objCost : [select Index__C, Value__c FROM Cost__c WHERE Index__c IN : setcollected])
   mapIndexToCost(objCost.Index, objCost.Value__c);

Now, in for loop which you have used for assigning the values, you can do like this:

if(mapIndexToCose.containskey(objCost.Index + 'Acquire'))
     NM.Acquire_MPAN__c = Double.valueOf(mapIndexToCose.get(objCost.Index + 'Acquire'));
//In the same manner rest of fields will be assigned.


Note: I have done this only for one record only. This is not bulkified yet.
This was selected as the best answer
Alex KirbyAlex Kirby
Thanks for the reply Pankaj, I can see what is supossed to be happening in the above but I am having trouble with an error:

Method does not exist or incorrect signature: mapIndexToCost(String, Decimal)

Either I am implementing it incorrectly (very possbile) or it is something else?

Please can you assist?
Alex KirbyAlex Kirby
trigger Net_Mar_Ini on Net_Margin_Calculation__c (before insert) {
    
     string Index;
         
    for(Net_Margin_Calculation__c NM : Trigger.new){
        Index = nm.index__c;
    }  
    set<String> setcollected	;
    
    setcollected.add(Index + 'Acquire');
    setcollected.add(Index + 'Bad Debt');
    setcollected.add(Index + 'Debt Admin');
    setcollected.add(Index + 'Lose');
    setcollected.add(Index + 'Meter');
    setcollected.add(Index + 'Other');
    setcollected.add(Index + 'Register');
    setcollected.add(Index + 'Renew');
    setcollected.add(Index + 'Serve');
    setcollected.add(Index + 'Support');
    
    
         
    for(Cost__c objCost : [select Index__C, Value__c FROM Cost__c WHERE Index__c IN : setcollected])
   mapIndexToCost(objCost.Index__c, objCost.Value__c);
    
    for(Net_Margin_Calculation__c NM : Trigger.new){

        if(mapIndexToCost.containskey(Index + 'Acquire')){
        NM.Acquire_MPAN__c = Double.valueOf(mapIndexToCost.get(Index + 'Acquire'));}
      
    }

}
Pankaj_GanwaniPankaj_Ganwani
Hi Alex,

Please declare this map before using it

like

Map<String,Double> mapIndexToCost  = new Map<String,Double>();
Pankaj_GanwaniPankaj_Ganwani
Hi

And intialize the set as well like
Set<String> setcollected = new Set<String>();
Alex KirbyAlex Kirby
trigger Net_Mar_Ini on Net_Margin_Calculation__c (before insert) {
    
     string Index;
     Map<String, Double> mapIndexToCost = new Map<String, Double>(); 
	 set<String> setcollected  = new Set<String>();
    
    for(Net_Margin_Calculation__c NM : Trigger.new){
        Index = nm.index__c;
    }  
    
    setcollected.add(Index + 'Acquire');
    setcollected.add(Index + 'Bad Debt');
    setcollected.add(Index + 'Debt Admin');
    setcollected.add(Index + 'Lose');
    setcollected.add(Index + 'Meter');
    setcollected.add(Index + 'Other');
    setcollected.add(Index + 'Register');
    setcollected.add(Index + 'Renew');
    setcollected.add(Index + 'Serve');
    setcollected.add(Index + 'Support');
    
     
    for(Cost__c objCost : [select Index__C, Value__c FROM Cost__c WHERE Index__c IN : setcollected])

    mapIndexToCost(objCost.Index__c, objCost.Value__c);
    
    for(Net_Margin_Calculation__c NM : Trigger.new){

        if(mapIndexToCost.containskey(Index + 'Acquire')){
        NM.Acquire_MPAN__c = Double.valueOf(mapIndexToCost.get(Index + 'Acquire'));}
      
    }

}
This is what I am getting now;

Method does not exist or incorrect signature: mapIndexToCost(String, Decimal)

If I change it to this:
  
trigger Net_Mar_Ini on Net_Margin_Calculation__c (before insert) {
    
     string Index;
     Map<String, Double> mapIndexToCost = new Map<String, Double>(); 
	 set<String> setcollected  = new Set<String>();
    
    for(Net_Margin_Calculation__c NM : Trigger.new){
        Index = nm.index__c;
    }  
    
    setcollected.add(Index + 'Acquire');
    setcollected.add(Index + 'Bad Debt');
    setcollected.add(Index + 'Debt Admin');
    setcollected.add(Index + 'Lose');
    setcollected.add(Index + 'Meter');
    setcollected.add(Index + 'Other');
    setcollected.add(Index + 'Register');
    setcollected.add(Index + 'Renew');
    setcollected.add(Index + 'Serve');
    setcollected.add(Index + 'Support');
    
     
    for(Cost__c objCost : [select Index__C, Value__c FROM Cost__c WHERE Index__c IN : setcollected])

    mapIndexToCost(objCost.Index__c, double.valueof(objCost.Value__c));
    
    for(Net_Margin_Calculation__c NM : Trigger.new){

        if(mapIndexToCost.containskey(Index + 'Acquire')){
        NM.Acquire_MPAN__c = Double.valueOf(mapIndexToCost.get(Index + 'Acquire'));}
      
    }

}

I get Method does not exist or incorrect signature: mapIndexToCost(String, Double)

Once this is cracked I should be good to finish it off.

Thanks again
Alex KirbyAlex Kirby
I'm ashamed to say I have never used a set or a map before so this is new, I know it's going to be a huge help going forward though :)
Pankaj_GanwaniPankaj_Ganwani
Hi Alex,

Sorry, Please at line no 25, use mapIndexToCost.put(objCost.Index__c, double.valueof(objCost.Value__c));
Alex KirbyAlex Kirby
Thanks so much Pankaj, the error is history and I've learned a valuable lesson.

Take care.
Pankaj_GanwaniPankaj_Ganwani
Hi Alex,

You are welcome.And, don't be ashamed if you don't know something. This dev community is meant for this purpose only so that we can share our knowledge and help others in troubleshooting.

And, I like to be a helping hand.:)