How do i Upsert record in Parent- child relationship from custom to custom object Using External ID

Question

We have data integration Job for SQL SERVER - SFDC using Talend data integration tool.

SFDC environment:

Full sandbox Account

We have two custom object.

In that, Master-Detail relationship from Order_item__c to Order__c

 Order__c and  Order_item__c

External id : is composition of two column

that is

 ext_ID__C IS concat(salesorder,salesorderline)

Detailed structure of Order__c and Order_item__C is

Schema of Order__c

Order__c    ext_Id__c - External ID
    Customer__c
    Status__c
    Order_Date__c
    Req_Ship_Date__c
    Salesperson__c
    Customer_PO_Number__c
    SYSPRO_Entry_System_Date__c
    Inter_Warehouse_Sale__c

Schema of Order_item__c
    Order_Item__c   Order__c
    Order_Line_Number__c
    Warehouse__c
    Quantity__c
    Price__c
    Inventory_Item__c
    Disp_Qty__c
    Order_Value__c
    Ord_Qty_In_Each
    Disp_Qty_In_Each__c
    SYSPRO_Id__c
    Dispatch_Date__c
    Invoice_Date__c

Scenario is:

For Outputting the Order line item : The problem is we need to upsert the external ID in Order_Item__c. But to upsert this we need to go find the Sales force Order(which is available in the SQL SERVER view) first then traverse down to the Order Line Number. We are finding difficulty at the time of traversing into Multiple Line items for order.

We would like to Understand How do i UPSERT into Multiple Line item entries using Order_item__c and what will be the external Id in this case so that it does not allow duplicate as well.

Any help on it would be much appreciated !

Thanks in advance !

Answer 1

In this scenario, from your description, the Master is Order__c and the Detail is Order_Item__c. From there, it would seem that Order_Line_Number__c is then a child of Order_Item__c or are they the same? That part is a bit unclear. My sense from what I'm seeing is that they're the same.

You already know that Order__c has an external ID field named ext_Id__c which is your concatenated field. Your schema shows this under Order__c, but it actually applies to Order_Item__c. In a M-D relationship, the child always has a field with the Id of the Master record, which in this case would be Order__c.

It's my sense that your external Id is set up to give you both the Order__c and the Order_Item__c; thus the concatenation. Otherwise, you'd have multiple external Ids for Order__c, so it must actually be the external Id that's meant to be applied to Order_Item__c instead.

I would recommend you try using the concatenated field as your external Id for Order_Item__c as it cannot possibly be the external Id for Order__c if it also contains ALL of the Order Items too as opposed only one of them.

Edit in response to comments

First, I'd highly recommend you go through the Trailhead Integration Module.

I'd suggest you try including the Master record Id to establish the link to both the Master record and the detail record since that Id is referenced on both of those records. How you put them in your SOAP or REST call will be important if you try to do it that way.

If using data loader, it would make sense to use a foreign key for the line item record. What's not clear to me is whether or not you have multiple Ids in the Master Record. Is that a many to 1 relationship? Your schema would indicate that it is, but perhaps not. If it's not, then you'd only need the additional foreign key to use for line_item. Again, I recommend the trailhead Integration Module. The final one will help you see how some of this can work using REST.