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 !
Best Answer
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.