Creating relationship between tables using existing columns and data

[Sidkn33]

I have exported several tables from a SQL server database into Excel, I spent quite some time cleaning and configuring new primary key/ID columns then I imported the tables into Dataverse - all good.

Now I am trying to relate the tables in Dataverse by creating a new relationship and, I assumed, pointing one of the existing key columns in table 1 to the it's relative column in table 2.

So for example, I have tbl_Projects with "Project_Name" and "PM_ID" columns and tbl_Project_Managers with "PM_Name" and "PMID" columns.
PM_ID needs to relate to PMID but I can't see how to create a relationship that lets me select PM_ID from tbl_Projects and relate it manually to PMID in tbl_Project_Managers.

Again, these columns are pre-populated with unique values (numbers) in each row that correspond to a value in the other table/column/row. In this case, many-to-one:

There is one project manager per project and many projects for each project manager.

How is this relationship configured using existing columns and values?

Thanks in advance.

[Drrickryp]

@Sidkn33 

As long as your have the foreign key in the child table, say parentID, you can set up a Gallery for the Parent table and a gallery for the Child table.  The Items property of the Parent gallery would be simply the name of the table.  The gallery for the Child table would have Filter(ChildTable, parentID=Gallery.Selected.ID).  For a form to enter new data into the Child table, you need to capture the parentID from the Parent gallery. To see how this is implemented in powerapps, see my post https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Design-fundamental...  in the community blog.  

[Sidkn33]

Hi, thanks very much for your help.

I'm new to Dataverse and PowerApps and obviously need to skill-up but based on my decades old learning of Access relational database design, my assumption was that one creates a permanent relationship between specific columns of different tables (at the data layer) not in the application or other layer. Or at least, you could/should do both, where appropriate.

What I don't understand is how Dataverse when I create a new "relationship" targets the columns that I choose in the source and target tables - because I can't choose the child column only the parent.
Furthermore, this relationship appears to only be appropriate to a new table wherein the lookup (ID column) is populated dynamically as data is entered into the database/tables in question.
So there's no way to create a permanent/manual relationship in Dataverse using the columns and existing data of my choosing. it can only be done at the Application (gallery)?

[dpoggemann]

Hi @Sidkn33 ,

The world is a bit different in the Dataverse world.  If you are working with 1:N relationships from a Parent / Child type of table you create a Lookup column on your child table that is the parent table.  This will utilize the GUID from your parent table but when populating you will select the "Primary Name" field from your parent record for the child record from the user interface and even when you are populating the child records you will populate the "Primary Name" field in this lookup field in Excel etc. and Dataverse will lookup this record based on that value and populate the relationship. 

Now, if you have a situation where you have multiple records in your parent table that have the same "Primary Name" value then you can do a custom mapping to another field on the import of data as well, you would need to have another column that is unique on the import that would be on the parent records (https://www.quantacrm.com/2017/03/02/create-new-data-map-microsoft-dynamics-365-sales-crm/) 

I have converted many databases from Access to Dataverse and hopefully this helps you understand the approach.  I usually bring the data into Excel, cleanse, populate the lookups, and import the information into Dataverse.

[jaeiow]

I had this question as well. When you make a dataflow, you assign the lookup column to the "alternate key", and populate the ID. For example, I make an "Items" and a "Categories". I make a lookup column in the "Items", related to "Categories". In the dataflow for "Catagories," I populate the alternate key with the ID matching the "Items". The Primary Name of the "items" must of course be already populated with a dataflow, bringing the ID into the column.

[khwdata]

Hi Drew, 

What is your process to "Populate the lookups"?

I tried populating the lookup column by copy & paste using "Edit in Excel", but when published into Dataverse, the column is wiped clean of data, and I'm left manually populating the Look Up column through the drop-down menu. 

Many thanks.

[dpoggemann]

Hi @khwdata ,

When I import the Excel document one of the columns would be my lookup to the other table. I populate this column with the "Primary Name" column value from the other table.  Example, if I am uploading an Item record from Excel then one of the columns would be "Category" and I would populate with "Dairy" and when upload the record it will lookup the "Dairy" value in the Categories and as long as it is unique in the Categories table it will populate the value and create the relationship for that record.

[DJ2015]

This works. However, you wouldn't have any referential integrity being enforced in Dataverse.