7

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.

2
  • 1
    You could use the parent id if it actually existed as an external id custom field on your child record so you'd have to write code or workflow to populate that field. It's worth noting that the only way this is a reasonable direction is because of your pseudo 1-to-1 relationship, it wouldn't make sense in a true one-to-many, which M-D is. – greenstork Sep 25 '13 at 14:03
  • @greenstork, thx for your advice, I think that is the best workaround. – Martin Borthiry Sep 26 '13 at 6:07
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.

0

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.

1
  • thank you for your answer, I did this question long time ago. Let'me check how finally I solved the issue. What are you saying makes sense. – Martin Borthiry Dec 26 '13 at 8:37
-1

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.

3
  • Kevin that is not possible, I've tried before but I have this error msg: Invalid field for upsert, must be an External Id custom or standard indexed field: parentA_Id__c . I think that master-detail relation is not an standard indexed... sadly I cant change it as external Id – Martin Borthiry Sep 25 '13 at 12:33
  • @MartinBorthiry, Oh bother. Sorry about that. this is what I get for perusing SE before coffee. Lets see if I can't in some way redeem myself. putting forward a new answer. – Kevin P Sep 25 '13 at 16:59
  • Please Kevin there is not need to say sorry. I'm totally thankful for your intention – Martin Borthiry Sep 25 '13 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.