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
Mayank Sharma 21Mayank Sharma 21 

avoid soql in for loop

Below is the code. Can someone help to get the count outside for loop 

public static void generateInvoiceIds(Map<Id, Payment__c> newMap) {
    
        List<Payment__c> updatednewPaymentRecords = new List<Payment__c>();
         for(Payment__c record :newMap.values()) {
            if(record.TransactionStatus__c == 'Credit note'  && record.InvoiceNumberAuto__c == null){
                Payment__c record1 = new Payment__c(id=record.id);
                record1.InvoiceDate__c = System.today();
           // system.debug('CLINIC ID - ' + newPaymentRecord.ClinicIdAuto__c + ' --- APPOINTMENT MONTH - ' + newPaymentRecord.OpportunityAppointmentMonthAuto__c);
            Integer numberOfInvoices = [select count() from Payment__c where Opportunity__r.Clinic__c = :record.ClinicIdAuto__c and InvoiceNumberAuto__c != null and (TransactionStatus__c = 'Paid' OR  TransactionStatus__c = 'Credit note') and OpportunityAppointmentMonthAuto__c = :record.OpportunityAppointmentMonthAuto__c];
            if(record.TransactionStatus__c == 'Credit note')
            record1.InvoiceNumberAuto__c = 'G-VU-' + record.OpportunityAppointmentDate__c.format('MMYY') + '-' + record.ClinicInvoiceCodeAuto__c + '-' + (numberOfInvoices + 1);
            else
            record1.InvoiceNumberAuto__c = 'TR-VU-' + record.OpportunityAppointmentDate__c.format('MMYY') + '-' + record.ClinicInvoiceCodeAuto__c + '-' + (numberOfInvoices + 1);
            updatednewPaymentRecords.add(record1);
        }
            
        }
        update updatednewPaymentRecords;
    }
Best Answer chosen by Mayank Sharma 21
AjazAjaz
Hi Mayank,

Below code should help you.
  
Map<String,Set<String>> clinicApptMonthMap = new Map<String,Set<String>>();
Set<String> clinicSet = new Set<String>();
Map<string,Integer> clinicApptMonthCounterMap = new Map<String,Ineger>();
List<Payment__c> updatednewPaymentRecords = new List<Payment__c>();

for(Payment__c payRec : newMap.values()){
    clinicSet.add(payRec.ClinicIdAuto__c);
    if(clinicApptMonthMap.get(payRec.ClinicIdAuto__c)!=null){
        Set<String> newSet = clinicApptMonthMap.get(payRec.ClinicIdAuto__c);
        newSet.add(payRec.OpportunityAppointmentMonthAuto__c);
        clinicApptMonthMap.put(payRec.ClinicIdAuto__c,newSet);
    }
    else
        clinicApptMonthMap.put(payRec.ClinicIdAuto__c, new Set<String>{payRec.OpportunityAppointmentMonthAuto__c});
}

