Solved

MS CRM 2011 Import Data -  Lookup reference could not be resolved

Posted on 2014-03-12
19
1,773 Views
Last Modified: 2014-04-01
Good Day,
1. Templates for accounts; contracts and contract lines are used from MS CRM 2011.
2. The excel templates (spread sheets) are populated with the correct information.
3. Do a standard import by using the import function in the following order:
     First Accounts; Secondly Contracts and then Contract lines.
4. It will provide a number of errors on contracts. (Import Failures.)
5. The errors are "The lookup reference could not be resolved".
6. After thorough investigation nothing could be found wrong with these records in the spread sheet.
7. We then Re submit the contracts and contract lines for import.
8. This time it is was successful.
9. This is now happening on a regular basis.
10. Where can I do to stop this from happening.

Thanking you in advance for your help.
Kind Regards.
0
Comment
Question by:Pierre_Eksteen
  • 6
  • 6
  • 6
19 Comments
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 39925596
Hi,

The message usually means you're trying to import an entity that has a relationship to another entity, but the relationship can't be created because the record doesn't exist.

If you have not yet, please follow below post and let us know-
http://blogs.msdn.com/b/dynamicscrmonline/archive/2011/06/24/crm-2011-data-import-wizard-importing-related-accounts-and-contacts.aspx
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39925987
Are you committing the records (saving) before importing the next table? it sounds like the account records are not completely stored so the contracts records can't find them and in the time taken to re-run they appear
0
 

Author Comment

by:Pierre_Eksteen
ID: 39925992
Good day,

Thank you for the information.

1. I knew about the excellent website that you recommended and used the recommendation of the zipfile for contracts and contract lines. It worked well until CRM gave an error message of not able to extract the information from the zipfile.
2. Since this happened we import first contracts and then contract lines individually.
3. The link between contracts and accounts are account name and it is the same, thus it is existing.
4. We do not make any changes to the data when it had failures with the message of "The lookup reference could not be resolved".
5. We just re submit and then it works.
6. We submit the accounts; contracts and contract lines in sequence immediately after each other.
7. is there some setting that we need to change or what else can I try to overcome this requirement of re submitting failures and then it is successfull
Thanking you in advance for your assistance.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39926012
I'm applying knowledge here of Oracle data load techniques which may or may not apply so someone may be able to improve on this:-

Between the load of bulk data and the availability of the records within that data there is a series of housekeeping tasks - indexing, log files, cascading reference updates and so on - which is referred to as 'the commit'.  To facilitate a rapid turn around these are generally done in 'blocks' of data which is managed at the database level; for example: the first 1000 records might be read and validated in memory then 'committed' as a set. Obviously there is a small window during which the record has been read but the data is not yet 'committed'. I am guessing that some of your parent records are not completed when the child records are validated. The simplest solution is to stagger the loads so the commit has time to complete. In my case we sorted the records in the dataset so the children were in the same sequence as the parent and reduced the commit size to approx. 250 records (the actual amount is a number of database pages rather than a number of records)  - this worked fine for smaller numbers but was inefficient when doing several millions so for the bigger data set we disabled referential integrity checks until the data was loaded and made the commit size as large as possible.

I don't know if such manipulation is possible within CRM interface or practical - if the CRM system is still in use whilst the load takes place.
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 39926021
Hi,

We submit the accounts; contracts and contract lines in sequence immediately after each other.

Do you check in Data Import records if the data is successfully imported/Import job is completed successfully for the first file and move ahead for the second one?
0
 

Author Comment

by:Pierre_Eksteen
ID: 39926070
Hi Regmigrant,

1. I use the import facility (Import Data) as in the ribbon of MS CRM 2011.
2. We submit the three tables in sequence.
3. I have attached an example of imports.
3.1 The import highlighted as "1" was the first import and all the accounts were
       successful        but no contracts and contract lines.
