Trigger upsert questions

Question

I am trying a bunch of things with this trigger code and was wondering if anybody could give some input on this :

The main elements :

Parent Object : Contract_Overview__c Field : Subsidiaries_On_Contract__c (multi-value text)

Child Object : Subs_Serviced_On_Contract__c Fields : Contract_Over Subsidiary_Name__c : one of the values stripped from Subsidiaries_On_Contract__c in parent Contract_and_Sub : field used as External ID (Contract + Sub name)

I am attempting to upsert a new child record for each value in the Subsidiaries_On_Contract__c field on the Contract_Overview__c object every time a Contract_Overview__c is created or updated using this code :

trigger AutoCreateSubs on Contract_Overview__c (after insert, after update) {
 List<Subs_Serviced_On_Contract__c> subs = new List<Subs_Serviced_On_Contract__c>();

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

    //Subs_Serviced_On_Contract record for the specified Subsidiaries_On_Contract__c.  

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

    //that are being created.  

    for (Contract_Overview__c newContract : Trigger.New) {
        if (newContract.Subsidiaries_On_Contract__c != null) {
            // split out the multi-select picklist using the comma delimiter
            System.debug('Subsidiaries_On_Contract__c ' + newContract.Subsidiaries_On_Contract__c);
            for(String subsoncontract: newContract.Subsidiaries_On_Contract__c.split(',')){
                subs.add(new Subs_Serviced_On_Contract__c(
                        Name = newContract.Name,
                        Contract_Overview__c = newContract.Id,
                        Subsidiary_Name__c = subsoncontract,
                        Contract_and_Sub__c = newContract.Name + '~' + subsoncontract,
                        Logo_Usage_Allowed__c = 'Yes'));
            }
        } 
    }
    upsert subs;

}

Right now, it will properly create a new child record for each value which it gets from the Subsidiaries_On_Contract__c upon create or update of a parent (Contract Overview). It then puts that value into the Subsidiary_Name__c field in the child.

However, it will give me this error whenever I attempt to create or update a new Contract Overview which contains a value in its Subsidiaries_On_Contract__c which has already had a child record created :

Insert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, AutoCreateSubs: execution of AfterInsert caused by: System.DmlException: Upsert failed. First exception on row 0; first error: DUPLICATE_VALUE, duplicate value found: Subsidiary_Name__c duplicates value on record with id: a11W0000000kG4W: [] Trigger.AutoCreateSubs: line 26, column 1: []
Error is in expression '{!Save}' in component <apex:page> in page contractoverviewsfdc

I don't understand why I am getting this error. Also, I want to use the Contract Name+Subsidiary Name as the key for the upsert to identify if a child record for a Sub already exists for that particular Contract - and to update an existing one instead of creating a new one.

I've been trying to get this to work for days now, but I'm totally stumped.

Can anybody give me some help on this ?

Thank you very much for your time and effort.

Answer 1

It would seem like the Subsidiary_Name__c is a Unique field (possibly External Id) on Subs_Serviced_On_Contract__c. Navigate to the field and check to see if the Unique checkbox is checked.

If so uncheck it.

If however it is only an external id, then you need to explicitly specify the field to use that as an external id, else the salesforce id is used for the upsert.

upsert subs Subsidiary_Name__c;

To create a composite key, create a new Text field, mark it as External Id and Unique. Then create a workflow, which populates this field to be Contract Name+Subsidiary Name using a field update.