+ Start a Discussion
ekorzekorz 

Trigger that builds a parent & child records from existing record plus related records

Hello Kind SFDC Masters!

I have two custom objects, Merchant__c  and Location__c, that I sync between our main production database and our SFDC database.  They're basically clean copies of sold Accounts, and their constituent child locations.  Think Subway -- 1 Merchant, 10000 child Locations.

I have Opportunity records that can accept an unlimited number of related addresses, a related object called "LocationHolder" with standard addy fields.

My goal is to build a trigger that, upon closing an opportunity, grabs info from Opportunity to build a Merchant, then grabs  LocationHolder records and builds child Locations.  Again, Locations are children of Merchant.  LocationHolder is related to Opportunity.

I can create the Merchant without issue, thanks mostly to another forum post, but I can't

a) grab the newly created Merchant ID, or
b) iterate through the Opportunity's related LocationHolder objects, be it zero or twenty, to create any Locations.

----So Far----

trigger createMerchantFromOpportunity on Opportunity (after insert, after update) {
    
    List <Merchant__c> MerchToInsert = new List <Merchant__c> ();  
       
    for (Opportunity o : Trigger.new) {
        

        if (o.Probability == 100) {     //my criteria for the trigger
        
        Merchant__c m = new Merchant__c ();
        
        // map opportunity fields to Merchant object
        
        m.Name = o.AccountName__c;         //named the same as the Account
m.Account__c = o.AccountID; //add reference back to Account       

m.etc__c = o.etc__c;     //etc. truncated here, you get the idea.
                    
        MerchToInsert.add(m);
                
        } //end if
             
    } //end for o
    
   try {
        insert MerchToInsert;
    } catch (system.Dmlexception e) {
        system.debug (e);
    }
    
}

----END So Far----

After the insert, I should have a Merchant ID, but I don't know how to retrieve it.  I also don't know how to build, say, 5 new Location__c records if there were 5 LocationHolder related records.

Any guidance is cause for celebration!

Best Answer chosen by Admin (Salesforce Developers) 
Chris760Chris760

This is actually totally doable.  I just designed a brochure app that has to do this with 4 levels of master/detail on a single insert.  Consult your Apex Handbook > Insert Operation > "Creating Parent and Child Records in a Single Statement Using Foreign Keys" section.  Don't be afraid of your handbook, I learn all kinds of awesome things in there every day... it's the closest thing I have to a bible.

 

http://www.salesforce.com/us/developer/docs/apexcode/ ) - keep that perminantly bookmarked in your favorites.

 

Essentially, you need to create a text field on the parent and set it to be an identifier in an external system.  Then you need to create your parent record first with all the fields populated, including a unique External Id value for your external ID text field that you made.  Add that record to a generic list<sObject> type (as opposed to the object type that it really is... this way you can add all kinds of different objects to the same list without it giving you an error that it's not the correct type).

 

Then you would create a single instance of an identical record to the one you just made, but ONLY populated with the external ID value and nothing else (but don't add this one to your list of sObjects like you did with the real record - this phantom record will only be used to inject in to the lookup field of your child record).

 

Now create your child object records, and for the lookup field to the parent, specify Parent__r = Parent_Instance_Name_You_Just_Made_Containing_Only_The_External_Id;.  THEN, add that to your sObject list directly behind the parent object that you added.  Then when you do your Insert DML, it will feed them in one at a time in order, first the parent, and then the children directly behind the parent, which will cause them to bind seamlessly the moment they save to the database.

 

The only tricky part is creating a unique external ID.  What I usually do is I create an auto-number field on the top-level record that's already saved (that would be the opportunity in your case), and I add that auto number to the string representation of the opportunity Id.  This way, we're dynamically generating our own unique ID by combining the existing opportunity ID with an autonumber that's hidden on the opportunity.

 

So the trigger breaks down as:

 

1) Create a generic "list<sObject> allObjects = new list<sObject>();" type to save all our different object types to.

 

2) Create for loop to iterate through all the records that the parent records would need to be created from and set the external ID to equal String.valueOf(opportunity.Id)+opportunity.auto_number.

 

