Populate Target Lookup Field using an External ID while using the Import Module

[kevSF]

Hi all! A new user here, so appreciate any help.

I am using the Import module in Demand Tools V to migrate some records from one instance of Salesforce to another. 

Are we able to use an external ID field to populate a lookup field while importing?

For example, when we want to import Opportunity records, we want to populate the Account lookup field by using a custom External ID field (a unique identifier) to find the matching Account record in the target system.

We populated the External ID field on the Account record on the target system with the original record ID value from the source system. Since we don't have the target record ID, I want to use the External ID (Source System Record ID) to find the matching record. I believe this can be done easily in DataLoader, but unsure how this can be achieved in DemandTools V.

We are using an Import File as the Source when doing this operation. Any guidance will be much appreciated!

[Murphybp79]

You need the new Account ID to associate the Opportunity to the Account.  You can't do a lookup as part of an import.  Depending on how many records you are working with, you could either export the Accounts from the Target system with the External Id and the Account ID, and then update your source Opportunity File with the new Account Id.  Or you could create a Flow in the Target system that does the lookup using the External ID and then populates the Account Id field. 

[klinde]

I'm disappointed this can't be done in Demandtools. Poor assumption that source files will have standard salesforce IDs. Many offerings from other providers have this ability (Dataloader.io, Dataimporter.io).

[Pugs_Validity]

Thanks for the clarification, @Murphybp79! @klinde - Did you have a specific use case you are trying to solve for? As @Murphybp79 pointed out, you aren't require to have Salesforce IDs to identify records, DemandTools currently is just unable to populate fields based off an External ID. 

[Murphybp79]

@klinde I think you may have misunderstood.  You can use an External ID in an import, or do a matching based on other fields.  But you can't perform a lookup query as part of an import.  You have to have the data in the source you are importing. 

[mppowe]

I agree that this would be useful.  My Use Case is I work in our school's IT department, and I want to create an import Scenario that our users can use to import data from our ERP software into Salesforce.  The ERP software doesn't have Salesforce IDs in it; only its own Ids.  So the extract has the Erp Id for each student.  I'd like to import this data into a custom object in Salesforce that has a Lookup to the Account object, but I don't have the Salesforce ID for this AccountId, rather I have the ERP ID.  This ERP ID is an External Id on the Salesforce Account object, so if I could populate the lookup field in DemandTools using this erp id, that would be great.  Otherwise, it seems we have to export all Accounts into Excel, do a VLOOKUP to replace the source file with AccountIds, and THEN import using DemandTools.  But I think that intermediary step would be too complicated for our typical user.

[Murphybp79]

@mppowe You could build a simple flow on your custom object that could to the lookup and population of the account ID every time a new record is created.  

[mppowe]

yes, it's just more Salesforce development.  I'd love if this tool could handle it, especially when many of its competitors like Workbench and Dataloader support it.  I don't want to give instructions to my users to use DemandTools for some imports and DataLoader for others, so that means SF development, which is a bummer for short-staffed institutions 🙂

Is there an enhancement request for this already, I wonder?

[Murphybp79]

@mppowe You can submit a request for the feature.  However, if your goal is to have your users use DemandTools, then your only option for your scenario is to build a Flow to popoulate the Account.  It would only take a simple 2 step flow that shouldn't take more than 5 minutes to create.   One step to query the account using the External ID, and the 2nd step to update the record with the Account ID.