+ Start a Discussion

If there a better method than Upsert for this code ?

I have an AFTER INSERT, AFTER UPDATE trigger which Upserts a new record for each value in a multi-value field. It is also putting a value from a field in the current record (Vendor__c) into one of the fields of the newly created records (VendorNewRecord__c). If a new value is ever added to the multi-value field then it will create a new record for that value using the Upsert. I'm using Upsert because I don't want to create duplicate records every time the record is saved.

However, I don't want it to update the records which have already been made. I only want it to use Upsert to make sure it has already created a record for the value in the multi-value field.

Is there a way to use Upsert to only check if there is already a record made, but not actually update it ?

Here is the actual code which I have for this task :
trigger AutoCreateSubsServOnContrOv on Policy_Profile__c (After insert, after update) 
           List<Product_Affected_Entry__c> subs = new List<Product_Affected_Entry__c>();

           List<String> subAccNames=new List<String>();

           for (Policy_Profile__c newCont : Trigger.New) 
              if (newCont.Products_Affected3__c != '[]') 
                 // split out the multi-select picklist using a comma delimiter
System.debug('Products_Affected3__c ' + newCont.Products_Affected3__c);

                 String temp = newCont.Products_Affected3__c;
                 temp = temp.replace(']','');
                 temp = temp.replace('[','');
                 String[] all = temp.split(',');

                 for (String acctName : all) {



           // get the ids for all vendor products and store in a map keyed by name
           Map<String, Id> subAccIdsByName=new Map<String, Id>();
           for (Vendor_Product__c subacc : [select id, Name from Vendor_Product__c where Name in :subAccNames]) 
                      subAccIdsByName.put(subacc.Name, subacc.id);


           //For each position processed by the trigger, add a new  

           //Product_Affected_Entry__c record for the specified Products_Affected3__c.  

           //Note that Trigger.New is a list of all the new positions  

           //that are being created.  

           for (Policy_Profile__c newContract : Trigger.New) 
              if (newContract.Products_Affected3__c != '[]') 
                 // split out the multi-select picklist using a comma delimiter

                 String temp = newContract.Products_Affected3__c;
                 temp = temp.replace(']','');
                 temp = temp.replace('[','');
                 String[] all = temp.split(',');

                 for(String productsonpolicy: all)


                    productsonpolicy = productsonpolicy.normalizeSpace();
System.debug('productsonpolicy in last FOR loop making the NEW records FOR*****'+productsonpolicy);
                    //for(String productsonpolicy: newContract.Products_Affected3__c.split(',')){

                    Product_Affected_Entry__c ssoc = new Product_Affected_Entry__c(

                            Policy__c = newContract.Id,
                            VendorNewRecord__c = newContract.Vendor__c,
                            Vendor_Product__c = subAccIdsByName.get(productsonpolicy), // GET THE SUB ACCOUNT ID BASED ON NAME
                            Policy_and_Product__c = newContract.Name + '~' + subAccIdsByName.get(productsonpolicy)); 


           upsert subs Policy_and_Product__c;


Thank you very much for any help you can give.
Shashikant SharmaShashikant Sharma

The way your code flow looks to do
  1. Create one Product_Affected_Entry__c record for the Policy_and_Product__c 
  2. If record already exits then update it
I am sure Policy_and_Product__c is an externalId field.

If your requirement is to stop the update and only to do insert then you have to
  1. Query the Product_Affected_Entry__c from database
  2. Create a map of Policy_and_Product__c and respective Product_Affected_Entry__c
  3. Use the map to find if record exists or not
  4. If exists then do not do any thing
  5. If not in map then add to list of records to be inserted.
See similar example where external id is used for upsert if you need to understad:


Shanshikant - yha Policy_and_Product__c is an externalID field.

After reading the examples you showed I am still unsure how to do it. I was hoping one of those examples would show how to properly do an IF exists then do not do anything type of line in there....

Anirudh SinghAnirudh Singh

Yes, there is a way if you don't want to update the already created records and only want to insert the new ones.
You can use the External Id field to find out which records are already present or created and which ones are new i.e. needs to be created and then insert the new records instead of upserting.

I have modified the Trigger and explained what I have done. Please find below the Trigger:
trigger AutoCreateSubsServOnContrOv on Policy_Profile__c(after insert, after update)
    //Since, we need two values from Policy_Profile__c to be populated on the new Product_Affected_Entry__c record.
    //So, taking a Map instead of list. Earlier, List<String> subAccNames=new List<String>();
    //Map of subAccountNames and Policy_Profile__c Record.
    Map<String, Policy_Profile__c> subAccNames=new Map<String, Policy_Profile__c>();
    for(Policy_Profile__c newCont: Trigger.New) 
        //Checking of the field is empty or not.
        if(newCont.Products_Affected3__c!=Null && newCont.Products_Affected3__c!='') 
            String temp=newCont.Products_Affected3__c.normalizeSpace();
            //temp=temp.replace(']',''); //No need for this if comma seperated values as I have taken.
            //temp=temp.replace('[',''); //No need for this if comma seperated values as I have taken.
            //Iterate the number of values in the temp variable by slitting them by comma
            //add put them in subAccNames
            for(String acctName: temp.split(','))
                subAccNames.put(acctName.normalizeSpace(), newCont);
                system.debug('subAccNames !!! '+subAccNames); 
    //Take a Map of Name and Record for holding Vendor_Product__c Name and Vendor_Product__c Record.
    Map<String, Vendor_Product__c> subAccIdsByName=new Map<String, Vendor_Product__c>();
    //Iterate over the Vendor Product records and create a Map with Venfor Product Id as Key and Vendor Product Record as Value.
    for(Vendor_Product__c subacc: [SELECT Id, Vendor__c, Name FROM Vendor_Product__c WHERE Name IN :subAccNames.keySet()]) 
        //Putting name and record pair in the map.
        subAccIdsByName.put(subacc.Name, subacc);
        System.debug('subAcc Name and ID='+subacc.Name +'Id='+subacc.id+'Vendor_c='+subacc.Vendor__c);
    //This will hold all the Product_Affected_Entry__c records.
    List<Product_Affected_Entry__c> subs = new List<Product_Affected_Entry__c>();
    //This Set will hold all the External Ids(Policy_and_Product__c) for the Product_Affected_Entry__c records.
    Set<String> externalIds=new Set<String>();
    //Iterating over subAccNames Map.
    //No need to iterate again over Policy_Profile__c records as we have already taken the Policy_Profile__c record in the subAccNames Map.
    //Earlier: for (Policy_Profile__c newContract : Trigger.New)
    for(String ref1: subAccNames.keySet()) 
        //Iterate over the subAccIdsByName Map.
        for(String ref2: subAccIdsByName.keySet())
            //Match if the Name in the subAccNames Map is equal to Name in the subAccIdsByName Map.
                Product_Affected_Entry__c ssoc = new Product_Affected_Entry__c();
                //Access Vendor Product Id from the Map.
                //Access Vendor__c from the Map.
                //Access Name from the Map.
                //Put the records in the subs list.
                //Adding the External Id Policy_and_Product__c to the list externalIds.
    //This Set will hold the externalIds(Policy_and_Product__c) for which Records have already been created.
    Set<String> externalIdsHavingRecords=new Set<String>();
    //Since, you are using External Id field. It can be used to check whether the Product_Affected_Entry__c records exist for an external Id or not.
    for(Product_Affected_Entry__c prodAffectedRef: [SELECT Policy_and_Product__c FROM Product_Affected_Entry__c WHERE Policy_and_Product__c IN :externalIds])
    //This will hold the new Product_Affected_Entry__c records to be inserted.
    List<Product_Affected_Entry__c> subsFinal=new List<Product_Affected_Entry__c>();
    //Iterate the subs list and check whether for an External Id a record is already present or not.
    //It not, then it is a new record.
    for(Product_Affected_Entry__c prodAffectedRef: subs)
    //Insert the new records.
    insert subsFinal;

Please let me know if this helps.
If yes, please mark the Question as Solved.

Thanks and Regards,
Anirudh Singh