Upsert Detail object without external Id

Question

I have two related objects (Master-Detail) A -> B (where B is child of A). In addition there is only one B for a parent A.

I need to upsert the B object and I only have the A.Id and other fields of B. But not the B.Id.

I was trying this:

List<B__c> listToUpsert = new List<B__c>();
for(BObject dataB: listOfDataB){
   B__c b = new B__c();
   b.parentA_Id__c = dataB.parentA_id;
   b.otherField__c = dataB.otherField;   

} 

upsert listToUpsert;

This works, but is always inserting new records.

Since I have not chance to know the previous B.id or generate an external Id on B relation to A in the original data to upsert. The only workaround that I think is to query first the existing B then update those A.id and finally upsert.

Sample

//build a set of B.client ids
Set<Id> existingBsIds = new Set<Id>();
for(BObject b: listOfDataB){
    existingBsIds.add(b.parentA_Id__c);
}
//get existing
Map<Id, B__c> existingBsMap = new Map<Id, B__c>();
//build existing map
for(B__c b:[SELECT id,parentA_Id__c FROM  B__c WHERE parentA_Id__c in :existingBsIds]){
    existingBsMap.put(b.parentA_Id__c, b.id);
}

//update id to upsert
B__c b; 
for(BObject dataB: listOfDataB){
    b = new B__c(id = existingBsMap.get(dataB.parentA_Id));
    b.parentA_Id__c = dataB.parentA_id;
    b.otherField__c = dataB.otherField; 
    BsToUpsert.add(b);
}

upsert BsToUpsert;

Question

Is there other better approach to upsert a child object using the parent id as external Id?

Update

Another idea could be to have an extra text field as external Id, and fill it with the parentA_id using a trigger.

Answer 1

If I understand correctly, you have a list ? If this is the case, what happens if you cast the list to a map? This is going to be highly dependent on the context of execution but this might help.

Map<Id,bObj__c> mapOfAwesome = new Map<Id, bObj__c>(bObjectList);

Again, not knowing the context of execution this could be another red herring.

Answer 2

Sounds doable.

B is child of A and there can be at most only 1 B for each A? And you know the A.Id?

The "brute" approach would be to fetch all B's for the A's you're interested in into a list and then insert/update them as needed. Something like this:

List<A__c> masters; // Assuming that it's a valid variable that holds (among others) the A Ids. Can be also a set or a map, doesn't matter much.

List<B__c> children = new List<B__c>();

for(A__c a : [SELECT Id, (SELECT Id, A__c FROM B_Children__r LIMIT 1) FROM A WHERE Id IN :masters]){// assuming the B_Children is the relationship name
    B__c oneKid;
    if(!a.B_Children__r.isEmpty()){
        oneKid = a.B_Children__r[0]; // we found one so lets fetch it.
    } else {
        oneKid = new B__c (A__c = a.Id);
    }
    oneKid.foo__c = "bar"; // make the updates of other fields as needed here
    children.add(oneKid);
}
upsert children;

---

The "clever" approach would be to use upserts ;) There's more preparatory work and data fixes to run but it'll simplify the logic.

1. Make a new external Id field on B. Text(15). Mark it unique case sensitive.
2. Make a workflow rule that copies the A Id from the lookup field to this text field. Can run on every insert and when ischanged(A__c). Remember to activate it!
3. Run a data fix that populates the field... If the workflow runs ok something as simple as this should be enough: update [SELECT Id FROM B__c WHERE Ext_Id__c = null AND A__c != null LIMIT 10000];. Run it couple times if needed.

So, now that we do have the ext.id field and the value = A.id:

• it's unique so upsert should never fail (shouldn't ever return more than 1)
• the value is A.id - but since there will be at most 1 B for each A - that's cool.

I see no reason why upsert listofBs My_Ext_Id_field__c; shouldn't work.

Answer 3

You can use the Database.Upsert method rather than the Upsert method. If you use Database.upsert, you can specify the external id. something like this:

Schema.SObjectField f = b.parentA_Id__c;
Database.UpsertResult [] results = Database.upsert(ListOfBObjs , f, false);

Cheers.