for(Payment__c iterator : [select id, Opportunity__r.Clinic__c, OpportunityAppointmentMonthAuto__c from Payment__c where Opportunity__r.Clinic__c in :clinicSet and InvoiceNumberAuto__c != null 
                                        and (TransactionStatus__c = 'Paid' OR  TransactionStatus__c = 'Credit note')]){
    if(clinicApptMonthMap.get(iterator.ClinicIdAuto__c)!=null){
        if(clinicApptMonthMap.get(iterator.ClinicIdAuto__c).contains(iterator.OpportunityAppointmentMonthAuto__c)){
            if(clinicApptMonthCounterMap.get(iterator.ClinicIdAuto__c+iterator.OpportunityAppointmentMonthAuto__c)!= null){
                clinicApptMonthCounterMap.put(iterator.ClinicIdAuto__c+iterator.OpportunityAppointmentMonthAuto__c,clinicApptMonthCounterMap.get(iterator.ClinicIdAuto__c+iterator.OpportunityAppointmentMonthAuto__c)+1)
            else
                clinicApptMonthCounterMap.put(iterator.ClinicIdAuto__c+iterator.OpportunityAppointmentMonthAuto__c,1);
        }
    }
}

for(Payment__c payRec : newMap.values()){
    payRec.InvoiceDate__c = System.today();
    payRec.InvoiceNumberAuto__c = (payRec.TransactionStatus__c == 'Credit note')?'G-VU-':'TR-VU-'+payRec.OpportunityAppointmentDate__c.format('MMYY') + '-' + payRec.ClinicInvoiceCodeAuto__c + '-' + (clinicApptMonthCounterMap.get(payRec.ClinicIdAuto__c+payRec.OpportunityAppointmentMonthAuto__c) + 1);
    updatednewPaymentRecords.add(payRec);
}

if(updatednewPaymentRecords.size() > 0)
    update updatednewPaymentRecords;

Dont forget to give a thumbsup if this helps you.

Regards,
Zaja

All Answers

Balayesu ChilakalapudiBalayesu Chilakalapudi
Try like this, 
public static void generateInvoiceIds(Map<Id, Payment__c> newMap) {    
        List<Payment__c> updatednewPaymentRecords = new List<Payment__c>();
        Map<Id,Integer> paymap=new Map<Id,Integer>();
        Integer count=0;
        for(Payment__c record :newMap.values()) {
            count=[select count() from Payment__c where Opportunity__r.Clinic__c = :record.ClinicIdAuto__c and InvoiceNumberAuto__c != null and (TransactionStatus__c = 'Paid' OR TransactionStatus__c = 'Credit note') and OpportunityAppointmentMonthAuto__c = :record.OpportunityAppointmentMonthAuto__c];
            paymap.put(record.Id,count);
        } 
         for(Payment__c record :newMap.values()) {
            if(record.TransactionStatus__c == 'Credit note'  && record.InvoiceNumberAuto__c == null){
                Payment__c record1 = new Payment__c(id=record.id);
                record1.InvoiceDate__c = System.today();
           // system.debug('CLINIC ID - ' + newPaymentRecord.ClinicIdAuto__c + ' --- APPOINTMENT MONTH - ' + newPaymentRecord.OpportunityAppointmentMonthAuto__c);
            Integer numberOfInvoices = paymap.get(record.Id);       
            if(record.TransactionStatus__c == 'Credit note')
            record1.InvoiceNumberAuto__c = 'G-VU-' + record.OpportunityAppointmentDate__c.format('MMYY') + '-' + record.ClinicInvoiceCodeAuto__c + '-' + (numberOfInvoices + 1);
            else
            record1.InvoiceNumberAuto__c = 'TR-VU-' + record.OpportunityAppointmentDate__c.format('MMYY') + '-' + record.ClinicInvoiceCodeAuto__c + '-' + (numberOfInvoices + 1);
            updatednewPaymentRecords.add(record1);
        }
            
        }
        update updatednewPaymentRecords;
    }

Let us know if it helps.
Amit Chaudhary 8Amit Chaudhary 8
Please try below code 
public static void generateInvoiceIds(Map<Id, Payment__c> newMap) 
{

	Set<String> setMonth = new set<String>();
	Set<String> setClinicIdAuto = new set<String>();	
	
	for(Payment__c record :newMap.values()) 
	{
		if(record.TransactionStatus__c == 'Credit note'  && record.InvoiceNumberAuto__c == null)
		{
			setMonth.add( record.OpportunityAppointmentMonthAuto__c );
			setClinicIdAuto.add( record.ClinicIdAuto__c );
		}
	}

	if( setMonth.size() >0 )
	{
		List<Payment__c> lstPay = [ select OpportunityAppointmentMonthAuto__c from Payment__c where Opportunity__r.Clinic__c = :setClinicIdAuto and InvoiceNumberAuto__c != null and (TransactionStatus__c = 'Paid' OR  TransactionStatus__c = 'Credit note') and OpportunityAppointmentMonthAuto__c = :setMonth ] ;

		
		Map<String,List<Payment__c>> mapMothWisePay = new Map<String,List<Payment__c>>();
		for( Payment__c  pa : lstPay )
		{
			if(mapMothWisePay.containsKey(pa.OpportunityAppointmentMonthAuto__c))
			{
				List<Payment__c> listPayment = mapMothWisePay.get(pa.OpportunityAppointmentMonthAuto__c);
				listPayment.add(pa);
			}
			else
			{
				List<Payment__c> listPayment = new List<Payment__c> ();
				listPayment.add(pa);
				mapMothWisePay.put(pa.OpportunityAppointmentMonthAuto__c , listPayment);
			}
		}
		
	
		List<Payment__c> updatednewPaymentRecords = new List<Payment__c>();
		for(Payment__c record :newMap.values()) 
		{
			if(record.TransactionStatus__c == 'Credit note'  && record.InvoiceNumberAuto__c == null && mapMothWisePay.containsKey(record.OpportunityAppointmentMonthAuto__c) )
			{
				List<Payment__c> lstP = mapMothWisePay.get(record.OpportunityAppointmentMonthAuto__c);
				Integer numberOfInvoices = lstP.size();
				
			
				Payment__c record1 = new Payment__c(id=record.id);
				record1.InvoiceDate__c = System.today();
				
				if(record.TransactionStatus__c == 'Credit note')
					record1.InvoiceNumberAuto__c = 'G-VU-' + record.OpportunityAppointmentDate__c.format('MMYY') + '-' + record.ClinicInvoiceCodeAuto__c + '-' + (numberOfInvoices + 1);
				else
					record1.InvoiceNumberAuto__c = 'TR-VU-' + record.OpportunityAppointmentDate__c.format('MMYY') + '-' + record.ClinicInvoiceCodeAuto__c + '-' + (numberOfInvoices + 1);
				updatednewPaymentRecords.add(record1);
			}
		
		}
		update updatednewPaymentRecords;
		
	}		
}

Let us know if this will help you
 
AjazAjaz
Hi Mayank,

Below code should help you.
  
Map<String,Set<String>> clinicApptMonthMap = new Map<String,Set<String>>();
Set<String> clinicSet = new Set<String>();
Map<string,Integer> clinicApptMonthCounterMap = new Map<String,Ineger>();
List<Payment__c> updatednewPaymentRecords = new List<Payment__c>();

for(Payment__c payRec : newMap.values()){
    clinicSet.add(payRec.ClinicIdAuto__c);
    if(clinicApptMonthMap.get(payRec.ClinicIdAuto__c)!=null){
        Set<String> newSet = clinicApptMonthMap.get(payRec.ClinicIdAuto__c);
        newSet.add(payRec.OpportunityAppointmentMonthAuto__c);
        clinicApptMonthMap.put(payRec.ClinicIdAuto__c,newSet);
    }
    else
        clinicApptMonthMap.put(payRec.ClinicIdAuto__c, new Set<String>{payRec.OpportunityAppointmentMonthAuto__c});
}

for(Payment__c iterator : [select id, Opportunity__r.Clinic__c, OpportunityAppointmentMonthAuto__c from Payment__c where Opportunity__r.Clinic__c in :clinicSet and InvoiceNumberAuto__c != null 
                                        and (TransactionStatus__c = 'Paid' OR  TransactionStatus__c = 'Credit note')]){
    if(clinicApptMonthMap.get(iterator.ClinicIdAuto__c)!=null){
        if(clinicApptMonthMap.get(iterator.ClinicIdAuto__c).contains(iterator.OpportunityAppointmentMonthAuto__c)){
            if(clinicApptMonthCounterMap.get(iterator.ClinicIdAuto__c+iterator.OpportunityAppointmentMonthAuto__c)!= null){
                clinicApptMonthCounterMap.put(iterator.ClinicIdAuto__c+iterator.OpportunityAppointmentMonthAuto__c,clinicApptMonthCounterMap.get(iterator.ClinicIdAuto__c+iterator.OpportunityAppointmentMonthAuto__c)+1)
            else
                clinicApptMonthCounterMap.put(iterator.ClinicIdAuto__c+iterator.OpportunityAppointmentMonthAuto__c,1);
        }
    }
}

for(Payment__c payRec : newMap.values()){
    payRec.InvoiceDate__c = System.today();
    payRec.InvoiceNumberAuto__c = (payRec.TransactionStatus__c == 'Credit note')?'G-VU-':'TR-VU-'+payRec.OpportunityAppointmentDate__c.format('MMYY') + '-' + payRec.ClinicInvoiceCodeAuto__c + '-' + (clinicApptMonthCounterMap.get(payRec.ClinicIdAuto__c+payRec.OpportunityAppointmentMonthAuto__c) + 1);
    updatednewPaymentRecords.add(payRec);
}

if(updatednewPaymentRecords.size() > 0)
    update updatednewPaymentRecords;

Dont forget to give a thumbsup if this helps you.

Regards,
Zaja
This was selected as the best answer