Update child record based on parent external id

Question

I have two custom objects, the Rate Cards and the Rate Card Lines. What i need to do is to create rate card and rate card line records by deserializing two json files(one for the rate cards and one for the rate card lines). I have done the deserialization into two separate functions which return to my main function the records which i have to create. However, i need to connect my lines to my Rate Card records based on an external id(in Rate Cards). Also, a lookup field exists within the rate card lines object. When i try to populate the rate card line lookup with the external id, the field is not populated. Can you help me of how to accomplish it? My code bellow:

   for(Integer i=0; i< pConfList.size(); i++){
        String extId;
        for(attachment att: [select parentId, body, name from attachment where name like '%Rate Card%' and parentId =: pConfList[i].id]){
            //system.debug(att.Body.toString());
            
            
            if(/*pConfList[i].cscfga__Product_Definition__r.Name == 'Electricity' || */pConfList[i].cscfga__Product_Definition__r.Name == 'Custom Electricity'){
                if(att.Name.startsWithIgnoreCase('Rate Card Information')){
                    
                    //String pSubType = prodSubTypeList[j].cscfga__Value__c;
                    String pSubType = pConfList[i].el_Product_Sub_Category__c;
                    Id recordTypeRC = Schema.SObjectType.cspmb__Rate_Card__c.getRecordTypeInfosByName().get('Electricity').getRecordTypeId();
                    
                    list<cspmb__Rate_Card__c> createRateCard = el_CustomerApprovalTriggerHandler.deserializedRateCard(att.Body.toString(),
                                                                                                                      pConfList[i], pBasket[0].csordtelcoa__Account__c,
                                                                                                                      pSubType, recordTypeRC);
                    for(cspmb__Rate_Card__c RC: createRateCard){
                        toBeCreatedRCList.add(RC);
                    }
                    extId = createRateCard[0].el_External_Id__c;
                    
                    system.debug(extId);
                    system.debug(createRateCard);
                    system.debug(createRateCard[0].el_External_Id__c);
                }
                if(att.Name.startsWithIgnoreCase('Rate Card Line Information')){
                    Id recordTypeRCL = Schema.SObjectType.cspmb__Rate_Card_Line__c.getRecordTypeInfosByName().get('Electricity').getRecordTypeId();
                    
                    list<cspmb__Rate_Card_Line__c> createRateCardLines = el_CustomerApprovalTriggerHandler.deserializedRateCardLines(att.Body.toString(),
                                                                                                                                     pConfList[i], pBasket[0].csordtelcoa__Account__c,
                                                                                                                                     recordTypeRCL);
                    system.debug(extId);
                    cspmb__Rate_Card__c RCReference = new cspmb__Rate_Card__c(el_External_Id__c = extId);
                    system.debug(RCReference);
                    system.debug(RCReference.id);
                    
                    for(cspmb__Rate_Card_Line__c RCL:createRateCardLines){
                        system.debug(RCL);
                        system.debug(RCL.cspmb__Rate_Card__c);
                        RCL.cspmb__Rate_Card__c = RCReference.id;
                        system.debug(RCL.cspmb__Rate_Card__c);
                        toBeCreatedRCLList.add(RCL);
                        system.debug(createRateCardLines);
                    } 
                }
            }

The RCReference.id; is currently null while if i use the RCReference; it has the correct value but i get compile error.

Answer 1

All right, so I have never myself inserted/updated by external id, but from what I've read, there are a few issues that could be causing your problem.

First is a potential logic issue. As I'm reading your code, the value of extId is dependent on the order in which Salesforce returns attachments. The only time it is set is when it is processing a header record. So if Salesforce returns any line attachments before it returns one or more header attachments, then the line attachment will be processed using a null external id.

Second issue is also a potential logic issue related to grabbing the external id. This piece:

                    for(cspmb__Rate_Card__c RC: createRateCard){
                        toBeCreatedRCList.add(RC);
                    }
                    extId = createRateCard[0].el_External_Id__c;

This seems to suggest that a header attachment (with "Rate Card Information" name) may have multiple rate cards within it. First, if it ever contains zero rate cards, then you'll get a null reference error. Even if that's not a supported condition of the data, you should develop your code to be resilient against bad data. I'd restructure that by either putting it in the loop (which will grab the last external id) or protect it with an if statement (!createRateCard.isEmpty()).

The other potential problem with the above code structure is that if there are multiple rate cards with different external ids, then all lines following this will correlate to only the first card, even if multiple were created. I don't know what your data looks like, so that may not be a concern, but I wanted to mention it.

The third issue is with this line, as you pointed out:

RCL.cspmb__Rate_Card__c = RCReference.id;

Like you said, the id will be null at this point. From what I'm reading in the docs, to insert using an external id, you'll need to load an actual rate card instance into the rate card relationship property, rather than the id property. It would need to change to this:

RCL.cspmb__Rate_Card__r = RCReference;

Then you just need to make sure the rate cards are inserted first. If you want to get crazy fancy, you can also insert the headers and lines all in one statement.

Hope that helps!

EDIT relating to this comment:

Hello, Nathanael and really thank you for you response. To be honest i iterate through the product configuration list (pconf) and for each of those records i need to create a rate card record and it's lines. So i need to pick for every rate card generated it's extid. I used the [0](first record) as i cannot figure out how to pick it for each rate card record and assign it to extId. Can you propose something for this? Thank you!

Sure thing! :-) So it sounds like at this point you may not yet be in the habit of using map objects; these are probably what are going to help the most here. Maps allow you to associate one value with another, so that you can use it to quickly look up data related to a common identifier (like a salesforce id or external id), and tie it together.

You might start with this:

// Initialize Your Main Collections
Set<Id>                                 pConfIds           = new Set<Id>();
Attachment                              pConfAttachments   = null;
Map<String, cspmb__Rate_Card__c>        rateCardsByKey     = new Map<String, cspmb__Rate_Card__c>();
Map<String, cspmb__Rate_Card_Line__c[]> rateCardLinesByKey = new Map<String, cspmb__Rate_Card_Line__c[]>();
cspmb__Rate_Card_Line__c[]              allRateCardLines   = new cspmb__Rate_Card_Line__c[]();

// Query Attachments.
// One other issue I didn't notice on my first read-through of the code is that
// you're running multiple attachment queries, one for each pConf. This is a 
// bad practice in Salesforce that they punish with SOQL Query 101 errors and
// the like. Best practice is to run 1 query for all pConfs, then filter the
// attachments by pconf. My assumption in this code is that the external id is
// unique across all pConfs (e.g. the same id isn't used in two different pConfs
// for different rate cards). If that's a wrong assumption, then this code will
// need to be refactored / become a little more complex.
for(Integer i = 0; i < pConfList.size(); i++) {
  pConfIds.add(pConfList[i].Id);
}
pConfAttachments = [select parentId, body, name from attachment where name like '%Rate Card%' and parentId IN :pConfIds];

// Process Header Attachments
for (attachment att : pConfAttachments) {
  if (att.Name.startsWithIgnoreCase('Rate Card Information')) {
    processRateCardAttachment(att, rateCardsByKey);
  }
}

// Process Line Attachments
for (attachment att : pConfAttachments) {
  if (att.Name.startsWithIgnoreCase('Rate Card Line Information')) {
    processRateCardLineAttachment(att, rateCardsByKey, rateCardLinesByKey);
  }
}

// Create Records
insert rateCardsByKey.values();
for (cspmb__Rate_Card_Line__c[] rateCardLines : rateCardLinesByKey.values()) {
  allRateCardLines.add(rateCardLines);
}
insert allRateCardLines;

Then you would need to define the two attachment processing functions. I'll try to give you an outline of each, but unfortunately I'm not familiar with the data structure of what's being stored in JSON, so your mileage may vary.

In particular, I don't know what data is available on the rate card line that would allow you to link it to the header. There would need to be some sort of key field (perhaps the external id of the header); otherwise it is impossible to know which lines belong to which headers.

The sample code below assumes that there is some sort of a key besides the external id of the header, since you didn't use that in your original code. If the external id of the header is also available on the line, then the code can be simplified to just use lists rather than maps, because you already have all the data you need while processing the line; you don't need a reference to the header. Assuming that some other key is used, the code might look like the following:

@testVisible private static void processRateCardAttachment(Attachment att, Map<String, cspmb__Rate_Card__c> rateCardsByKey) {
  ...
  list<cspmb__Rate_Card__c> rateCards = el_CustomerApprovalTriggerHandler.deserializedRateCard(...);
  for(cspmb__Rate_Card__c rateCard: rateCards) {
    String key = rateCard.el_External_Id__c; // or whatever key exists in the data to link lines to headers
    rateCardsByKey.put(key, rateCard);
  }
}

@testVisible private static void processRateCardLineAttachment(Attachment att, Map<String, cspmb__Rate_Card__c> rateCardsByKey, Map<String, cspmb__Rate_Card_Line__c[]> rateCardLinesByKey) {
  ...
  list<cspmb__Rate_Card__c> rateCardLines = el_CustomerApprovalTriggerHandler.deserializedRateCardLines(...);
  for(cspmb__Rate_Card__c rateCardLine: rateCardLines){
    // Get Lines Related To Header
    String                     headerKey   = rateCardLine.el_External_Header_Id__c; // or whatever key exists in the data to link a line to its header
    cspmb__Rate_Card__c        rateCard    = rateCardsByKey.get(headerKey)
    cspmb__Rate_Card_Line__c[] headerLines = rateCardLinesByKey.get(headerKey);
    
    if (rateCard    == null) continue; // or throw an error
    if (headerLines == null) rateCardLinesByKey.put(headerKey, headerLines = new cspmb__Rate_Card_Line__c[]());
    
    rateCardLine.cspmb__Rate_Card__r = new cspmb__Rate_Card__c(el_External_Id__c = rateCard.el_External_Id__c);
    headerLines.add(rateCardLine);
  }
}