3) Create a duplicate instance of the record you made above, but without ANY fields populated this time except the external ID, which equals String.valueOf(opportunity.Id)+opportunity.auto_number (we're going to inject this dummy reference object in to the child in order to bind the two records together).

 

4) Create a nested for loop at the end of step 3, which then iterates through all the records that the child records would need to be created from and set Parent_Lookup__r on all the child records to equal the name of the dummy instance record that you created in step 3, and make sure the lookup you're setting is the "__r" (relationship identifier) and not the "__c" (record Id) for the parent lookup.  Add each of these child records to your allObjects generic sObject list.

 

5) Insert allObjects;.

 

Since lists do their insert in the same order that you add the records to the list, the first record that would be inserted would be the parent, followed by the children.  Then the next record would be the next parent record, followed by the children, imitating the order in which our nested for loops added each record to the list.

 

You can also make this with mapped sets (it requires only 2 query calls, one for the parent and one for the child, which is considered more "bulk apex friendly" if you're converting hundreds of opportunities at the same time) but I don't want to overwhelm you with new stuff, especially when this probably will be rarely done in a bulk fashion.

 

Like I said though, just refer to your handbook, as the handbook has code examples (although it's an example for a single instance insert, and they make the records in a differernt order by making the child first and then the parent, which doesn't really work when you have multiple child records, where as creating the parent first and then the child by doing it my way, does).  But anytime I want to know if some totally crazy thing is possible, I just click "Search" on the handbook and type in the keywords of what I'm trying to do, or type in the data type that I know I need to work with and then I just read all the methods available to me to do the impossible, and what I need to do is usually in there. :)

 

All Answers

s_k_as_k_a

I think it is not possible in one trigger. So write another trigger on  merchant after insert and create locations based on AccountName__c

 

example

 

get all closed (100% probability ) opportunities  based on name on merchant which is equal to AccountName__c on opportunity the  collect all locatio holder for that opportunity create locations for merchant.

ekorzekorz

I like the idea -- a similar trigger for when a new Merchant is created. 

 

However, I don't think I could use AccountName__c on Opportunity for my query, since in my case any Account might have several Opportunities and several Merchants associated with it.  But, I can probably find some other unique criterion to do a match on.  I'll give it a shot after the weekend!

 

Thanks for the thoughts.

Chris760Chris760

This is actually totally doable.  I just designed a brochure app that has to do this with 4 levels of master/detail on a single insert.  Consult your Apex Handbook > Insert Operation > "Creating Parent and Child Records in a Single Statement Using Foreign Keys" section.  Don't be afraid of your handbook, I learn all kinds of awesome things in there every day... it's the closest thing I have to a bible.

 

http://www.salesforce.com/us/developer/docs/apexcode/ ) - keep that perminantly bookmarked in your favorites.

 

Essentially, you need to create a text field on the parent and set it to be an identifier in an external system.  Then you need to create your parent record first with all the fields populated, including a unique External Id value for your external ID text field that you made.  Add that record to a generic list<sObject> type (as opposed to the object type that it really is... this way you can add all kinds of different objects to the same list without it giving you an error that it's not the correct type).

 

Then you would create a single instance of an identical record to the one you just made, but ONLY populated with the external ID value and nothing else (but don't add this one to your list of sObjects like you did with the real record - this phantom record will only be used to inject in to the lookup field of your child record).

 

Now create your child object records, and for the lookup field to the parent, specify Parent__r = Parent_Instance_Name_You_Just_Made_Containing_Only_The_External_Id;.  THEN, add that to your sObject list directly behind the parent object that you added.  Then when you do your Insert DML, it will feed them in one at a time in order, first the parent, and then the children directly behind the parent, which will cause them to bind seamlessly the moment they save to the database.

 

The only tricky part is creating a unique external ID.  What I usually do is I create an auto-number field on the top-level record that's already saved (that would be the opportunity in your case), and I add that auto number to the string representation of the opportunity Id.  This way, we're dynamically generating our own unique ID by combining the existing opportunity ID with an autonumber that's hidden on the opportunity.

 

So the trigger breaks down as:

 

1) Create a generic "list<sObject> allObjects = new list<sObject>();" type to save all our different object types to.

 

2) Create for loop to iterate through all the records that the parent records would need to be created from and set the external ID to equal String.valueOf(opportunity.Id)+opportunity.auto_number.

 

3) Create a duplicate instance of the record you made above, but without ANY fields populated this time except the external ID, which equals String.valueOf(opportunity.Id)+opportunity.auto_number (we're going to inject this dummy reference object in to the child in order to bind the two records together).

 

4) Create a nested for loop at the end of step 3, which then iterates through all the records that the child records would need to be created from and set Parent_Lookup__r on all the child records to equal the name of the dummy instance record that you created in step 3, and make sure the lookup you're setting is the "__r" (relationship identifier) and not the "__c" (record Id) for the parent lookup.  Add each of these child records to your allObjects generic sObject list.

 

