Pentaho salesforce upsert using externalID

Question

I am trying to insert data in salesforce using upsert, for one field i am using the ExternalId field , i have tried many combinations but it fails...I get the error : the syntax should be object:externalId/lookupField

Any idea what is the exact syntax? Keep in mind i am inserting in table Account and the externalId field refers to Account also

What is the API name of the field you want to use during upsert? If it is a custom field it will end with __c. This field should be marked as an ExternalId in Salesforce so it has the required index for checking if you are doing an insert or an update. — Daniel Ballinger
The name of the field is Primary_Parent_vod__c , actually i want to insert new accounts in salesforce with primary parent an account that already exists in the system but i dont want to use the account's actual salesforce id but the externalID we use in our db. This can be done?If yes i guess it shoyld have a specific syntax. — user3797135
I tried the syntax Primary_Parent_vod__r:External_ID_vod__c in Module Field, this works in Dataloader but here i get th error:The value of the attribute "prefix="xmlns",localpart="Primary_Parent_vod__r",rawname="xmlns:Primary_Parent_vod__r"" is invalid. Prefixed namespace bindings may not be empty. — user3797135

codeulike codeulike · Accepted Answer · 2015-01-26T13:16:54

object:externalId/lookupField is not very clear is it. There's a comment hidden away in the Pentaho code:

        // We use an external key
        // the structure should be like this :
        // object:externalId/lookupField
        // where
        // object is the type of the object
        // externalId is the name of the field in the object to resolve the value
        // lookupField is the name of the field in the current object to update (is the "__r" version)

Lets say you're populating a Salesforce Object Foo__c, which has a Lookup field to Contact called Contact__c. The 'relationship name' for that lookup field would then be Contact__r. On Contact lets say you have added an External ID called Legacy_Id__c and thats what you want to use when populating Foo__c.

What Pentaho would want in the Module Field column would then be:

Contact:Legacy_Id__c\Contact__r

The bit to the left of the slash is telling Pentaho which object/external id to map to. To the right of the slash, its telling Pentaho which lookup/relationship on Foo__c to fill in.

Pentaho salesforce upsert using externalID

1 Answers