function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

Upsert Account to Parent Account with External ID using Pervasive Data Integrator



I've upserted a list of companies to Account with their original id placed into an external id field called "Account_ExtID".

I've got a second list of Child Accounts that I want to upsert. The mapping is the same for the most part, but now I'm also filling in the Parent ID which is the default Lookup field for account.


This is what I could find in the documentation of Pervasive for the Salesforce API connector

  • In the reference field of the child entity, enter the unique External Id information of the parent entity in the following format (parentheses are included in the syntax):



MAPPING: Parent ID = "(Account:Account_ExtID:"&Fields("Company_ID")&")"

ERROR: Upsert: error sf:INVALID_FIELD (INVALID_FIELD: No such column 'Account' on entity 'Account'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.)


Then I thought I shouldn't specify the entityname because he was checking for a column on the entityname location.


MAPPING: Parent ID = "(Account_ExtID:"&Fields("Company_ID")&")"

ERROR: Upsert error code MALFORMED_ID: Parent Account ID: id value of incorrect type: (Account_ExtID__c:300078)


I've done upserts with external id's several times already with Pervasive succesfully, but never on Account and never with both parent and child being the same object type.


What is the correct way to do this in this situation ?


Thanks in advance,





I would guess that when they say entityname, they really mean relationship name, try Parent ID ="(Parent:Account_ExtID:....)


we are running into the same issues.  did you ever figure out how to upsert account to parent account with external ID?


I encountered the same problem and after Salesforce acknowledged my case and after confirming it was still an issue they closed the call ! 

I managed to solve it by using upsert to create the Parent Account in Salesforce and then passing back the SalesforceID back to our source system and then upserting this together with the 'child' data to create the child Account which is automatically linked to the Parent Account.



Please note that in the above post I am using an External ID to identify Account records. 




I never found a way to solve this using the external id as identifier to upsert with.


There is however another way to do this, unfortunately it is also alot slower.


In Pervasive Data Integrator, there is something called 'Lookup Functions'. I created a function that queries Salesforce with the external id as parameter and that returns the salesforce id.

I call this function on every record, I fill in a variable with the return value and I use that variable as my identifier.


This will increase the processing time immensely but it works and you only have to do one migration action.