cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

How to import data with relationship and lookup

Hi,

 

I have to import data in CDS from an SQL server source and i need to preserve N:N relationship and fill cds lookup field.

 

Which tool can i use for this task?

7 REPLIES 7
Highlighted
Super User III
Super User III

Is this a one time import or an on going integration?

 

Most import methods can populate lookup fields. Many of the OOTB ones use alternate keys to resolve the lookup record, so you will want to define a key for your matching criteria.

 

If you use the legacy data import utility in advanced settings you can import from spreadhseet

 

I'm also a big fan of the Power Automate Relate records step--this can bring your relationship data into CDS, including your N:N related records https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-relate-unrelate

Highlighted

@jlindstrom thanks for your answer!

It is a one time import, for now.

 

Can you explain me that:
"Most import methods can populate lookup fields. Many of the OOTB ones use alternate keys to resolve the lookup record, so you will want to define a key for your matching criteria."

 

 

Highlighted

If you import using Data Import (.CSV, Excel) and leave the lookup fields untouched, they will try to resolve on the primary (display) field. This can be problematic, since some entities display field (like Contacts (full name)) allow duplicates.

 

You can set the lookup field in the data mapping step to check other fields to resolve on, but this can also run into the same issue.

 

Alternate keys are definitions you can set on an entity, that the database will enforce as unique. This can be a field, or a mixture of fields, from the entity itself. The system will then generate an index and prevent any operation from creating a duplicate.

 

These alternate keys, once established, are then usable for imports, Data Flows, etc. and provide a reliable mechanism for matching to the correct record.

Highlighted

Hi @nicfil 

My preference is to use the dynamics365 import wizard to bring in relational data.  It is a relatively undocumented feature in PowerApps but can be accessed and used as described and illustrated in my post https://powerusers.microsoft.com/t5/Building-Power-Apps/Update-on-importing-relational-data-into-CDS... 

Highlighted

Is the N:N relationship implemented as a native N:N relationship (i.e. it shows under N:N relationships), or implemented as a separate entity ? If it's the former, I don't think any of the file-based import tools will work. Instead, you can either do this programmatically (using the Associate message), or via a tool like Kingsway Soft's SSIS component

Highlighted

@DavidJennaway 

I don't think the import wizard can handle N:N relationship. Since I always use a junction table in my apps, it hadn't been a problem as it can do the 2 one to many relationships in the junction table.

Highlighted

Use power automate flow to import your data and use the relate records action to load n:n related records. It is awesome https://www.google.com/amp/s/tattooedcrmgirl.com/2019/10/30/microsoft-flow-the-relate-records-action...

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Users online (11,515)