function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Jim_morrisonJim_morrison 

Working with null rows of query results

I am unable to code easily, the following logic.
 
--> Query the batch of Leads to find if the campaign member already exists for them 
--->  If it does not exist, Create One.
 
The issue i see is that: The SOQL select in a for loop, returns only selected rows. If we want to work on those "not matched" I cannot utilize that result.
Ex1: (does not work)
Code:
for(campaign c : [select id from campaign where id in : leadIdList]){
 if c[i].id == null)
  // Do some action such as create a campaign member record.
 i++;
}

Even if you use another coding logic as below, It does not work. since it crosses the SOQL (governers limit) for leads
Ex2: (does notwork)
Code:
for(Lead ld: Trigger.new){ 
   if([select count() from Campaign where id in: leadidList] <= 0){
   //Do some action such as create a campaign member record and store in a list
   }
}
//Then insert all newly created campaign member records at once.
insert xxxx;

 
I bet there is a solution, that I am unable to use here.
 
Thanks in advance.
werewolfwerewolf
Your query is sort of backwards.  The link from the lead to the campaign will be from the CampaignMember table, and you're querying the Campaign table.

Try instead (some pseudocode for you here because I'm too lazy to write the real code):

Code:
Make a Set<Id> out of your list of lead IDs
for each [Select LeadId From CampaignMember Where LeadId in :listLeadIds]
{
   remove this LeadId from the Set
   //because this LeadId is clearly in the CampaignMember table
}
for each remaining member of your Set
{
   make a CampaignMember entry and store them in a list
}
insert your list of CampaignMembers

 




Message Edited by werewolf on 04-23-2008 02:38 PM
werewolfwerewolf
Also: never put a select statement inside a loop as you did in your second snippet.  That's a practice that will end badly.
TehNrdTehNrd
Give this a try. I just threw it together in notepad so there may be some syntas issues but it should give you the right idea:

Code:
trigger name on Lead (before insert) {

Set<Id> leadIds = new Set<Id>();

for(lead l : trigger.new){
leadIDs.add(l.id);
}

List<CampaignMember> memberList = List<CampaignMember>([select Id, Name, LeadId from CampaignMember where LeadId IN :leadIds]);
Map<Id, CampaignMember> memberMap = new Map<Id, CampaignMember>();

for(CampaignMember cm : memberList){
memberMap.put(cm.LeadId,cm);
}

List<CampaignMember> newMembers = new List<CampaignMember>();

for(lead l : trigger.new){
CampaignMember campaignExist = memberMap.get(l.id);

if(campaignExist == null){
//no campaing found
CampaignMember newMember = new Campaign(
field1 = '12321',
field2 = 'asdfasd',
etc = etc
);
newMembers.add(newMember);
}
}

if(newMembers.Size() > 0){
insert newMembers;
}

}

 
EDIT: Fixed.




Message Edited by TehNrd on 04-23-2008 03:08 PM
ppatppat
Do a count. If count is > 0 then perform your actions.
werewolfwerewolf
Well that won't suffice because the count can only tell you how many of the leads are currently CampaignMembers but not which ones aren't (and need to be created).  I think my pseudocode and TehNrd's real code are the correct solution.
Jim_morrisonJim_morrison

TehNrd,
 Thanks for writing up the code. But it did not work.I think it is just similar to what I have originally written.
 The issue is while creating campaign members.

Code:
if(campaignExist == null){//no campaing found
  CampaignMember newMember = new Campaign(
     field1 = '12321',
     field2 = 'asdfasd',
  etc = etc   );

Let's say 2 out of 4 leadIDs matched, The memberList is 2. Neither we have then the specifics as to as which ids did not match (nor we have captured the null values in that list). Therefore the MemberList values are incorrect. Consequently the logic to create a Campaign Member just as leadID is null value is inadequate.

ppat,
  I tried your approach already, which seemed correct(using count() <= 0 and using List.get(i) ) . But the issue is ('select count()' goes in a for loop, and the No of SOQL statements exceed in a trigger which is not acceptable.

Werewolf,
 The psedocode is right, thanks. But I think it will be a more complicated than that.First -> The campaign member creation requires campaign ID(required field). In our leads, if there were a lead with campaign ID that did not match with existing ones, then we need to assign a 'unknown' campaign ID. Therfore we need to find if that is matched using another soql query that again must using "non-matching" technique, identify and change campaignID Names on stored in Map etc etc.

But, I think it should work. I will update if it does not.

Thanks Everyone..

werewolfwerewolf
First -> The campaign member creation requires campaign ID(required field). In our leads, if there were a lead with campaign ID that did not match with existing ones, then we need to assign a 'unknown' campaign ID. Therfore we need to find if that is matched using another soql query that again must using "non-matching" technique, identify and change campaignID Names on stored in Map etc etc.

I guess I just assumed you knew what campaign you wanted to put it in.  But that should be easy -- why not just make one and only one immutable "Unknown" campaign?  Then you could just store the ID specifically, or you can do a simple singleton SOQL search.  If you do end up writing SOQL for it, don't do that campaign SOQL query inside the lead loop -- do it outside the loop, before you get into the leads at all.
Jim_morrisonJim_morrison
I was able to code it up. It required from my play around with additional and lists, storage maps, finally. But I am getting this DML error when I do test with 500 records(leads)
 
Code:
Here is the resource usage

Resource usage for namespace: (default) 
Number of SOQL queries: 3 out of 100 
Number of query rows: 1 out of 500 
Number of SOSL queries: 0 out of 20 
Number of DML statements: 2 out of 100 
Number of DML rows: 1000 out of 500 ******* CLOSE TO LIMIT 
Number of script statements: 3513 out of 200000 
Maximum heap size: 53438 out of 500000 
Number of callouts: 0 out of 10 
Number of Email Invocations: 0 out of 10

a) I did not see why it is taking 2 DML when actually I am running one(one statement).
b) I see that the API document specifies that The total number of DML records processed is 100X (x being the no of records input); but the array list seems to be have limitations here.
 
I do need to over come this limits. (expect upto 5000 lead batches at a time).
Do I have to use multiple lists based on size() and insert 5 times...Does that make a difference?
Is there no easy solution here? how about just getting the "unlimited licence" ?
TehNrdTehNrd
Why such a high batch size? 200 should be adequate.


Message Edited by TehNrd on 04-24-2008 01:48 PM
werewolfwerewolf
In what context do you intend this to be run?  As a button or webservice, or as part of a Lead trigger?
Jim_morrisonJim_morrison
Well, in case they update or insert change all at once..(from the DB ofd the third party app, where we get the feed from).
The max they said was 5000.
 
Does this "unlimited licence" overcome all these limits?
Jim_morrisonJim_morrison

Its an after trigger. that generally comes from multiple sources --web form probably has only 1 at a time. A feed that sends batches..(that I am not sure of the limit)...and their claim of a DB refresh that might trigger all(>5000).....

So, I am just trying to overcome the limits as much as possible.

 

 

 

TehNrdTehNrd
The third party app should be able to set the batch size. 200 is about the standard, this is what the dataloader uses.
werewolfwerewolf
To my knowledge mass inserts or updates are limited to 200 at a time anyway, so even if you have 5000, it will actually be broken up into 25 batches of 200 -- right?
werewolfwerewolf
Batch size can be set for queries, but I'm pretty sure it's stuck at 200 for updates and inserts.
TehNrdTehNrd
Yup, 200 looks like the max. I just set the dataloader to 300 but it only did them in sets of 200.

So for your testing you only need to insert/update 200 leads, not 500.



Message Edited by TehNrd on 04-24-2008 02:04 PM
Jim_morrisonJim_morrison
Is 200 the limit? Is that documented somewhere(I might want to have this for reference).
 
Well, I am not sure they would send 25 batches of 200 each....(I dont think they are aware of this limit). At this time, all I am looking for is a programmatic/technical solution.
 
(any way I can splitup and call multiple 'inserts' etc)?  No-one commented about that "unlimited licence" ?
 
 
werewolfwerewolf
It's documented here for updates:

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_update.htm

(search for the word "Maximum")

I'm sure it's documented also on the page for insert too.
Jim_morrisonJim_morrison
Thanks All.