Hi all,

       I got stuck with the limit exception error in Salesforce. I'll explain the situation first, we are trying to copy about quarter million products from a custom object to Standard Product Object in salesforce and in the mean time we are also creating entries in PricebookEntry object. We want to do both these tasks using a Single Batch Apex.

I've tried the following the methods for solving this problem

1) Wrote a Batch Apex which has following things written in the execute method

    a) Loop which Upserts Products to Standard Product Object from Custom Object

    b) Loop through the Standard Product Object and create pricebook entries in a seperate loop.

Result : Got exception "Too many queries"

2) Wrote a Batch Apex with a single loop, instead of adding them to a list and then adding it by batch, I've tried to upsert products to Standard Product Object one by one also creating pricebook entries at the same time

Result : Got exception "Too many DML Statements"

What would be the best methodology to use in this situation?

I'm also adding my code snippets with this,

Batch Apex with two loops

global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  //Upserting products from CB_Products to Product2
 
      productstoupload = new List<Product2>();      
      for(sObject s : scope){
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;           
      productstoupload.add(prod);                      
    }       
    upsert productstoupload SF_ID__c;       
   
  //Upserting entries from Product2 to PricebookEntry
 
    //pricebookupdate = new List<PricebookEntry>();
    pricebookinsert = new List<PricebookEntry>(); 
   
 
   
    List<sObject> batch = [Select Id, Price__c, Availability_Status__c FROM Product2];
       
     for(sObject s : batch){
    
         Product2 prod = (Product2)s;               
         PricebookEntry pbe = new PricebookEntry();     
         pbe.Pricebook2Id=pricebook.ID;       
         pbe.Product2Id=prod.Id;
         pbe.UnitPrice=prod.Price__c;
        
         //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.
                
         if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
         pbe.IsActive= false;
         }                
         else{        
         pbe.IsActive= true;        
         }
        
         pbe.UseStandardPrice=false;
        
        //Checking whether there is a Pricebook Entry exist for a particular product
     
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
                    
       //If there is no existing entry then we will insert a new Pricebook Entry
                    
          if(exist.isEmpty()){
          pricebookinsert.add(pbe);
          }
         
       //Else we will update the existing entry
         
          else{      
          //pricebookupdate.add(pbe);
         
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
          if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
          pbupd.IsActive=false;
          }
          else{
          pbupd.IsActive=true;
          }
          pbupd.UnitPrice=prod.Price__c;
          update pbupd;         
         
         
          }
         
    } 
   
    insert pricebookinsert;
   
   // update pricebookupdate;
   
  }

Batch Apex with Single Loop:

 global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  //Upserting products from CB_Products to Product2
 
      for(sObject s : scope){
     
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();     
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;                 
      upsert prod SF_ID__c;
    
          PricebookEntry pbe = new PricebookEntry();     
          pbe.Pricebook2Id=pricebook.ID;       
          pbe.Product2Id=prod.Id;
          pbe.UnitPrice=prod.Price__c;
     
      //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.
                
          if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
          pbe.IsActive= false;
          }                
          else{        
          pbe.IsActive= true;        
          }               
         pbe.UseStandardPrice=false;
        
        
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
        
         if(exist.isEmpty()){
         insert pbe;
         //pricebookinsert.add(pbe);
         }
        
         else{                    
         
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
         
              if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
              pbupd.IsActive=false;
              }
              else{
              pbupd.IsActive=true;
              }
         
              pbupd.UnitPrice=prod.Price__c;
              update pbupd;        
         }
    }       
   
    //insert pricebookinsert;                  
 
  }

It would be a great help, if someone could provide any help on this issue.

Many Thanks,

Joe

Hi all,

   I'm new to Salesforce Apex Development. I'm getting the following error while running the Test Class.

Message :

System.DmlException: Update failed. First exception on row 0 with id 01uM0000000bBPqIAM; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [UnitPrice]: [UnitPrice]

Stack Trace:

Class.ProductImport_BatchJob.execute: line 85, column 11 External entry point

I'm also posting the BatchJob Class below.

