tSalesforceOutput - Upsert - Lookup Relationship - Master/Detail - upserting Details with External ID only

Five Stars

tSalesforceOutput - Upsert - Lookup Relationship - Master/Detail - upserting Details with External ID only

Hi,

I'm trying to use the tSalesforceOutput module to upsert detail records, without using a tSalesforceInput, and I only have the external IDs available from the source database.

 

My example is as follows:

 

Master__c is the master object, and it has an external ID field Master_External_ID__c.

Detail__c is the detail object, and it has a Master/Detail Lookup field called Master_ID__c onto the Master__c object, as well as the Masters_External_ID__c, for reference purposes directly within that table (redundant, I know, but helps for debugging for now).

 

In Talend, the source schema has other fields to be upserted into Detail__c, as well as a field called Masters_External_ID, which I need to use to establish the master/detail relationship.

 

This is how I currently populated the Advanced Settings Relationship Mapping for upsert table:

Column Name for Talend Schema: Masters_External_ID

Lookup Field Name: "Masters_External_ID__c"

Lookup Relationship field name: "Id"

Module Name: "Master__c"

External id name: "External_ID__c"

 

I'm getting the following error:

Exception in component tSalesforceOutput_1 (PushDetailsIntoSF)
java.io.IOException: com.sforce.ws.SoapFaultException: Unexpected element {urn:sobject.partner.soap.sforce.com}type during simple type deserialization ......


Accepted Solutions
Sixteen Stars TRF
Sixteen Stars

Re: tSalesforceOutput - Upsert - Lookup Relationship - Master/Detail - upserting Details with External ID only

For lookups based on custom fields, replace __c by __r (for relationship).
In your example, the Lookup Field Name must be "Masters_External_ID__r"

TRF

View solution in original post


All Replies
Sixteen Stars TRF
Sixteen Stars

Re: tSalesforceOutput - Upsert - Lookup Relationship - Master/Detail - upserting Details with External ID only

For lookups based on custom fields, replace __c by __r (for relationship).
In your example, the Lookup Field Name must be "Masters_External_ID__r"

TRF

View solution in original post

Sixteen Stars TRF
Sixteen Stars

Re: tSalesforceOutput - Upsert - Lookup Relationship - Master/Detail - upserting Details with External ID only

@jpereira_ca, dies this help?
If so, thank's to mark your case as solved (Kudos also accepted as a bonus)

TRF
Five Stars

Re: tSalesforceOutput - Upsert - Lookup Relationship - Master/Detail - upserting Details with External ID only

Hi @TRF

 

Unfortunately, no, it didn't work. I ended up just pulling the foreign table in, and joining it before upserting.

Sixteen Stars TRF
Sixteen Stars

Re: tSalesforceOutput - Upsert - Lookup Relationship - Master/Detail - upserting Details with External ID only

That's the standard way to use the component (I use it like this every days).

If it doesn't work as expected maybe you have changed something on the relationship name on salesforce side.


TRF

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog