+ Start a Discussion
DBManagerDBManager 

Clone record 'n' times, change Master-Detail in new records

I am trying to use a trigger to create a series of cloned records in a custom object, Item.

 

The Item is a in a Master-Detail relationship with both Opportunity and Event/Issue (aka Issue_Year__c), and I want to change the Event/Issue field for each new, cloned Item. The idea is to split the revenue from one Item across monthly Event/Issue records (for online sales, for example).

 

However, having tried various cominations of before/after insert and .clone(false/true) function, I can't seem to get it to work.

 

Here is my code, as it stands:

 

trigger SeriesItems on Item__c (after insert) {

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

for (integer i = 0; i < Trigger.new.size(); i++){
    if (Trigger.new[i].Item_Sold__c == 'CIPI Membership' && Trigger.new[i].Cloned__c == FALSE){
        itemIds.add(Trigger.new[i].Id);
    }
}

Item__c[] itemList =
    [SELECT Id, Subscription_Term_Months__c, Event_Issue__r.Product__c FROM Item__c WHERE Id IN :itemIds];

if (itemList.size() > 0){
    for (Item__c item :ItemList){
        for (integer n = 1; n < item.Subscription_Term_Months__c; n++){
            Item__c copy = item.clone();
            copy.Cloned__c = TRUE;
            Issue_Year__c ei = 
                [SELECT Id FROM Issue_Year__c WHERE Product__c = :item.Event_Issue__r.Product__c AND CALENDAR_MONTH(Date__c) = 7 LIMIT 1];
            copy.Event_Issue__c = ei.Id;
            insert copy;
        }
    }
}

}

 

Here is the current error message I get, when using a test class:

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, SeriesItems: execution of AfterInsert caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ItemTrigger: execution of BeforeInsert caused by: System.QueryException: List has no rows for assignment to SObject Trigger.ItemTrigger: line 13, column 1: [] Trigger.SeriesItems: line 22, column 1:

 

And here is the relevant lines of the trigger to which this error refers:

trigger ItemTrigger on Item__c (before insert,after update,after insert) {

    System.debug('Item Trigger');
    Item__c[] item = Trigger.New;
    Item__c[] itemOld = Trigger.Old;
    Double lineNumber;
    String UniqueId='';
    list<ContactAttendance__c> caList = New list<ContactAttendance__c>(); 
        
    for(Integer i = 0; i< item.Size();i++){
        
        if(Trigger.IsInsert && Trigger.IsBefore){
            lineNumber = [select Max_Line_Number__c from opportunity where id = :item[i].Opportunity__c limit 1].Max_Line_Number__c; 

 

sfdcfoxsfdcfox

First of all, you have a Query inside a Loop, which is a Bad Idea™. Secondly, it sounds like Opportunity__c is not yet populated when the trigger executes, and so it's falling flat on it's proverbial face.

 

Try this:

 

map<id,opportunity> opps = new map<id,opportunity>();
for(item__c item:trigger.new)
  opps.put(item.opportunity__c,null);
opps.putAll([SELECT id,max_line_number__c from opportunity where id in :opps.keyset()]);
for(item__c item:trigger.new)
  if(item.opportunity__c != null)
    // DO SOMETHING HERE WITH opps.get(item.opportunity__c).Max_Line_Number__c

Since the rest of the code is missing, I had to just stop there, but hopefully you'll see what's going on. By the by, this is a form of trigger code that I've coined "Aggregate-Query-Update" (though I may not be the first); it is perhaps one of the most fundamental and common trigger patterns you will use when writing triggers. It always occurs in the same form: loop through trigger.new or trigger.old (or both), perform a Query that is placed into a Map, and then operate on the values inside the Map.

DBManagerDBManager

Thanks for your reply, sfdcfox.

 

You're right, of course - that query was inside the loop because I was being lazy during testing. Here's the updated code:

trigger SeriesItems on Item__c (after insert) {

Set<Id> itemIds = new Set<Id>();
Set<String> evPrd = new Set<String>();

Map<Id, String> itemMap = new Map<Id, String>();

for (integer i = 0; i < Trigger.new.size(); i++){
    if (Trigger.new[i].Item_Sold__c == 'CIPI Membership' && Trigger.new[i].Cloned__c == FALSE){
        itemIds.add(Trigger.new[i].Id);
        evPrd.add(Trigger.new[i].Event_Issue__r.Product__c);
        itemMap.put(Trigger.new[i].Id, Trigger.new[i].Event_Issue__r.Product__c);
    }
}

Item__c[] itemList =
    [SELECT Id, Subscription_Term_Months__c, Event_Issue__r.Product__c FROM Item__c WHERE Id IN :itemIds];
    
Issue_Year__c[] evList =
    [SELECT Id FROM Issue_Year__c WHERE Product__c IN :evPrd AND CALENDAR_MONTH(Date__c) = 7];

if (itemList.size() > 0){
    for (Item__c item :ItemList){
        for (integer n = 1; n < item.Subscription_Term_Months__c; n++){
            Item__c copy = item.clone();
            copy.Cloned__c = TRUE;
            for (Issue_Year__c ei :evList){
                copy.Event_Issue__c = ei.Id;
            }
            insert copy;
        }
    }
}

}

 

As for the trigger that is generating the error, here is more of the code:

trigger ItemTrigger on Item__c (before insert,after update,after insert) {

    System.debug('Item Trigger');
    Item__c[] item = Trigger.New;
    Item__c[] itemOld = Trigger.Old;
    Double lineNumber;
    String UniqueId='';
    list<ContactAttendance__c> caList = New list<ContactAttendance__c>(); 
        
    for(Integer i = 0; i< item.Size();i++){
        
        if(Trigger.IsInsert && Trigger.IsBefore){
            lineNumber = [select Max_Line_Number__c from opportunity where id = :item[i].Opportunity__c limit 1].Max_Line_Number__c;
            
            if(lineNumber == Null){
                lineNumber = 0;
            }
        
            item[i].Line_Number__c = lineNumber + 1;
            item[i].SA__c = null;
            item[i].Override_ok__c = false;
            item[i].commission_paid__c = 0;
            item[i].Invoice_Status__c = null;
            item[i].AMount_Paid_Net__c = 0;
            item[i].Override_Formulae__c = False;
            item[i].Manual__c = 0;
            
        }

 

So how would what you've written fit into the above?

 

Thanks again.

DBManagerDBManager

So I tried this:

trigger ItemTrigger on Item__c (before insert,after update,after insert) {

    System.debug('Item Trigger');
    Item__c[] item = Trigger.New;
    Item__c[] itemOld = Trigger.Old;
    Double lineNumber;
    String UniqueId='';
    list<ContactAttendance__c> caList = New list<ContactAttendance__c>(); 
    
    map<id,opportunity> opps = new map<id,opportunity>();
    
    for(Integer i = 0; i< item.Size();i++){
        
        opps.put(item[i].opportunity__c,null);
        opps.putAll([SELECT id,max_line_number__c from opportunity where id in :opps.keyset()]);
        
        if(Trigger.IsInsert && Trigger.IsBefore){
            lineNumber = opps.get(item[i].opportunity__c).Max_Line_Number__c;
            
            if(lineNumber == Null){
                lineNumber = 0;
            }
        
            item[i].Line_Number__c = lineNumber + 1;
            item[i].SA__c = null;
            item[i].Override_ok__c = false;
            item[i].commission_paid__c = 0;
            item[i].Invoice_Status__c = null;
            item[i].AMount_Paid_Net__c = 0;
            item[i].Override_Formulae__c = False;
            item[i].Manual__c = 0;
            
        }

 

And got the following error message from the test class:

System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, SeriesItems: execution of AfterInsert caused by: System.DmlException: Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ItemTrigger: execution of BeforeInsert caused by: System.NullPointerException: Attempt to de-reference a null object Trigger.ItemTrigger: line 18, column 1: [] Trigger.SeriesItems: line 30, column 1: []

 

Any help?

DBManagerDBManager

I now have a trigger which creates the required clones, and updates a few fields on those copied records. However, what I cannot work out how to do, is to update the Master-Detail relationship on the clones.

 

For example, if I create an Item record, which is a subscription related to the May '12 Event/Issue record, with a term that last two months, then I want to create one clone that is related to the Jun '12 Event/Issue record.

 

Here's my code as it stands:

trigger SeriesItems on Item__c (after insert) {

Set<Id> origIds = new Set<Id>();
Set<Id> newIds = new Set<Id>();

for (integer i = 0; i < Trigger.new.size(); i++){
    if (Trigger.new[i].Item_Sold__c == 'CIPI Membership' && Trigger.new[i].Cloned__c == FALSE){
        Decimal am = Trigger.new[i].Amount__c / Trigger.new[i].Subscription_Term_Months__c;
        origIds.add(Trigger.new[i].Id);
        for (integer n = 1; n < Trigger.new[i].Subscription_Term_Months__c; n++){
            Item__c copy = Trigger.new[i].clone(false);
            copy.Cloned__c = TRUE;
            copy.Amount__c = am;
            insert copy;
            newIds.add(copy.Id);
        }
    }
}

Item__c[] newList =
    [SELECT Id, Line_Number__c FROM Item__c WHERE Id IN :newIds];
    
if (newList.size() > 0){
    for (Item__c item :newList){
        item.Line_Number__c = item.Line_Number__c + 1;
    }
    update newList;
}

Item__c[] origList =
    [SELECT Id, Amount__c, Subscription_Term_Months__c FROM Item__c WHERE Id IN :origIds];

if (origList.size() > 0){
    for (Item__c orig :origList){
        orig.Amount__c = orig.Amount__c / orig.Subscription_Term_Months__c;
    }
}

update origList;

}

 

Any thoughts?

DBManagerDBManager

BUMP...

 

I have a seemingly very simple task to do, that I am finding impossible:

 

  • Item A is a child of Event/Issue X. 
  • Event/Issue X has date D and product P
  1. Find Event/Issue Y, which has product P and date D + 1 month
  2. Find Event/Issue Z, which has product P and date D + 2 months
  3. Clone Item A, to create virtual Items B and C
  4. Assign Event/Issue Y to Item B
  5. Assign Event/Issue Z to Item C
  6. Insert Items B and C

Can anyone help?

 

Jerun JoseJerun Jose
Can you post the trigger which you have developed till now? Also let me know where you are having a problem with that.
Jerun JoseJerun Jose
Better open a new thread for this, as it seems to be unrelated to this topic
DBManagerDBManager

Thanks Jerun Jose.

 

The code I've written so far is above. All I've done here is to breakdown the essence of the problem.

 

The code I've written clones the record n times, but I don't know how to include the extra steps I've outlined above.

 

Any thoughts?