How to import data with relationship and lookup

[nicfil]

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?

[jlindstrom]

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

[nicfil]

@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."

[GarethPrisk]

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.

[Drrickryp]

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... 

[DavidJennaway]

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

[Drrickryp]

@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.

[jlindstrom]

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...