cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lazevedo
Frequent Visitor

Data integration and mapping a foreign key to the entity relationship

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?
6 REPLIES 6
AbeSaldana
Helper II
Helper II

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.

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.

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. 

PietPoelsma
Helper I
Helper I

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

PietPoelsma
Helper I
Helper I

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

 

 

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (1,343)