function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AeriesAeries 

How do you use the Data Loader with lookup tables?

I have several tables that are lookup tables (Standard name field is actually the primary key).  I have a pretty heavy reliance on structuring the data this way.  I snake my way through the lookup relationships to pull descriptive information.  It is not a problem to load the data though the interactive data loading wizard.  When it recognizes a field that is defined as a lookup data type, the loader knows which table it is associated with (but you can change it if desired). 

The command line data loader/ Apex Data loader does not have this intelligence built in, so it won't load the data  because of an unmatched data type.  I've perused the discussion board and know about External IDs but that is not what I'm looking for, since External IDs can only be applied to an attribute field of the parent table, not the actual primary key itself.  So while I can duplicate the key value into a text field and declare it to be an External ID, the data will load, but it will not associate the data with the actual primary key value,  so it will not recognize the lookup relationship.

What I really want to do is declare the standard name field, that I have made into my primary key, to be an External ID, but there doesn't seem to be any way to do that.  Any suggestions?

aalbertaalbert
Could you just create a new custom field, called Name__c  (for example) and declare it as External Id? And then define the standard name field as an auto-number so you don't have to manually populate that (if you don't want to have redundant data in both Name and Name__c)?

Or a more advanced solution would be to deploy an apex trigger on the object you are loading to do the logic you want to apply to the Name field. Just make sure the trigger is developed efficiently to handle the bulk records being loaded during the data load process.
AeriesAeries
Unfortunately, it's not enough for me to just be able to load the data.  I have taken full advantage of the "drill down" capability you are awarded if you use the standard name field as your lookup field.  That is, when you run reports, SF lets you click on that (lookup) field and you are then able to see everything that is linked to the parent object as a lookup entity.  It is a very nice capability.  Once you use a custom field that is not the standard name field you lose that capability.  I played around with the interactive wizard, which does let you select an external id instead of the standard name field as your lookup field, but the data record just comes out as a data record.  The lookup field isn't underlined, so you can't click on it to drill down to see the other objects that are linked to the parent.

Sigh.  I may eventually have to try something like Informatica to do the ETL stuff that Salesforce doesn't seem to be able to handle.

Thanks for the advice, though.  I may try writing custom code, but am not sure that will give me what I want either.