You need to sign in to do that
Don't have an account?
Limit Exception - How to tackle this?
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
try following code. This is optimized for to handle the limits.
Please vertfy the logic
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>();
map<id, sObject> mapbatch = new map([Select Id, Price__c, Availability_Status__c FROM Product2]); List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id IN :mapbatch.KeySet()]; map<id, List<PricebookEntry>> mapexist = new map<id, List<PricebookEntry>>();
for(PricebookEntry oPricebookEntry: exist)
{
if(mapexist.containsKey(oPricebookEntry.Product2Id))
mapexist.get(oPricebookEntry.Product2Id).add(oPricebookEntry);
else
mapexist.put(oPricebookEntry.Product2Id, new list<PricebookEntry>{oPricebookEntry});
}
set<id> setexist2 = new set<id>();
for(sObject s : batch)
{
if(!mapexist.containskey(s.Id))
{
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
//If there is no existing entry then we will insert a new Pricebook Entry pricebookinsert.add(pbe);
}
//Else we will update the existing entry
else
{
list<PricebookEntry> lstpbupd = mapexist.get(s.Id);
for(PricebookEntry opbupd:lstpbupd)
{
if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA')
pbupd.IsActive=false;
else
pbupd.IsActive=true;
pbupd.UnitPrice=prod.Price__c;
pricebookinsert.add(pbupd);
}
}
}
upsert pricebookinsert;
}
Hi Pankaj,
Thanks for your reply. I've tried your code and it includes some errors which are syntactical error as well as logical errors. I've tried to correct the code with my limited knowledge (I'm not a Apex Developer, basically I'm a PHP Web Developer). But still I'm getting the following error while saving the code
"Error: Compile Error: Initial term of field expression must be a concrete SObject: LIST<PricebookEntry> at line 146 column 1"
could you have a look at my code and see whether you can fix it? Many thanks in advance for your help.
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>();
//map<id, sObject> mapbatch = new map<id, sObject>([Select Id, Price__c, Availability_Status__c FROM Product2]);
Map<Id, ID> mapbatch = new Map<Id, Id>();
for(Product2 sl:[Select Id, Price__c, Availability_Status__c FROM Product2])
mapbatch.put(sl.Id,sl.id);
List<PricebookEntry> exist = [Select Id,Product2Id FROM PricebookEntry WHERE Product2Id IN :mapbatch.KeySet()];
map<id, List<PricebookEntry>> mapexist = new map<id, List<PricebookEntry>>();
for(PricebookEntry oPricebookEntry: exist)
{
if(mapexist.containsKey(oPricebookEntry.Product2Id))
mapexist.get(oPricebookEntry.Product2Id).add(oPricebookEntry);
else
mapexist.put(oPricebookEntry.Product2Id, new list<PricebookEntry>{oPricebookEntry});
}
set<id> setexist2 = new set<id>();
List<sObject> batch = [Select Id, Price__c, Availability_Status__c FROM Product2];
for(sObject s : batch)
{
Product2 prod = (Product2)s;
if(!mapexist.containskey(s.Id))
{
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
//If there is no existing entry then we will insert a new Pricebook Entry pricebookinsert.add(pbe);
}
//Else we will update the existing entry
else
{
list<PricebookEntry> lstpbupd = mapexist.get(s.Id);
for(PricebookEntry opbupd:lstpbupd)
{
if(prod.Availability_Status__c == 'OBS' || prod.Availability_Status__c == 'NLA')
lstpbupd.IsActive=false;
else
lstpbupd.IsActive=true;
lstpbupd.UnitPrice=prod.Price__c;
pricebookinsert.add(lstpbupd);
}
}
}
upsert pricebookinsert;
}
Which is the line no 146? I am not able to identify the exact line.