Apex Code Development You need to sign in to do that
Don't have an account?
SteveEthos 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
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
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
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
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
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
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
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
Well, i hope it works for you Steve :)
Do, post back the resolution you go ahead with!
Cool_D
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
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
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
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
Thanks a ton Mike!!
I was able to get the relationship;)
Cool_D