5) Insert allObjects;.

 

Since lists do their insert in the same order that you add the records to the list, the first record that would be inserted would be the parent, followed by the children.  Then the next record would be the next parent record, followed by the children, imitating the order in which our nested for loops added each record to the list.

 

You can also make this with mapped sets (it requires only 2 query calls, one for the parent and one for the child, which is considered more "bulk apex friendly" if you're converting hundreds of opportunities at the same time) but I don't want to overwhelm you with new stuff, especially when this probably will be rarely done in a bulk fashion.

 

Like I said though, just refer to your handbook, as the handbook has code examples (although it's an example for a single instance insert, and they make the records in a differernt order by making the child first and then the parent, which doesn't really work when you have multiple child records, where as creating the parent first and then the child by doing it my way, does).  But anytime I want to know if some totally crazy thing is possible, I just click "Search" on the handbook and type in the keywords of what I'm trying to do, or type in the data type that I know I need to work with and then I just read all the methods available to me to do the impossible, and what I need to do is usually in there. :)

 

This was selected as the best answer
ekorzekorz

Yowza, that's the stuff, Chris!  Thanks for the codebook section callout too, I just read through it and think that's a great starting point.  I'm pretty new to all this, but I think you laid it all out so cleanly that I will be able to pull it off.  I'll give this a try and report back.  Thank for taking the time to write such a thorough explaination!

Chris760Chris760

No problemo.  By the way, the other handbook you should bookmark is the SOQL - SOSL handbook, because that's the language you use to do all your queries.  They briefly cover it in the apex handbook, but since it's technically a "differerent language" that APEX simply utilizes, they don't go very in depth.  Where as the SOQL- SOSL handbook will tell you all kinds of crazy ways that you can build your queries to do anything you need to do (from grouping your query results by a certain field to sorting the results in ascending or descending order if you need to perform some kind of record-sort update trigger, or whatever).  The "SOQL SELECT Syntax" and "SOSL Syntax" sections are great starting points in that handbook.

 

 

The SOQL - SOSL handbook is available here:  http://www.salesforce.com/us/developer/docs/soql_sosl/

 

Good luck, and have fun coding.

ekorzekorz

Thanks again Chris, as far as I can tell your suggestion worked like a charm.  Here are my working code + notes.  If you happen to see anything terrible please let me know, otherwise I will just keep this at the end of the thread so future folks can find it.

 

 

 

trigger createMerchantAndLocationsFromOpportunity on Opportunity (after insert, after update) {

     list <sObject> allObjects = new list<sObject>();
     for (Opportunity o : Trigger.new) {

          //my criteria for firing the trigger


          if (o.Probability == 100) {

         //build Merchant object first


         Merchant__c m = new Merchant__c ();

         // now map opportunity fields to new merchant object that is being created with this opportunity


         m.Name = o.AccountName__c;   //required field on my Mechant Object
         m.Account__c = o.AccountID;    //required reference for my merchant object
         m.etc__c = o.etc__c;    //etc. etc. fields on my Merchant object
         m.MyExtID__c = String.valueOf(opportunity.Id)+o.AutoNumber__c;    //set external id to text of opportunityid+auto-number

         //build blank Merchant m_holder with only External id being the op.id+autonumber above
         Merchant__c m_holder = new Merchant__c (MyExtID__c = String.valueOf(opportunity.Id)+o.AutoNumber__c);

         allObjects.add(m);   

         //build list of LocationHolders attached to this opportunity, load into variable
         list<Location_Holder__c> lh = [Select Name, etc__c, OpptyAccount__c from Location_Holder__c WHERE Opportunity__c = :o.Id];

              //iterate through the list of LocationHolders

              for(Integer i =0; i<lh.size(); i++) {
              Location__c l = new Location__c ();
         

              //iterate through arrary to grab fields
              l.Name = lh[i].Name;    //Name is required for my Location records
              l.Merchant__r = m_holder;    //set reference to the m_holder built above
              l.Account__c = lh[i].OpptyAccount__c;   //Account reference is required for my Location records, I store it with a formula field OpptyAccount__c
              l.etc__c = lh[i].etc__c;   //etc. etc.  fields from my Location Holder

              allObjects.add(l);


               }  //end for i


          }//end if

     

     }//end for o

//once loop is done, add allObjects and check for errors

try {
insert allObjects;
} catch (system.Dmlexception e) {
system.debug (e);
}

}

Chris760Chris760

Looks awesome to me.  If you have any issues, then it's probably just a small syntax thing.  By the way, another method that some people use to "uniquify" external ID's is to combine the top level ID with a textual representation of the current date-time: String.valueOf(Opportunity.id)+String.valueOf(System.now()).  It can come in handy for weird scenarios where you're stuck, like Before Inserts where the parent ID hasn't been created yet and nor has the autonumber (auto numbers only get created after-insert).  

 

In scenarios where there's no ID whatsoever that you can use as your "base number", you can combine an iteration number (i++) with the current date-time string.  When I have to clone entire lists of master-detail though, I usually use a combination of the ID and autonumber, or ID and date-time, so that way the same relationships that existed in the original record, exist in the cloned hierarchies automatically.

 

Anyway, play with it, have fun with it, etc.  It looks like you totally got the concept though.  :)

