Import data with lookup references

(0) Share

Report

Posted on by Community Member

Hi,

I'm trying to import data to dynamics CRM using the data import template. The table I'm trying to import to contains lookup columns a and there is data already there which has been added through forms. However when I export this data I see that the data in the lookup uses a non-unique reference, such as "Yes", even though a unique ID field exists (I assume because it's not the primary field?).

So when I try to import data using the unique ID field, I get a "reference could not be resolved" error, and there is no other way of referencing a unique column.

Could anyone propose a way around this, such as changing the relationship mapping?

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Suggested answer

Community Member on at

Like (0)

Report

Hi AidanWW,

Bit confused about the exact steps.
Does it still give the non-unique reference if you download a new template or simply use Export to Excel with the specific lookup columns?

The Error "reference could not be resolved" usually comes when it is trying to find the Excel lookup value in the list of the Lookup entity records (In your case it might be using 'Name' field) but unable to find it.

If its a new upload file without record GUIDs Import Wizard will also allow for mapping the fields where you may also choose a different column to resolve the lookup reference. The Import Wizard will then consider it as new data and not update of existing data.

Primary field of an Entity like Name is usually enough to resolve the lookup but it may have duplicates. So we may choose alternate fields which may have unique values.

You may also try importing with the Unique value of the lookup entity record (the unique value should exist in CRM before the import).

Let me know more details if the above wasn't helpful.

-Aamer.

Was this reply helpful? Yes No
Verified answer

gdas 50,091 Moderator on at

Like (1)

Report

Hi Adian,

This seems data issue , the lookup reference you are giving in the template that does not find in your lookup record .

Every lookup entity should have primary field called "Name" if I am not wrong . So I would recommend for trial run take one lookup entity record "Name" value and put it in the template for 1 or 2 records for test run , see how it goes .

Apart from that check which reference is not exists , so you need to break your data in multiple template file .

Hope this helps.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, setting all the "Name" values as unique and using that as the lookup worked fine. Thanks!

Was this reply helpful? Yes No
Suggested answer

Community member 2 on at

Like (0)

Report

Hi There

I had a similar problem as I wanted to import data which had a lookup to a virtual table. After a number of failed attempts it seems that when a virtual table is loaded, each row is assigned a numerical value from 1. If you view the table in Dataverse you should see an 'ID' column with a number in it. For example, a row with Reference Item01 may have an ID as 1 and so on. When I loaded data and tried to look up Item01 it fails but when I replaced the Item01 value with the ID - 1 it linked to the virtual table entry ok. The ID is stored as a Hex number and is in a GUID like form such as "00000001-0000-0000-00000000' but it works.

If you manually create some entries and link to the virtual table you can export the data and see these numerical numbers appearing. I changed the number from 1 to 2 and it then linked to the second record in my virtual table.

Note that the numerical values are based on the order in which the records are added so I had to export the source virtual table to a spreadsheet and assigned numerical values to get a lookup from the ID to the number. I then used this lookup to map my Ids to a number and I could import all of my data.

Not straight forward but it does seem two work.

Was this reply helpful? Yes No