You need to sign in to do that
Don't have an account?
ScorpionKing
Help with bulk Upsert!!!!!
Hi,
I have a code snippet :
for(int i=0;i
Account acc = { Code goes here......};
upsert acc accId; // This it what happening and is consuming a lot of DML statements from LIMITS
/*****Below is what i wanna achive******/
List accList = new List();
accList.add(acc);
}
How can i do something like this :-
upsert accList ---------> how i suppose to pass the externalId
update/insert accList works fine...but i wanna do an upsert.
Thnx in advance....
I have a code snippet :
for(int i=0;i
Account acc = { Code goes here......};
upsert acc accId; // This it what happening and is consuming a lot of DML statements from LIMITS
/*****Below is what i wanna achive******/
List accList = new List();
accList.add(acc);
}
How can i do something like this :-
upsert accList ---------> how i suppose to pass the externalId
update/insert accList works fine...but i wanna do an upsert.
Thnx in advance....
Number of SOQL queries: 19 out of 20 ******* CLOSE TO LIMIT
Number of query rows: 20 out of 2000
Number of SOSL queries: 0 out of 0
Number of DML statements: 1 out of 20
Number of DML rows: 1 out of 200
Number of script statements: 814 out of 10400
Maximum heap size: 0 out of 200000
Number of callouts: 0 out of 10
Number of Email Invocations: 0 out of 10
Number of fields describes: 0 out of 10
Number of record type describes: 0 out of 10
Number of child relationships describes: 0 out of 10
Number of picklist describes: 0 out of 10
Number of future calls: 9 out of 10 ******* CLOSE TO LIMIT
Number of find similar calls: 0 out of 0
Number of System.runAs() invocations: 0 out of 0
my workflow rule is getting deleted after this para..... please suggest , if any one want to see my trigger i will so...please suggest and help me build a builkified trigger / application.
thnx :((
Hey
Post your trigger and I'll try help.
Wes
trigger ScheduleWorkFlow on Workflow__c (before insert, before update) {
if (Trigger.new.size() < 100) {
for (Workflow__c batchRun : Trigger.new) {
String setUpId = getCompiereSetUpId(batchRun);
Integer soqlQueries = Limits.getLimitQueries() - Limits.getQueries();
if(soqlQueries > 1 && setUpId != ''){
if ( batchRun.Running__c == true ) {
if ((batchRun.Schedule__c == true) && (batchRun.Purge__c == false) && (batchRun.Account_purge__c == false)) {
if(choice == 'BP'){
postAddressInfo(setUpId, batchRun.Next_Run_Date__c);
getDetailAddress(setUpId, batchRun.Next_Run_Date__c);
}
if (choice == 'BP') {
String url = WebServiceCall.makeURL();
String phoneUrl = WebServiceCall.makeURLForPhone();
WebServiceCall.callservice(url, choice, phoneUrl, setUpId, conflict, batchRun.Next_Run_Date__c);
futureCalls = Limits.getLimitFutureCalls() - Limits.getFutureCalls();
if(futureCalls > 1){
if(conflict == 'Compiere Wins'){
callPostSFDCWins(setUpId,conflict,batchRun.Next_Run_Date__c);
}else if(conflict == 'SFDC Wins'){
callPostSFDCWins(setUpId,conflict,batchRun.Next_Run_Date__c);
callPostSFDCWinsClosed(setUpId,conflict,batchRun.Next_Run_Date__c);
}
}
} else if (choice == 'SO') {
String url = WebServiceCall.makeSOINVURL();
String orderLineUrl = WebServiceCall.makeURLForOrderLine();
futureCalls = Limits.getLimitFutureCalls() - Limits.getFutureCalls();
if(futureCalls > 1){
WebServiceCall.callservice(url, choice,orderLineUrl,setUpId,conflict,batchRun.Next_Run_Date__c);
}
} else if (choice == 'INV') {
String url = WebServiceCall.makeSOINVURL();
String invoiceLineUrl = WebServiceCall.makeURLForInvoiceLine();
futureCalls = Limits.getLimitFutureCalls() - Limits.getFutureCalls();
if(futureCalls > 1){
WebServiceCall.callservice(url, choice,invoiceLineUrl,setUpId,conflict,batchRun.Next_Run_Date__c);
}
}
}
}
}
// Create the next Batch Run and configure it so that the scheduler workflow
// adds a Trigger_Batch_Run field update in the time-based workflow queue.
Workflow__c workFlow = new Workflow__c(
Username__c = batchRun.Username__c,
Password__c = batchRun.Password__c,
Host__c = batchRun.Host__c,
Port__c = batchRun.Port__c,
Organization__c = batchRun.Organization__c,
Tenant__c = batchRun.Tenant__c,
Role__c = batchRun.Role__c,
Warehouse__c = batchRun.Warehouse__c,
Table__c = batchRun.Table__c,
Account_purge__c = batchRun.Account_purge__c,
Account_Purging_Type__c = batchRun.Account_Purging_Type__c,
Account_Last_N_Days__c = batchRun.Account_Last_N_Days__c,
Purge__c = batchRun.Purge__c,
Purging_Type__c = batchRun.Purging_Type__c,
Last_n_days__c = batchRun.Last_n_days__c,
Sync_Direction__c = batchRun.Sync_Direction__c,
Sync_Mode__c = batchRun.Sync_Mode__c,
Conflict_Resolution__c = batchRun.Conflict_Resolution__c,
Frequency__c = batchRun.Frequency__c,
Script_Type__c = batchRun.Script_Type__c,
Next_Run_Date__c = nextrun,
Running__c = false,
Schedule__c = true,
Trigger_Scheduler_1__c = true);
maxDMLStatements = Limits.getLimitDMLStatements() - Limits.getDMLStatements();
if(maxDMLStatements > 1){
insert workFlow;
}
setAssociation(batchRun,workFlow,nextrun);
BatchScript__c bsUpdate = new BatchScript__c (
Id = setUpId,
Schedule__c = false,
Next_Run_Date__c = nextrun);
maxDMLStatements = Limits.getLimitDMLStatements() - Limits.getDMLStatements();
if(maxDMLStatements > 1){
update bsUpdate;
}
} else {
// Alternate Trigger Scheduler flags to keep workflow queued and current
if (batchRun.Trigger_Scheduler_1__c == false) {
batchRun.Trigger_Scheduler_1__c = true;
batchRun.Trigger_Scheduler_2__c = false;
} else {
batchRun.Trigger_Scheduler_1__c = false;
batchRun.Trigger_Scheduler_2__c = true;
}
}
}catch(System.Exception exp){
// Report Governor Limit Stats and set return values
String limitText = GenerateLog.getLimitInfo();
errorMessage = exp.getMessage();
GenerateLog.logScheduleAssociation(errorMessage,setUpId);
}
}
}
}
public String getCompiereSetUpId(Workflow__c batchRunOld){
String setUpId = '';
Integer soqlQueries = Limits.getLimitQueries() - Limits.getQueries();
if(soqlQueries > 1){
List ruleList = [select Compiere_Setup__c from Rule__c where Workflow_Rule__c =: batchRunOld.id];
System.debug('Limits.getQueries()--------getCompiereSetUpId-->'+Limits.getQueries());
for(Rule__c rule:ruleList){
setUpId = rule.Compiere_Setup__c;
}
}
return setUpId;
}
public void setAssociation(Workflow__c batchRunOld,Workflow__c batchRunNew,DateTime st){
Integer soqlLimit = Limits.getLimitQueries() - Limits.getQueries();
if(soqlLimit > 1){
List ruleList = [select Compiere_Setup__c from Rule__c where Workflow_Rule__c =: batchRunOld.id];
System.debug('Limits.getQueries()--------setAssociation-->'+Limits.getQueries());
String setUpId = '';
for(Rule__c rule:ruleList){
Rule__c newRule = new Rule__c();
newRule.Compiere_Setup__c = rule.Compiere_Setup__c;
setUpId = rule.Compiere_Setup__c;
newRule.Workflow_Rule__c = batchRunNew.id;
insert newRule;
}
}
}
public void callPostSFDCWins(String id,String conflict,DateTime nextRunDate){
String postUrl = '';
//System.debug('in callPostSFDCWins ------> ');
Integer soqlQueries = Limits.getLimitQueries() - Limits.getQueries();
Set accountIdSet = new Set();
List idList = new List();
if(soqlQueries > 1){
List accountList = [Select o.AccountId from Opportunity o where o.StageName = 'Closed Won' and o.LastModifiedDate >: nextRunDate];//o.AccountId =: strId];
System.debug('Limits.getQueries()--------callPostSFDCWins-1->'+Limits.getQueries());
for(Opportunity op : accountList){
accountIdSet.add(op.AccountId);
}
List accList = [select Id, Name,Custom_Account_Id__c, Description, Rating, Website, ACQUSITIONCOST__c, C_BP_SIZE_ID__c, C_BP_STATUS_ID__c, ISACTIVE__c, C_BP_GROUP_ID__c,
SOCREDITSTATUS__c, DUNS__c, FIRSTSALE__c, C_GREETING_ID__c, C_INDUSTRYCODE_ID__c, AD_LANGUAGE__c, ACTUALLIFETIMEVALUE__c,
NAICS__c, NAME2__c, NUMBEREMPLOYEES__c, TOTALOPENBALANCE__c, AD_ORG_ID__c, POTENTIALLIFETIMEVALUE__c, ISPROSPECT__c, REFERENCENO__c,
SALESVOLUME__c, SHAREOFCUSTOMER__c, ISSUMMARY__c, ISTAXEXEMPT__c, TAXID__c, AD_CLIENT_ID__c, BillingStreet, BillingCity, BillingState, BillingCountry, BillingPostalCode, Phone, Fax from Account where id in :accountIdSet and Custom_Account_Id__c = null];
System.debug('Limits.getQueries()--------callPostSFDCWins-->'+Limits.getQueries());
for(Account a:accList){
String strId = a.Id;
soqlQueries = Limits.getLimitQueries() - Limits.getQueries();
if(soqlQueries > 1){
postUrl = WebServicePost.makePostUrl('create');
String postAddUrl = WebServicePost.makePostURLForAddress('create');
String addressXML = '';
futureCalls = Limits.getLimitFutureCalls() - Limits.getFutureCalls();
if(futureCalls > 1){
WebServicePost.postAccount(postUrl,id,SalesforceToCompiereIntegration.buildXMLForPost(a,id),conflict);//,a.Id, postAddUrl, addressXML);
}
}
}
}else{
GenerateLog.logScheduleAssociation('SOQL CLOSE TO LIMIT(callPostSFDCWins) - '+soqlQueries,id);
}
}
public void callPostSFDCWinsClosed(String id,String conflict,DateTime nextRunDate){
String postUrl = '';
Integer soqlQueries = Limits.getLimitQueries() - Limits.getQueries();
if(soqlQueries > 1){
List accList = [select Id, Name,Custom_Account_Id__c, Description, Rating, Website, ACQUSITIONCOST__c, C_BP_SIZE_ID__c, C_BP_STATUS_ID__c, ISACTIVE__c, C_BP_GROUP_ID__c,
SOCREDITSTATUS__c, DUNS__c, FIRSTSALE__c, C_GREETING_ID__c, C_INDUSTRYCODE_ID__c, AD_LANGUAGE__c, ACTUALLIFETIMEVALUE__c,
NAICS__c, NAME2__c, NUMBEREMPLOYEES__c, TOTALOPENBALANCE__c, AD_ORG_ID__c, POTENTIALLIFETIMEVALUE__c, ISPROSPECT__c, REFERENCENO__c,
SALESVOLUME__c, SHAREOFCUSTOMER__c, ISSUMMARY__c, ISTAXEXEMPT__c, TAXID__c, AD_CLIENT_ID__c, BillingStreet, BillingCity, BillingCountry, BillingPostalCode, BillingState, Phone, Fax from Account where LastModifiedDate >: nextRunDate and Custom_Account_Id__c != null];
System.debug('Limits.getQueries()--------callPostSFDCWinsClosed-->'+Limits.getQueries());
for(Account a:accList){
sfdcWinClosedIdList.add(a.Id);
postUrl = WebServicePost.makePostUrl('update');
String postAddUrl = WebServicePost.makePostURLForAddress('update');
String addressXML = '';
futureCalls = Limits.getLimitFutureCalls() - Limits.getFutureCalls();
if(futureCalls > 1){
WebServicePost.postAccount(postUrl,id,SalesforceToCompiereIntegration.buildXMLForPost(a,id),conflict);
}
}
}else{
GenerateLog.logScheduleAssociation('SOQL CLOSE TO LIMIT(callPostSFDCWinsClosed) - '+soqlQueries,id);
}
}
public void postAddressInfo(String id, DateTime nextRunDate){
String locationId = '';
//System.debug('In getAddressDetails ----->');
Integer soqlQueries = Limits.getLimitQueries() - Limits.getQueries();
if(soqlQueries > 1){
Integer nDays = 1;
List location = [select Id, C_Location_ID__c, C_BPartner_Location_ID__c, Name, Custom_Account_Id__c,BillingStreet, BillingCity, BillingCountry, BillingPostalCode, BillingState, Phone, Fax from Account where Custom_Account_Id__c != null and LastModifiedDate >: System.now().addDays(-nDays)];
System.debug('Limits.getQueries()--------postAddressInfo-->'+Limits.getQueries());
//LastModifiedDate >: nextRunDate and
for(Account loc:location){
locationId = loc.C_Location_ID__c;
String postAddUrl = '';
if(loc.C_BPartner_Location_ID__c != null) {
postAddUrl = WebServicePost.makePostURLForAddress('update');
String locationAddUrl = WebServicePost.makePostURLForAddressDetails('update');
WebServicePost.postAddress(postAddUrl,id,SalesforceToCompiereIntegration.buildXMLForAddressUpdatePost(loc,id,loc.Custom_Account_Id__c));
WebServicePost.postAddress(locationAddUrl,id,SalesforceToCompiereIntegration.buildXMLForAddressUpdate(loc,id,loc.Custom_Account_Id__c));
}else{
postAddUrl = WebServicePost.makePostURLForAddress('create');
WebServicePost.postAddress(postAddUrl,id,SalesforceToCompiereIntegration.buildXMLForAddressPost(loc,id,loc.Custom_Account_Id__c));
}
}
}else{
GenerateLog.logScheduleAssociation('SOQL CLOSE TO LIMIT(postAddressInfo) - '+soqlQueries,id);
}
}
public void getDetailAddress(String id, DateTime nextRunDate){
String locationId = '';
Integer soqlQueries = Limits.getLimitQueries() - Limits.getQueries();
if(soqlQueries > 1){
Integer nDays = 1;
List location = [select Id, C_Location_ID__c ,Name from Account where Custom_Account_Id__c != null and LastModifiedDate >: System.now().addDays(-nDays)]; // and LastModifiedDate >: System.now().addDays(-1)
System.debug('Limits.getQueries()--------getDetailAddress-->'+Limits.getQueries());
//LastModifiedDate >: nextRunDate and
for(Account loc:location){
locationId = loc.C_Location_ID__c;
if(locationId != null) {
String addressUrl = WebServiceCall.makeURLForAddress(locationId);
WebServiceCall.callAddressService(id, addressUrl);
}
}
}else{
GenerateLog.logScheduleAssociation('SOQL CLOSE TO LIMIT(getDetailAddress) - '+soqlQueries,id);
}
}
public String checkListForId(List accIdList,String accountId){
String message = 'success';
for(String a:accIdList){
if(a == AccountId){
message = 'fail';
}
}
return message;
}
}
Any help will be appreciated.....i have some SOQLs in apex also which m calling from triggers.
Hey
For your callouts I'm not sure what to do.. it's a problem I have too in certain areas where I have to change the way I work quite significantly. For you SOQL governor limit I can help.
You should never select from the same object more than once within your trigger. You should fetch all the records in the object up front and then later find the records you may need within that list e.g. at the beginning of your code
List<Account> accList = [select Id, Name,Custom_Account_Id__c, Description, Rating, Website, ACQUSITIONCOST__c, C_BP_SIZE_ID__c, C_BP_STATUS_ID__c, ISACTIVE__c, C_BP_GROUP_ID__c,
SOCREDITSTATUS__c, DUNS__c, FIRSTSALE__c, C_GREETING_ID__c, C_INDUSTRYCODE_ID__c, AD_LANGUAGE__c, ACTUALLIFETIMEVALUE__c,
NAICS__c, NAME2__c, NUMBEREMPLOYEES__c, TOTALOPENBALANCE__c, AD_ORG_ID__c, POTENTIALLIFETIMEVALUE__c, ISPROSPECT__c, REFERENCENO__c,
SALESVOLUME__c, SHAREOFCUSTOMER__c, ISSUMMARY__c, ISTAXEXEMPT__c, TAXID__c, AD_CLIENT_ID__c, BillingStreet, BillingCity, BillingState, BillingCountry, BillingPostalCode, Phone, Fax from Account];
Then later
List<Account> myAccounts = new List<Account>();
for(Account a: accList) {
if(a.id=MyVar)
accList.add(a);
}
Cheers,
Wes
thnx
Hey
Unfortunately it all counts as the same context and limits are almost always based on a single context. There's a bit more info here.
Wes
Thnx for the tips.....