+ Start a Discussion
Zoom_VZoom_V 

Upsert with

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.

 

Can anybody give me some input  on this ?

Thank you very much for your time.

Best Answer chosen by Admin (Salesforce Developers) 
ashishkrashishkr

Let's assume that newContract.Name field is unique for every contract(parent.) Even then a possibility arises that the subcontract will not be unique during updates. (uniqueness is required since the Subsidiary_Name__c is an external-id.)

 

Example: I create a Contract_Overview__c with name=cid-123 and Subsidiaries_On_Contract__c as x,y,z.  Your trigger would create 3 records on Subs_Serviced_On_Contract__c with external-ids as x,y and z.

 

Now when you update cid-123 and put A,y,m in Subsidiaries_On_Contract__c field, the trigger would try to insert 3 records with external-ids A,y and m. This is when you would receive a DUPLICATE_VALUE error.

 

To upsert using an external-id, you need to specify the field with the upsert command as below:

 

   upsert subs Subsidiary_Name__c;

 

 

All Answers

ashishkrashishkr

Let's assume that newContract.Name field is unique for every contract(parent.) Even then a possibility arises that the subcontract will not be unique during updates. (uniqueness is required since the Subsidiary_Name__c is an external-id.)

 

Example: I create a Contract_Overview__c with name=cid-123 and Subsidiaries_On_Contract__c as x,y,z.  Your trigger would create 3 records on Subs_Serviced_On_Contract__c with external-ids as x,y and z.

 

Now when you update cid-123 and put A,y,m in Subsidiaries_On_Contract__c field, the trigger would try to insert 3 records with external-ids A,y and m. This is when you would receive a DUPLICATE_VALUE error.

 

To upsert using an external-id, you need to specify the field with the upsert command as below:

 

   upsert subs Subsidiary_Name__c;

 

 

This was selected as the best answer
Zoom_VZoom_V

That did the trick Ashish - thanks so much !

One more question : How can I eliminate the [ ] surrounding the values in Subsidiaries_On_Contract__c ?

I know I want to do something like this with the string :

 

subsoncontract = subsconcontract.replace(']','');

subsoncontract = subsoncontract.replace('[','');

 ...but I can't really figure out the right place to put it

Thanks again !

ashishkrashishkr
Bringing the split outside the for loop should be good enough. Something like this:
String temp = newContract.Subsidiaries_On_Contract__c;
temp = temp.replace(']','');

temp = temp.replace('[','');

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

for(String subsoncontract: all){
// rest is the same code.
Zoom_VZoom_V

Thank you so much Ashish ! I appreciate that so much !

 

Take care.

Zoom_VZoom_V

ok - one last thing : It looks like it's having problems getting rid of the space after the columns.

So, the values from a field are coming out like this : 

Field : [Red, Blue, Green, Yellow]

 

Comes out like this : 

Red

 Blue

 Green
 Yellow

 

I think it's from the space after the columns, but I'm not sure. I'm trying to see if the code maybe actually inserts the space when it interates through the list. You got any ideas ? 

I really appreciate your time & effort.

Thank you.

ashishkrashishkr
SFDC truncates leading and trailing whitespaces while importing data using dataloader. But in this case you'll have to explicitly do that. Try subsoncontract = subsoncontract.normalizeSpace() before assigning it to the subsidiary name.
Zoom_VZoom_V

Yes, that is it Ashish. You got it again - thanks so much ! 

 

Take care.