+ Start a Discussion
Keith Stephens 18Keith Stephens 18 

Bulkify help with soql query

Hello,
I have the follwoing code being executed in a for loop and I want to remove it from the loop and bulkify it how would I do this?
A list, or a map? I preferer to use a map if possible.
 
Account_Procedure_Rate__c rate = [SELECT Amount_To_Pay__c, Procedure_Cost__c FROM Account_Procedure_Rate__c 
														 	 WHERE  CPT_Code__c = :proc.CPT_Code__c AND Main_Center__c = :centerId];
							proc.Amount_To_Pay__c = rate.Amount_To_Pay__c;
							proc.Procedure_Cost__c = rate.Procedure_Cost__c;



Thanks,
K
Best Answer chosen by Keith Stephens 18
Paul S.Paul S.
Keith - somewhere else in your code you could loop through all of your existing Account_Procedure_Rate__c records and build a nested map.  I believe you'd want to use the Main_Center__c (I am assuming it's unique) Id as the key of the outer map, the values of which would be an inner map with a key of CPT_Code__c and values of the Account_Procedure_Rate__c records.

You'd be left with Main_Center__c => CPT_Code__c => Account_Procedure_Rate__c record, where your for loop illustrated above could reference the map like this:
 
proc.Amount_To_Pay__c = yourNewMap.get(centerId).get(proc.CPT_Code__c).Amount_To_Pay__c;
proc.Procedure_Cost__c = yourNewMap.get(centerId).get(proc.CPT_Code__c).Procedure_Cost__c

 

All Answers

Mark Moore 7Mark Moore 7
Keith, I don't see a loop.

The way you've written the SELECT, it appears you expect it to *always* return a single APR.  The CPT_Code is unique within a Main_Center, and it is never missing.  Right?

What are you looping over?
Amit Chaudhary 8Amit Chaudhary 8
Can you please post your full code with for loop so that we can help you to remove SOQL from for loop
Paul S.Paul S.
Keith - somewhere else in your code you could loop through all of your existing Account_Procedure_Rate__c records and build a nested map.  I believe you'd want to use the Main_Center__c (I am assuming it's unique) Id as the key of the outer map, the values of which would be an inner map with a key of CPT_Code__c and values of the Account_Procedure_Rate__c records.

You'd be left with Main_Center__c => CPT_Code__c => Account_Procedure_Rate__c record, where your for loop illustrated above could reference the map like this:
 
proc.Amount_To_Pay__c = yourNewMap.get(centerId).get(proc.CPT_Code__c).Amount_To_Pay__c;
proc.Procedure_Cost__c = yourNewMap.get(centerId).get(proc.CPT_Code__c).Procedure_Cost__c

 
This was selected as the best answer