Unanswered

Data integration and mapping a foreign key to the entity relationship

(0) Share

Report

Posted on by lazevedo

I'm importing my postgresql database through Data Integration and the entities are being created fine the issue is connecting both entities together. I have a "Company" and a "Project" table which are connected through a foreign key (Projects has a foreign key to a company). I've imported all entries from Company to a CDS Entity and then did the same for the Projects. Then I went to the Projects "Relationship" tab and created a new N:1 pointing to Company but now I need to map my FKid on the projects table to that relationship field so entries are connected to the right Company but I don't have that attribute on the mapping columns. How do I do it?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Abe Saldana 75 on at

Like (0)

Report

lazevedo,

you need to create the fields that are for the FK as lookups on that entity, and then associate those records with the lookup field, that will create the relationship you need to for those 2 entities.

hope that helps

abe.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @AbeSaldana

Can this be done at the time the data integration for the child table is done? If not, can it be done in bulk following integration of the two tables using Power Query? I have a child table containing over 20k items and it is impractical to relate each item one at a time.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

Hi @lazevedo and @AbeSaldana

I have been struggling with bringing a large dataset from an outside database into CDS while maintaining the relationships between the tables. It has even been stated that it is impossible to do it. https://powerusers.microsoft.com/t5/Common-Data-Service-for-Apps/Creating-Parent-Child-Relationship-... I have just posted an article in the Community blog https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/bg-p/PowerAppsBlog with a step by step method for accomplishing this with illustrations of the steps. It might be cumbersome but it is possible just using Excel with the built in Power Query, PowerApps Common Data Service. Kudos goes to @SeanHenderson for the original idea.

Was this reply helpful? Yes No
PietPoelsma 67 on at

Like (0)

Report

Hi @Drrickryp

I followed the link to your article in the COmmunity blog on how to create the relationships, but am unable to find the right article...🙄.

I also would like to etablish relationships between tables for imported data from SQL Server into the Dataverse.

Thanks
Piet

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Importing-an-external-relational-database-into-Common-Data/ba-p/364452

Was this reply helpful? Yes No
PietPoelsma 67 on at

Like (0)

Report

Thanks @Drrickryp,

I made it work!😀

As I will be importing about 25 tables from SQL Server with also approximately the same number of relationships it would be nice if you can script this, instead of doing it all by the GUI. Make it repeatable.

Any ideas or suggestions over there?

Kind regards,

Piet

Was this reply helpful? Yes No