global class ProductImport_BatchJob implements Database.Batchable<SObject>{
 
  Date dateToRunProcessFor = null;
  String email = 'andy.bailey@bioscience.co.uk';
//  String email = 'jodil.davis@bioscience.co.uk';
// Fetching Standard Pricebook ID
  sObject pricebook = [select ID from Pricebook2 where IsStandard = TRUE];
 
  /* Old Query which picks products with Availability Status not equal to NLA,OBS and RP
  public String query =
    'Select Id, Availability_Status__c, Size__c, Supplier_Code__c, Catalogue_Number__c, UK_List_Price__c, Product_Name__c, Manufacturer__c, UOM__c ' +       
    'From CB_Products__c where ' +
    'Availability_Status__c != \'NLA\' and ' +
    'Availability_Status__c != \'OBS\' and ' +
    'Availability_Status__c != \'RP\'';
  */
  //New Query
  public String query = 'Select Id, Availability_Status__c, Size__c, Supplier_Code__c, Catalogue_Number__c, UK_List_Price__c, Product_Name__c, Manufacturer__c, UOM__c FROM CB_Products__c';
 
  List<Product2> productstoupload;
  List<PricebookEntry> pricebookupdate;
  List<PricebookEntry> pricebookinsert;
 
  global Database.QueryLocator start(Database.BatchableContext bc){
    return Database.getQueryLocator(query);
  }
   
  global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  //Upserting products from CB_Products to Product2
 
     productstoupload = new List<Product2>();      
      for(sObject s : scope){
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;           
      productstoupload.add(prod);                      
    }       
    upsert productstoupload SF_ID__c;       
   
  //Upserting entries from Product2 to PricebookEntry
 
    pricebookupdate = new List<PricebookEntry>();
    pricebookinsert = new List<PricebookEntry>(); 
   
 
   
    List<sObject> loopvar = [Select Id, Price__c, Availability_Status__c FROM Product2];
       
     for(sObject s : loopvar){
    
         Product2 prod = (Product2)s;               
         PricebookEntry pbe = new PricebookEntry();     
         pbe.Pricebook2Id=pricebook.ID;       
         pbe.Product2Id=prod.Id;
         pbe.UnitPrice=prod.Price__c;
         pbe.IsActive= true;
         pbe.UseStandardPrice=false;
        
        //Checking whether there is a Pricebook Entry exist for a particular product
     
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
                    
       //If there is no existing entry then we will insert a new Pricebook Entry
                    
          if(exist.isEmpty()){
          pricebookinsert.add(pbe);
          }
         
       //Else we will update the existing entry
         
          else{       
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
          pbupd.UnitPrice=prod.Price__c;
          update pbupd;         
          }
         
    } 
   
    insert pricebookinsert;
   
    //update pricebookupdate;
   
  }
   
  global void finish(Database.BatchableContext bc){
    // Get the ID of the AsyncApexJob representing this batch job 
   
   // from Database.BatchableContext. 
   
   // Query the AsyncApexJob object to retrieve the current job's information. 
   
    AsyncApexJob a = [Select Id, Status, NumberOfErrors, JobItemsProcessed,
      TotalJobItems, CreatedBy.Email
      from AsyncApexJob where Id =
      :bc.getJobId()];
   // Send an email to the Apex job's submitter notifying of job completion. 
   
    Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
    mail.setToAddresses(new String[] {email});
    mail.setSubject('Apex Sharing Recalculation ' + a.Status);
    mail.setPlainTextBody
    ('ProductImport batch Apex job processed ' + a.TotalJobItems +
    ' batches with '+ a.NumberOfErrors + ' failures.');
    Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
  }

}

It would be a great help if someone could provide me some help on this error.

Many Thanks,

Joe

I'm trying to upsert pricebookentry via apex class. I know upsert operation need an external id but I found that we couldn't create one in PricebookEntry object, so how we can tackle this problem. Does anyone had faced any similar issues before? please help me.

Many Thanks,

Joe

Hi all,

  Could someone explain the relation between these 3 objects. Please give explaination on the context of creating new products. What all entries should be created while creating a product?

Hi all,

       I got stuck with the limit exception error in Salesforce. I'll explain the situation first, we are trying to copy about quarter million products from a custom object to Standard Product Object in salesforce and in the mean time we are also creating entries in PricebookEntry object. We want to do both these tasks using a Single Batch Apex.

I've tried the following the methods for solving this problem

1) Wrote a Batch Apex which has following things written in the execute method

    a) Loop which Upserts Products to Standard Product Object from Custom Object

    b) Loop through the Standard Product Object and create pricebook entries in a seperate loop.

