2 Minute Migration Mastery
Map Legacy Ids into Custom External Id Fields
Interested in testing a pre-release of the Salesforce community’s first-ever Data Migration course?
Sign up below to secure a spot.
Use External ID Fields in Salesforce to Preserve and Reference Legacy Record IDs
When migrating data into Salesforce from a legacy system, one small decision can save hours of cleanup and prevent painful referential errors: store the source system ID on each record and mark that field as an external ID. External ID fields let Salesforce and external tools like Data Loader treat legacy identifiers as a lookup key. That means you can insert parents, capture their new Salesforce IDs, and then load children by referencing the legacy IDs — no messy VLOOKUPs and far less manual risk.
If you want a visual walkthrough, check out The Secret to Perfect Field Mapping in Salesforce Data Loader for a step-by-step guide.
Table of Contents
Why store legacy IDs at all?
Legacy IDs are the bridge between the old system and the new. Keeping them gives you:
- Traceability — know exactly which legacy record maps to which Salesforce record.
- Rollback and reconciliation — validate that all legacy records made it into Salesforce or compare data later.
- Reference during subsequent loads — load related records (contacts, opportunities) by using the legacy ID to find the newly created parent.
External ID vs regular text field: what’s the difference?
Marking a field as an external ID does two important things:
- It makes the field available via the API for use as a reference when inserting or updating records with tools like Data Loader.
- Salesforce automatically creates an index on the field, which speeds lookup operations and makes large data loads perform more reliably.
Optionally marking the field unique helps enforce that no two records accidentally get the same legacy ID in Salesforce.
Real-world migration pattern (Accounts and Contacts)
Here’s a common pattern you’ll run into. Imagine a Microsoft Dynamics to Salesforce migration where Dynamics uses a 36-character GUID for record IDs.
- Create a text field on Account called Legacy ID (or a naming convention your team prefers). Make the length at least the size of the source ID — in this case 36 characters.
- Check the External ID box and, if it makes sense, the Unique box as well. Save and set field-level security and layout visibility.
- Load Accounts with Data Loader and map the Dynamics ID into Account.Legacy_ID__c. Data Loader will insert accounts and produce a success file that lists the newly assigned Salesforce AccountId alongside the legacy ID you provided.
- Now load Contacts. Each contact record in Dynamics references its parent Account via the Dynamics account ID. Instead of copying the Salesforce AccountId into each contact row using a VLOOKUP in Excel, instruct Data Loader to match Account on the Account.Legacy_ID__c field. The lookup occurs by the legacy value and associates the contact to the correct Salesforce account automatically.
This second option — using the external ID during the contact insert — eliminates a manual VLOOKUP step and reduces the chance of human error creating bad relationships.
How to create an external legacy ID field
The setup is straightforward. In Setup, go to Object Manager and select the object you want (for example Account). Create a new Text field and:
- Give it a clear label like Legacy ID. Tab down to auto-generate the API name.
- Set the field length to accommodate the longest source ID you might see.
- Check the External ID checkbox.
- Optionally check Unique to prevent duplicates.
- Save, then update field-level security and page layouts as appropriate.
Because external ID fields are indexed automatically, lookups are faster and bulk loads run better. They also become searchable from the global search box, which makes post-migration validation much easier.
Data Loader: using external IDs for lookups
When mapping fields in Data Loader, you can map a foreign key (a lookup) using the target object’s external ID instead of its Salesforce ID. For example, when loading Contacts, map the Contacts Account lookup to the Account object by referencing the Account.Legacy_ID__c external field. Data Loader will find the Account by that external value and populate the contact’s AccountId behind the scenes.
This removes the need to update your CSV files with Salesforce IDs between loads and allows you to keep working with the source system identifiers throughout the migration.
Best practices and tips
- Use consistent naming — pick a naming convention like Legacy_ID__c or Source_System_ID__c and stick to it across objects. Consistency helps mapping and reduces confusion.
- Ensure field length — set the text length to accommodate the longest possible legacy ID including hyphens or extra characters.
- Decide on uniqueness — if the legacy system enforces unique IDs, enable the unique constraint in Salesforce. If not, leave it off to avoid insertion failures.
- Protect sensitive data — legacy IDs may include PII or other sensitive tokens. Treat them according to your security and privacy policies and only expose them to users who need access.
- Keep a mapping success file — Data Loader’s success file that contains new Salesforce IDs alongside legacy IDs becomes your canonical map for any later reconciliation and troubleshooting.
- Clean legacy IDs before loading — trim whitespace, remove non-significant characters, and standardize formats so lookups match reliably.
- Test in a sandbox — always validate the approach in a developer or sandbox org before running a full production migration.
Why this saves time and reduces risk
People often default to doing VLOOKUPs in spreadsheets to match new Salesforce IDs back into related object files. That works for small datasets, but it is error-prone and slow. Using external IDs lets your ETL tool or Data Loader do the matching reliably at scale.
Because the external ID field is indexed, Salesforce performs the lookup efficiently. And by keeping the legacy identifier on the record, you preserve an audit trail that is invaluable for troubleshooting, historical reporting, or phased cutover strategies.
Quick summary checklist
- Create a text field on the object for the legacy ID with an appropriate length.
- Check External ID and check Unique if appropriate.
- Load parent records with the legacy ID populated and save the Data Loader success file.
- Load child records using the parent object’s external ID as the lookup key in your data loader.
- Validate the results using global search, reports, or direct queries.
Using external ID fields is one of those simple Salesforce practices that pays dividends across the entire migration lifecycle. It keeps your dataset consistent, speeds up loads, and gives you a reliable way to reference legacy information long after the cutover. Create those legacy ID fields early in the migration process, and your future self will thank you.
Troubleshooting Tips
- Child records aren’t linking to the correct parent
- Confirm you mapped the child lookup to the parent’s External ID field, not Salesforce ID.
- Ensure all legacy IDs are clean, consistent, and free of extra spaces.
- Duplicate errors during insert/upsert
- Check that the External ID field is unique if your legacy system guarantees uniqueness.
- Remove duplicates from your CSV before loading.
- External ID field not recognized by Data Loader
- Make sure the field is marked as External ID in Salesforce.
- Refresh the object schema in Data Loader if the field was just created.
- Lookup matches fail
- Verify that parent records were loaded first and appear in Salesforce with the correct External IDs.
- Check for mismatched formatting between CSV and Salesforce (case, hyphens, or extra characters).
- Data load is slow
- Ensure External ID fields are indexed (they are by default).
- Split large CSVs into smaller batches for better performance.
FAQ
Why should I store legacy IDs in Salesforce?
Storing legacy IDs lets you track exactly which old system record maps to which Salesforce record. It ensures traceability, simplifies rollbacks, and makes loading related records much safer.
What’s the difference between an External ID field and a regular text field?
External ID fields are indexed for faster lookups and can be used as a reference key in tools like Data Loader. Optional uniqueness ensures no two records accidentally share the same legacy ID.
Can I use External IDs when loading child records?
Yes! Map the child’s lookup field to the parent object’s External ID in Data Loader. This eliminates manual VLOOKUPs and ensures relationships are accurate.
How do I set up a legacy External ID field?
- Create a text field with a clear label like Legacy_ID__c.
- Set length to accommodate the longest source ID.
- Check External ID and optionally Unique.
- Adjust field-level security and page layouts.
Are there any best practices I should follow?
- Use consistent naming across objects.
- Clean and standardize legacy IDs before loading.
- Test in a sandbox before production.
- Keep the Data Loader success file for reconciliation.
Conclusion
Using External ID fields to store legacy system record IDs is one of the simplest but most powerful habits you can build in Salesforce data migrations. It saves hours of manual work, eliminates error-prone VLOOKUPs, and ensures your child and parent records link correctly from the start.
By creating clear, consistently named External ID fields, loading parent records first, and referencing these IDs for child records, you create a reliable, scalable, and auditable migration process. Test your approach in a sandbox, clean your legacy data, and always keep your Data Loader success files as your canonical mapping reference.
This small upfront effort pays off big—faster loads, cleaner data, and peace of mind that your migration was done right. Your future self (and your team) will thank you.