s_k_as_k_a

Hi ekroz

 

That is very good approach. But, try to aviod SOQL query inside  for loop.Otherwise it will hit governor limitis .

 

ekorzekorz

The for loop that runs potentially several times is inside another for loop, but after the query.  Does my code run that query once for every locationholder that I have?  I thought I put it where it'd only run the query once per opportunity, and even then only if it qualifies based on my o.probability == 100.  I'm pretty new here, what governor limits would this potentially hit?  I may not need to worry about it in my business case, but I'd also like to learn to write the appropriate code.  

 

I guess, in short, what would you change?  I use query to pull all the LocationHolders from the opportunity.  What is the preferred way?

s_k_as_k_a

Hi,

 

If suppose you update 400 opportunities at a time, if more than 100 opportunies quilifies the Soql query inside for loop will excecute more than 100 times . Because for each opportunity it will run. So that you will get error " Too Many SOQL Queries :101" . See below link for governor limits in salesforce.

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm

 

So while writing any code you always consider that you code will work for bulk operations for insert update .

 

See below modified code for yoyr requirement.

 

trigger createMerchantAndLocationsFromOpportunity on Opportunity (after insert, after update) {

   
	 list <sObject> allObjects = new list<sObject>();
	 Map<Id, opportunity> applicableOppts = new Map<Id, opportunity>();
	 for(Opportunity opp : Trigger.new)
	 {
		 if(opp.Probability == 100)
		 {
		   applicableOppts.put(opp.id, opp);
		 }
	 }

	 Map<Id, List<Location_Holder__c>> oppToLocationholders = new Map<Id, List<Location_Holder__c>>();	 
	 for(Location_Holder__c loch : [Select Name, etc__c, OpptyAccount__c from Location_Holder__c WHERE Opportunity__c in :applicableOppts.keySet()])
	 {
		 if(oppToLocationholders.containsKey(loch.Opportunity__c) && oppToLocationholders.get(loch.Opportunity__c) != null)
		 {
				List<Location_Holder__c> templochs = new List<Location_Holder__c>();
				templochs = oppToLocationholders.get(loch.Opportunity__c);
				templochs.add(loch);
		 }else
				oppToLocationholders.put(loch.Opportunity__c, new List<Location_Holder__c>{loch});
	 }

     for (Opportunity o : applicableOppts.values()) 
	  {
          
         //build Merchant object first

         Merchant__c m = new Merchant__c ();

         // now map opportunity fields to new merchant object that is being created with this opportunity

         m.Name = o.AccountName__c;   //required field on my Mechant Object
         m.Account__c = o.AccountID;    //required reference for my merchant object
         m.etc__c = o.etc__c;    //etc. etc. fields on my Merchant object
         m.MyExtID__c = String.valueOf(opportunity.Id)+o.AutoNumber__c;    //set external id to text of opportunityid+auto-number

         //build blank Merchant m_holder with only External id being the op.id+autonumber above
         Merchant__c m_holder = new Merchant__c (MyExtID__c = String.valueOf(opportunity.Id)+o.AutoNumber__c);

         allObjects.add(m);   

         //build list of LocationHolders attached to this opportunity, load into variable
         if(oppToLocationholders.containsKey(o.id) && oppToLocationholders.get(o.id) != null)
		  {
			 for(Location_Holder__c lh : oppToLocationholders.get(o.id))
			  {
                 //iterate through the list of LocationHolders
                  Location__c l = new Location__c ();
                  l.Name = lh.Name;    //Name is required for my Location records
                  l.Merchant__r = m_holder;    //set reference to the m_holder built above
                  l.Account__c = lh.OpptyAccount__c;   //Account reference is required for my Location records, I store it with a formula field OpptyAccount__c
                  l.etc__c = lh.etc__c;   //etc. etc.  fields from my Location Holder

                  allObjects.add(l);
               }  //end for loop
		  }//end if
	  }//end for o

    //once loop is done, add allObjects and check for errors

    try {
        insert allObjects;
    } catch (system.Dmlexception e) {
          system.debug (e);
    }

}

 

 

