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
SteveEthosSteveEthos 

Getting error: MALFORMED_ID:bad id when trying to import data using External Ids for Lookups

I have 2 objects:

 

Fee__c

    Has field key__c which is marked as an External ID.

 

FeeMarkup__c  (Points to fee)

    Has field key__c which is marked as an External ID. 

    Has field r_fee__c which is a lookup to Fee_c. 

 

 

I can use the DataLoader to load Fee__c fine.  The problem is when I try to load FeeMarkup__c.  I have the following data

 

 

FEE

key__c value__c

LP5 23

LP4 19

AG1 99 

 

 

FEEMARKUP

r_fee__c markup__c

LP5 1.1

LP4 1.0

 

 

When I use the data loader for FeeMarkup, I map r_fee__c to the lookup, and then the data loader asks me to confirm the external id field (key__c) on the Fee object.

 

However, I am getting the following error in the error results:

 

MALFORMED_ID:bad id LP5:-- 

 

 

This is an critical feature of my data loading, being able to upsert data with links to other objects, and refer to the other objects by their external Ids.

 

Has anyone else run into this problem, and have you been able to resolve it?

 

Thanks,

Steve 

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
mshelmanmshelman

This does work. I think you are maybe missing a step.

 

I think that in order to create the relationship, that the operation must be Upsert.  First you are asked for the external id field that is used to upsert into the FeeMarkup__c object which is the r_fee__c field.  Remember the ultimate goal is to populate the lookup field to the Fee__c object which is obviously a separate field from the external id r_fee__c. 

 

Next you choose key__c as the matching field for the parent end of the relationship (Fee_c). Then you get to mapping the fields.  The r_fee__c column must map to the r_fee__c field as it is the external id for the upsert.  What you need is an additional column that is a copy of r_fee__c to map to Fee__r.key__c and then the relationship will be populated.

 

Mike

All Answers

Cool_DevloperCool_Devloper

Steve,

When you load the data in any dependent object (FeeMarkup__c) which has a lookup field to any other object (Fee__c), then salesforce expects the salseforce 18 digit ID as the value and not a text like " LP5 ".

This is the reason you are getting the Malformed_Id error while loading the data. You have to take the 18 digit ID of the loaded Fee__c records and manually map them to the lookup field " r_fee__c " of the corresponding FeeMarkup__c records.

This should solve your issue!

Cool_D

SteveEthosSteveEthos

I had thought that the DataLoader was powerful enough to use the External Id of the related object, (Fee__c,  LP5), and do the lookup for me.

 

Otherwise, data loading will be a painful process.  I would have to load Fee__c, then retrieve its SF Ids back to to my SQL Server, then key the r_fee__c fields in FeeMarkup__c.   (Mutiply this many times for every related object and field)

 

When I was at the new Bulk Loader Session at DreamForce on Tuesday, the SF team told me that this was possible.  (In fact it was Lexi himself after the session, author of the "LexiLoader".

 

When loading FeeMarkup using the Data Loader, it detects the relationship and prompts me to select the External Id field of the related object Fee.  So, I expected it to work.

 

Has nobody else successfully used Related Ids to link lookup fields on the Data Loader?

 

Steve 

 

Cool_DevloperCool_Devloper

I think what you're saying is right Steve!

Looking at some posts i do realize that this should work out. Can you post the complete error message which you are getting?

Cool_D

Message Edited by Cool_Devloper on 11-22-2009 02:39 AM
SteveEthosSteveEthos

This is the error that I am getting for each and every row in the upsert:

 

MALFORMED_ID:bad id LP5:-- 

 

(the LP5 is different for each row, however, I do not know what the :-- means at the end, it is not in my data).

 

I confirmed that there is a Fee__c object with the External Id field having a value of LP5, so this should be working.

 

 

I actually have 15 objects to load, with record counts ranging up to 500,000 rows, and having references to other objects, so this is a critical feature of the Data load process. 

 

 

Thanks, 

Steve 

Cool_DevloperCool_Devloper

Steve,

I just created a similar scenario in my dev org and ran the upsert process using Data Loader. I mapped the same value "LP5" to the lookup field to the other object and there was no exception :O 

But still, i did not see the lookup field populated when i saw the record in salesforce UI :( 

M not sure, what is exactly happening out here!! What is the data type of your external ID field? I made it a text.

Cool_D 

SteveEthosSteveEthos

I have opened a trouble ticket and is has been raised to level2.

 

If this is resolved, then I will be able to insert the data easily.  Otherwise, I will have to do the following:

 

a) Insert primary objects (no dependencies)

b) Add additional columns for the Sf ids for each object, and additional columns for each relationship 

b) Retrieve the SF ids and put them back into my SQL

c) Run SQL statements with Joins to populate the relationships with the SF Ids.

d) Push the data into SF.

e) May need to repeat for complex relationships (multi level relationships, and/or MasterDetail)

 

This would be so much easier to do it in one step with using External Ids for lookups.

 

Steve 

Cool_DevloperCool_Devloper

Well, i hope it works for you Steve :)

Do, post back the resolution you go ahead with!

Cool_D

Cool_DevloperCool_Devloper

Hi Steve,

Any luck with this?

Just curious to know, if we can do this. As you said, it can ease out a lot of data load effort;)

Cool_D

mshelmanmshelman

This does work. I think you are maybe missing a step.

 

I think that in order to create the relationship, that the operation must be Upsert.  First you are asked for the external id field that is used to upsert into the FeeMarkup__c object which is the r_fee__c field.  Remember the ultimate goal is to populate the lookup field to the Fee__c object which is obviously a separate field from the external id r_fee__c. 

 

Next you choose key__c as the matching field for the parent end of the relationship (Fee_c). Then you get to mapping the fields.  The r_fee__c column must map to the r_fee__c field as it is the external id for the upsert.  What you need is an additional column that is a copy of r_fee__c to map to Fee__r.key__c and then the relationship will be populated.

 

Mike

This was selected as the best answer
Cool_DevloperCool_Devloper

Thanks for the info Mike:)

Can you please elaborate on what does - "copy of r_fee__c to map to Fee__r.key__c" mean exactly?

Do you mean to say, that i should have two identical ExternalId fields on my child object to populate the lookUp field? Or do you mean, just that the child object should have an ExternalId field to hold the matching ExternalId to its parent?

Cool_D

mshelmanmshelman
No just an additional column in the csv that maps to Fee__r.key__c. If you walk through the wizard I think you'll see what I mean.
Cool_DevloperCool_Devloper

Yes, i understood that Mike. Thanks a ton!

Well, i just ran a test in my dev org using Data Loader. I created a custom lookup from contact to account and then an externalId field at both account and contact say "externalId__c".

While doing upsert for contacts, i mapped this field to the lookup field to account with a valid matching externalId value in Accounts!

Data Upload was successful but the lookup field did not get populated :(

Am i missing something here?

Cool_D

mshelmanmshelman
csv                                                contact                                                              account
 
val for ext id                               externalid__c (upsert to contact)
val for ext id                                                                                                            Account.externalid__c  (populate relationship)
Cool_DevloperCool_Devloper

Thanks a ton Mike!!

I was able to get the relationship;)

Cool_D