3.2  We do not change any data in the tables and submit the import facility again for the
       contracts and contract lines. (labelled as 2 on attachment) All Contracts were  
       successful imported. No contract lines were successful.
3.3  We do not change any contract line data and submit the contract lines again. It is
       successful (Labelled as 3 on (attachment).

How can I apply a commit as explained by regmigrant.

The imports are submitted in sequence  - thus the first one finishes before the next one will start.

Thank you for the help.
Import-Statistics.png
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 39926098
Hi,

Do Remember... Database level changes like commit and rollback are strictly unsuported by Microsoft in case of MS Dynamics. Anything to modify must be done through either CRM UI or MS Dynamics CRM SDK.
0
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 39926102
Have you tried some pause while importing all 3 files??
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39926244
Based on Rikin's comment above I would stay away from the database level commits, page sizes etc unless you have a DBA that knows precisely what they are doing. However from your description it sounds more and more like the problem I described.

Presumably this is not a one-off exercise to pre-load the data but will happen regularly and you are trying to automate or script it.

I don't know the detail of Import Data for Dynamics but when you say 'in sequence' do you issue a single Import command that specifies three files? I would recommend issuing each one as a single step and confirming the load before issuing the next.

Even if you currently issue 'import file1' then immediately 'import file2' its likely that the import code creates a thread and returns a command prompt before the load/commit cycle completes because the import sequence is 'asynchronous'

See this link for executing Data Import
http://msdn.microsoft.com/en-us/library/gg328031(v=crm.6).aspx

it clearly states:
"You must let each job complete successfully before you can submit a new message request for the next asynchronous job."

An although you are loading a complete set of data I believe you are creating three separate asynchronous jobs which is leading to your error
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Expert Comment

by:Rikin Shah
ID: 39926261
Hi,

This is what I have been trying to say... Data Import is being handled by CrmAsyncService and it will have a lag in some seconds up to 2 minutes.
0
 

Author Comment

by:Pierre_Eksteen
ID: 39926687
Good day Rikin and Regmigrant,

1. We changed our import by ensuring that each import job completed before the next one
    is submitted.
    1.1  Submit import for accounts and wait until completed.
    1.2  Submit import for contracts and wait until completed.
    1.3  Submit import for contract lines.

The imports completed successful!!! No failures with the description of "The lookup reference could not be resolved".  

2. Is there something that I can do in order to be able to:
    2.1 Submit the imports(accounts; contracts and contract lines) with the import
          utility in the correct order and
    2.2 The accounts import will then first finish before the next import starts and so for
           contracts.

Thank you very much for the effort and assistance so far. It is much appreciated.
Pierre.
0
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 250 total points
ID: 39926722
I think the method provided in the link Rikin provided earlier is the way forward but I think you are not providing enough information for the import to recognise when it needs to handle the id fields and lookups - ie: when you form your entity maps and specify lookup groups you are telling Import where the dependencies lie so it can execute the load in the proper sequence.

Also I don't know to what extent it can be automated if that's what you need.

If you are creating a script to issue the imports one after another then the script should wait until each step is finished - by reading the status from the log file for example.
0
 

Author Comment

by:Pierre_Eksteen
ID: 39945213
Thank you very much for the help.
Kind Regards
Pierre.
0
 
LVL 19

Accepted Solution

by:
Rikin Shah earned 250 total points
ID: 39949592
Hi Pierre_Eksteen,

Don't you think I deserve some points on this?
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39950023
I concur - a split would be appropriate
0
 

Author Comment

by:Pierre_Eksteen
ID: 39952472
Please tell me how to split - it was my intention to split and unfortunately by accident added it to two comments that belong to the same person. My sincere apologies. What must I do to reverse and submit it equally split.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39952646
You want to request an alert to moderators and put the request in there
0
 

Author Comment

by:Pierre_Eksteen
ID: 39968860
Thank you very much.
Kind Regards.
Pierre.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now