ekorzekorz

Awesome!  

 

That also reminds me, I really only want this to fire when an opportunity closes for the first time, when it's a certain record type, and only if they're not brand-new (since otherwise it would fire before a user had the chance to build any related LocationHolders).  I should probably add a few more if's and make it fire after just update... not after insert.  

 

Anyway, bulk update operations still would apply, so I'll make some modifications like you so kindly contributed. 

 

 

 

Thanks!

Chris760Chris760

You'd be getting in to the part where I mentioned in my original post that I didn't want to overwhelm you with new stuff that probably wouldn't be needed in your case.  Essentially, if you were converting a bunch of opportunities, your trigger would hit the governor limits pretty quickly because your trigger has to do 2 SOQL queries for every opportunity where probability == 100.  So if you did 10 opportunities in one go, it means 20 SOQL queries.  If you did 100 in one go, it means 200 SOQL queries, and so on.

 

The preferred way to "Bulkify" your trigger would be to harvest all the master records needed to build all the Master-Detail record sets in 1 SOQL query, then query all detail records "IN:" (your Master record ID set).  This becomes your second SOQL query.  Then, since you've harvested ALL the detail records you'll be needing, you'll have only done 2 SOQL queries for your 100 records instead of 200 queries.

 

Then you organize them ahead of time in to sets of detail records that correspond to each master record, like organizing papers into different piles.  Then once you've organized them all, you'd do the step we did with the nested for loops at the end to build out the final records that would be added to your sObject list in the correct order.

 

So, like the way I essentially did this in my brochure app I was telling you about, was:

 

map<Id,set<Parent__c>> parentSets = new map<Id,set<Parent__c>>();

map<Id,set<Child__c>> childSets = new map<Id,set<Child__c>>();

list<sObject> allObjects = new list<sObject>();

 

for(Opportunity op : trigger.new){

 if(my conditions for building my parent-child records is true){

   parentSets.put(op.Id,null);

   }

}

 

for(Parent__c parent : [select Id, other fields I'll need to make my parent records, this, that from Parent__c where Opportunity__c IN: parentSets.keySet()]){

    set<Parent__c> setInstance = parentSets.get(parent.Opportunity__c) != null ? parentSets.get(parent.Opportunity__c) : new set<Parent__c>(); //if we've already added to this set, we're loading the records we've added previously into this FOR loop instance set so that we don't overwrite what we've already added.  If nothing exists already though, then we're creating a new instance set that we can add to.

    setInstance.add(parent);

    parentSets.put(parent.Opportunity__c,setInstance);  //if you put the same key back in to the mapped set, it will just overwrite the first key and corresponding value/set with this new one.  So we're just updating what existed with more complete information.

   }

}

 

for(Child__c child : [select Id, other fields I'll need to make my child records, this, that from Child__c where Parent__c IN: parentSets.keySet()]){

  set<Child__c> setInstance = childSets.get(child.Parent__c) != null ? childSets.get(child.Parent__c) : new set<Parent__c>();

  setInstance.add(child);

  childSets.put(child.Parent__c,setInstance);

  }

}

 

//Now that we have a mapped set of "Parents" and a mapped set of "Children", we can iterate through them in for loops and build out our master detail records...

 

for(Parent__c parent : parentSets.get(parent.Opportunity__c) != null ? parentSets.get(parent.Opportunity__c) : new set<Parent__c>()){ //we do this "null" verification IF statement because, in the off-coincidence that there are no values in our mapped set, it will crash the trigger because there will be no values to iterate through.  So this is for the weird scenario where there happens to be no related Parent-Child records that need to be made when your trigger runs.

//put together your parent records like you originally did, setting the external ID, etc.

allObjects.add(parent);

 

  //Create our single external ID instance like you originally did to inject in to the child.

 

  for(Child__c child : childSets.get(child.Parent__c) != null ? childSets.get(child.Parent__c) : new set<Parent__c>()){

  //put together your child records like you originally did, injecting the external ID in to the "__r" lookup, etc.

  allObjects.add(child);

  }

}


Essentially, you're just doing more "FOR" loop iterations than SOQL query iterations, which is considered a best practice since SOQL queries suck up a lot more resources than for loops do.

 

That's my understanding anyway of why we do nested mapped for loop iterations rather than nested SOQL for loop iterations, but s_k_a, you're welcome to add to this if you feel I'm leaving anything out.

 

If you're unfamiliar with maps or sets, refer to the MapsSetsMap Methods, and Set Methods sections of your handbook.  

 

By the way, the reason I usually use sets when organizing my records in preperation before my FOR loop iterations, is beause, if you add two of the same record ID's to a list (as opposed to a set), it will crash your trigger during the DML update because you can't do a DML on the same two ID's at the same time.  But sets will automatically overwrite the last record you added if it's the same ID value (which is rare, but it does happen occasionally on certain types of triggers depending on how you process the records).  So I just protect myself by storing records I need to organize in sets rather than lists.  Since you're doing a strict insert, it probably wouldn't be as big of a deal, but I'm just always in the habbit of organizing records in to sets so I never have to think about it.  Then when I need to prep my records for insert/update with DML, I convert them back to lists.

 

I'm curious to hear though how s_k_a would do it.  That's just my method like I said, but he might have a cooler way that I'm just not aware of. :)

Chris760Chris760

oh lol, whoops, I didn't realize that s_k_a had already responded on page 2... DOAH!  Looks like our thinking is pretty similar though.  About your additional if criteria for executing the trigger, using oldMap to compare the old value with the new value of a record is the equivelant of using workflows where you select "The first time this criteria is true" so that it doesn't run every time you do an update/insert.  It's essentially like "If oldvalue.Stage != Stage", then... kind of a thing.

 

Example:

 

//define container
Map<Id, Id> PropertyOwnerMap = new Map<Id,Id>();

//Collect accounts
for (Opportunity o : Trigger.new) {
if (((trigger.isUpdate) && (o.StageName == 'Closed - Other' || o.StageName == 'Closed - LAO')
&& (trigger.oldMap.get(o.Id).StageName != 'Closed - Other' & trigger.oldMap.get(o.Id).StageName != 'Closed - LAO'))
|| ((trigger.isInsert) && (o.StageName == 'Closed - Other' || o.StageName == 'Closed - LAO')))
if (o.Property_Name__c != null)
PropertyOwnerMap.put(o.Property_Name__c, o.AccountId);
}

 

Anyway, hope that helps. :)