cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CShaneGoodman
Helper I
Helper I

Use an alternate key

Greetings,

 

I really need help. I'm creating entities in Dataverse, which will be used in Dynamics.

 

I have created two tables: one is for schools, and another is for school districts. There is a many (school) to one (district) relationship between the two. The lookup column is based on the district name.

 

As the size of the table has grown, I started to get errors when uploading new schools. There are now districts that have the same name. This generates an error message saying a duplicate lookup reference was found.

 

I now have a batch of 100,000 schools that I need to import, and many different districts have the same name. I would like to use the district number column for the relationship because it is unique. How do I do this? I don't want to create a second lookup column in the school entity. Can I create an alternate key, just for this import? Or can I use two separate columns (like name and state) that when added together will be unique?

 

Thank-you,

Shane.

6 REPLIES 6
ChrisPiasecki
Super User
Super User

Hi @CShaneGoodman,

 

The data import process allows you to map your Lookup column using one or more fields from the Lookup's table, in your case the District Number column as long as its unique. In the column mapping screen there should be a magnifying glass icon next to the Lookup column name which will let you select the columns you want to use. 

 

The alternate key is not necessary for the purposes of your import, but I would still advise defining a key for your district number if it should be unique as it will help enforce data quality and also boost query performance when that column is used.

 

More information is documented here.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

I don't recognize that workspace, and I don't know how to get there.

 

I'm working in Dataverse (make.powerapps.com) in the Data section. I followed the Dataverse documentation but it just gives an error message.

 

Shane.

Regarding the link above, how do I find this interface? Do I have to create an app first?

 

It looks like the relationship automatically tries find the correct record using the Primary Name Column in the parent table. Unfortunately, that name column has duplicate names. Is it possible to change the Lookup column in the template, instead of manual mapping? For example, can I use dot notation in the template to point to the school board's number column, instead of the name column?

 

In Dataverse if I expand Data > Tables and select the School table and click the Relationships tab it says the Many to One relationship is called:
cr4fc_School_cr4fc_Board_cr4fc_Board

 

Thanks,

Shane.

Hi @CShaneGoodman,

 

Yes you'll need to navigate to that table within a model driven app. When looking at a view the Command Bar will have the Import Data button. 

 

You do have the ability to save the Data Map so that it's available upon subsequent imports instead of manually mapping every time. 

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

 

I just realized that if I upload a .csv (instead of an Excel Workbook) it will ask me to map the columns. I can then specify which column in the parent table I want to refer to. 

 

Thanks for all the suggestions.

 

Shane.

Hi @CShaneGoodman,

 

Yes that's correct, I forgot to mention that minor detail. Use CSV when you want to create new records, Excel to modify existing records that were exported.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (3,673)