Result : Got exception "Too many queries"

2) Wrote a Batch Apex with a single loop, instead of adding them to a list and then adding it by batch, I've tried to upsert products to Standard Product Object one by one also creating pricebook entries at the same time

Result : Got exception "Too many DML Statements"

What would be the best methodology to use in this situation?

I'm also adding my code snippets with this,

Batch Apex with two loops

global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  //Upserting products from CB_Products to Product2
 
      productstoupload = new List<Product2>();      
      for(sObject s : scope){
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;           
      productstoupload.add(prod);                      
    }       
    upsert productstoupload SF_ID__c;       
   
  //Upserting entries from Product2 to PricebookEntry
 
    //pricebookupdate = new List<PricebookEntry>();
    pricebookinsert = new List<PricebookEntry>(); 
   
 
   
    List<sObject> batch = [Select Id, Price__c, Availability_Status__c FROM Product2];
       
     for(sObject s : batch){
    
         Product2 prod = (Product2)s;               
         PricebookEntry pbe = new PricebookEntry();     
         pbe.Pricebook2Id=pricebook.ID;       
         pbe.Product2Id=prod.Id;
         pbe.UnitPrice=prod.Price__c;
        
         //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.
                
         if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
         pbe.IsActive= false;
         }                
         else{        
         pbe.IsActive= true;        
         }
        
         pbe.UseStandardPrice=false;
        
        //Checking whether there is a Pricebook Entry exist for a particular product
     
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
                    
       //If there is no existing entry then we will insert a new Pricebook Entry
                    
          if(exist.isEmpty()){
          pricebookinsert.add(pbe);
          }
         
       //Else we will update the existing entry
         
          else{      
          //pricebookupdate.add(pbe);
         
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
          if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
          pbupd.IsActive=false;
          }
          else{
          pbupd.IsActive=true;
          }
          pbupd.UnitPrice=prod.Price__c;
          update pbupd;         
         
         
          }
         
    } 
   
    insert pricebookinsert;
   
   // update pricebookupdate;
   
  }

Batch Apex with Single Loop:

 global void execute(Database.BatchableContext bc, List<sObject> scope){
 
  //Upserting products from CB_Products to Product2
 
      for(sObject s : scope){
     
      CB_Products__c cbp = (CB_Products__c)s;
      Product2 prod = new Product2();     
      // FIELD 'MAPPINGS':
      prod.Catalogue_Number__c     = cbp.Catalogue_Number__c;
      prod.Name                    = cbp.Product_Name__c;
      prod.Price__c                = cbp.UK_List_Price__c;
      prod.Manufacturer__c         = cbp.Manufacturer__c;
      prod.Availability_Status__c  = cbp.Availability_Status__c;
      prod.Supplier_Code__c        = cbp.Supplier_Code__c;
      prod.Size__c                 = cbp.Size__c;
      prod.UOM__c                  = cbp.UOM__c;
      prod.IsActive                = true;
      prod.SF_ID__c                = cbp.Id;                 
      upsert prod SF_ID__c;
    
          PricebookEntry pbe = new PricebookEntry();     
          pbe.Pricebook2Id=pricebook.ID;       
          pbe.Product2Id=prod.Id;
          pbe.UnitPrice=prod.Price__c;
     
      //If Product's Status is either 'OBS or 'NLA' make the PricebookEntry inactive.
                
          if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
          pbe.IsActive= false;
          }                
          else{        
          pbe.IsActive= true;        
          }               
         pbe.UseStandardPrice=false;
        
        
         List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id = :prod.Id];
        
         if(exist.isEmpty()){
         insert pbe;
         //pricebookinsert.add(pbe);
         }
        
         else{                    
         
          PricebookEntry pbupd = [Select Id,Product2Id,UnitPrice,Pricebook2Id,IsActive,UseStandardPrice FROM PricebookEntry WHERE Product2Id = :prod.Id];
         
              if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA'){
              pbupd.IsActive=false;
              }
              else{
              pbupd.IsActive=true;
              }
         
              pbupd.UnitPrice=prod.Price__c;
              update pbupd;        
         }
    }       
   
    //insert pricebookinsert;                  
 
  }

It would be a great help, if someone could provide any help on this issue.

Many Thanks,

Joe