Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Bulk Upsert to a Custom Object when External ID is only defined on Account Object only

I have an issue with External IDs reference.. I am trying to Upsert to salesforce but the object I am upserting to does not have external id defined on it and it is not the account object:

The below options failed:
--Upsert
LocalSQLTable SalesForceTableName_MySuffixETL SomeColumnName_ID (Foreign Key or External ID Column):Data stored in SomeColumnName_ID: "ExternalColumn__c:568674"
Exec SF_BulkOps 'Upsert:bulkapi','LinkedServerName','SalesForceTableName_MySuffixETL', 'ExternalColumn__c'

--Update
LocalSQLTable SalesForceTableName_MySuffixETL SomeColumnName_ID (Foreign Key or External ID Column):Data stored in SomeColumnName_ID: "ExternalColumn__c:568674"
Exec SF_BulkOps 'Update:bulkapi','LinkedServerName','SalesForceTableName_MySuffixETL'

--Update no reference value
LocalSQLTable SalesForceTableName_MySuffixETL SomeColumnName_ID (Foreign Key or External ID Column):Data stored in SomeColumnName_ID: "568674"
Exec SF_BulkOps 'Update:bulkapi','LinkedServerName','SalesForceTableName_MySuffixETL'
Does external id have to be explicitly defined on the custom object for upsert to work?
Any help would be greatly appreciated.
1 person has
this question
+1
Reply
  • External Ids are used in 2 different ways and I think you have confused these together.

    1. To specify a value for a foreign key field. For example on the Contact table there is a field call AccountId. When inserting contacts we can either put an actual id of an account record in the AccountId field or if the ACCOUNT table has an external id column called XID__C we can put XID__C: 222 . This tells salesforce to resolve the AccountId field by looking up the XID__c value. Note: This use of external ids as a foreign key reference has NOTHING to do with UPSERTS. In fact, this technique can be used for any operation (INSERT,UPDATE, UPSERT, etc.

    2. External Ids can be used to drive an UPSERT operation. For example you want to push Contact records to salesforce. The CONTACT table has an external id field called CXID__c. You want salesforce to UPSERT meaning that you are going pass a CXID__c on each contact record and you want salesforce to insert that contact record if the CXID__c value does not exist and Update the contact if it does.

    So, to answer your question: If you want to UPSERT (#2 above) to a table then that table MUST have an external id.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Thanks very much for your prompt response; it makes sense!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • External ID usage not functioning according to doc. Referenced table has External ID column defined. Place value in the foreign key column instructing dbamp to lookup the ID value based on external ID field defined. Immediately there is one flaw: the column is defined as nchar(18) to match all ID standard types. if your new value concatenation exceeds 18 characters you have to increase the size on the source field.. See error I am getting, please help:

    BulkAPI:Upsert:750c0000000FpXVAA0:751c0000000OtRFAA0:1:Error - MALFORMED_ID:SalesForceID Object: id value of incorrect type: Replaceme__c:2567842:SalesForceID_Object__c --
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Just change the field type from nchar(18) to nvarchar(50) to accommodate the value.

    Also, please post an example value that you are placing in that column in the load table.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • BulkAPI:Upsert:750c0000000FpgcAAC:751c0000000Otg0AAC:11:Error - MALFORMED_ID:SalesForceReferenceTableName DummSuffix: id value of incorrect type: DummyColumPrefixName__c:2579762:SalesForceReferenceTableName_DummSuffix__c --

    I replaced the actual column and table names in the above failure messages for company integrity reasons. The actual value that is failing is seen below:

    DummyColumPrefixName__c:2579762
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Is DummyColumnPrefixName__c defined as an external id, unique on salesforce ?

    If so , send me an email to support at forceamp.con with a date / time / timeline we can have a web meeting
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Yes it is. I will reply to support tomorrow..thanks!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated