How can I not update records using this Upsert?

Question

I am attempting to create a new child record for each value of the multi-value field. I can only get the following code to create a new child record for the first entry into the list :

//Data fields and objects :

//Policy_Profile__c - the parent object which is kicking off this trigger

//Product_Affected_Entry__c - child object which will be used for new records

//Product_Affected3__c - field in parent object populated with selected options

//Vendor_Product__c - object loooked up and containing the profile(recordid) of the entry Named in the newly created child record
//***SHOULD HAVE BEEN NAMED VENDOR_PRODUCT_PROFILE__C in this case because it now has the same name as the child record field which
//contains the record id 

//Policy__c - lookup field on newly created child record which will be used to contain the Parent record's ID

//Vendor__c - lookup field on newly created child record which is pulling another lookup field from the parent object 
//which contains the id to the PARENT of the entry Named in the newly created child record - ie the object queried 
//in the VF controller to produce the multi-select choices - in this case the parent of the Vendor_Product__c object

//Vendor_Product__c - lookup field on the newly created child record which contains the recordid of the entry Named in the newly created record
//ie it is the field in the new child record which is populated by the result of the query in this trigger
//it is the recordid of one of the selections which appear in the names list of this trigger
//****CAUTION**** - in this case it has the same name as the object looked up

//Policy_and_Product__c - text field in the newly created child object which contains a combination of the name 
//of the selection which appears in the names list as well as well as the recordid retrieved in the query - in 
//this case it is the recordid plugged into the Vendor_Product__c field of the newly created child record
//****VERY IMPORTANT***** - THIS MUST BE AN EXTERNAL ID FIELD BECAUSE IT IS REFERENCED BY THE UPSERT TO PREVENT 
//DUPLICATE CHILD RECORDS FROM BEING CREATED IF A SELECTION IS MADE MULTIPLE TIMES BY THE USER

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.normalizeSpace();
            temp = temp.replace(']','');
            temp = temp.replace('[','');
            String[] all = temp.split(',');
            subAccNames.addAll(all);
            System.debug('************************temp'+temp);

            for(String acctName : all)
            {
                subAccNames.add(acctName.normalizeSpace());
                System.debug('subAccNames !!! ' + subAccNames); 
            }
        }
    }


    Map<String, Vendor_Product__c> subAccIdsByName=new Map<String, Vendor_Product__c>();
    System.debug('FIRSTsubAccIdsByName='+subAccIdsByName);   

    for (Vendor_Product__c subacc : [select id, Vendor__c, Name from Vendor_Product__c where Name in :subAccNames]) 
    {

        subAccIdsByName.put(subacc.Name, subacc);
        System.debug('subAcc Name and ID=' + subacc.Name +'Id=' + subacc.id + 'Vendor_c=' + subacc.Vendor__c);
    }

    System.debug('SECONDsubAccIdsByName=' + subAccIdsByName);

    //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
            System.debug('Products_Affected3__c ' + newContract.Products_Affected3__c);

            String temp = newContract.Products_Affected3__c;


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

            for(String productsonpolicy: all)
            {

                for(Integer i=0; i<subAccIdsByName.keySet().size(); i++)
                {

                    if(productsonpolicy==(new List<String>(subAccIdsByName.keySet()))[i])
                    {
                        Product_Affected_Entry__c ssoc = new Product_Affected_Entry__c(
                            Policy__c = newContract.Id,

                            Vendor_Product__c = subAccIdsByName.values()[i].Id, 

                            Vendor__c = subAccIdsByName.values()[i].Vendor__c,

                            Policy_and_Product__c = newContract.Name + '~' + (new List<String>(subAccIdsByName.keySet()))[i]
                        );
                        subs.add(ssoc);
                    }
                }
            }
        } 
    }

    upsert subs Policy_and_Product__c;
}

Any kind of help I can get I would be very grateful. Thank you.

Answer 1

You've already got it in your code that the Policy_Product__c field is used to prevent dupes with upsert.

Collect all of those values and run a query on Product_Affected_Entry__c. If any of them are returned, remove matching new Product_Affected_Entry__c's from your list named subs. To make that easier, create a map with those values against something else that's unique if you can since your list has no Ids.

You already have the map subAccIdsByName which might allow you to run a query ahead of time using either .values() or .keyset(). Since I don't know the relationships and fields in your object schema, I can't say one way or another.

Edit

Look for comments encapsulated with '***' in the revised code below.

                    map<string,Product_Affected_Entry__c>PolPrd2PAE = new map<string,Product_Affected_Entry__c>();
                    // ***declare the above map outside of a for loop*** 

                    for(String productsonpolicy: all)    
                     {   
                        productsonpolicy = productsonpolicy.normalizeSpace();

                        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), 

                        //Policy_Poroduct__c field is used as key to prevent duplicates with the Upsert
                        Policy_and_Product__c = newContract.Name + '~' + subAccIdsByName.get(productsonpolicy)
                        );

                        //***Add ea new Policy_and_Product__c as the key to a map with*** 
                        //***ea new Product_Affected_Entry__c record as the value***
                        PolPrd2PAE.put(Policy_and_Product__c, ssoc);

                        subs.add(ssoc);
                     }

With the above revisions, you can now you can write a query using WHERE Policy_and_Product__c IN :PolPrd2PAE.keyset() to get a list of any previously inserted records related to the Parent Record Ids in Trigger.new. I'm confident that you can get the Parent Ids easily enough yourself and add them to the WHERE condition if they're needed to write the rest of the query.

Once you do that, here's how to find the matches and remove them from subs:

list<Product_Affected_Entry__c>QueryResults = [SELECT Id, Policy_and_Product__c, etc FROM Product_Affected_Entry__c WHERE .... ];
for(Product_Affected_Entry__c QR : QueryResults)
{
    for(Product_Affected_Entry__c s : subs)
    {

        if(QR.Policy_and_Product__c = s.Policy_and_Product__c){
            subs.rem(s);
        }
    }
}