How does Dataverse Table Relationships work when you don't define which Columns to match on?

[PhilipL]

Hi,

I am new to dataverse tables and I am having a very difficult time understanding how to use the relationship in Dataverse. In my mind a lookup or relations shod work like in PowerBI on Excel with lookup/relationship functions, Where you need to define which columns to match with.  For example if I have two tables containing different information about an Employee, I would make a relationship/look up on the columns Table1.EmployeeID = Table2.EmployeeID.

But for Dataverse all we do is say "I want a relation ship between these two tables" and then it is created, but with no way of deciding which rows that should match. 

Can someone explain how this works to be, because the relationships are not working as they should and the wrong records are being matched. 

Simplified example of my issue:

Table1 - a activity table:
ActivityID (Primary column)
Organisation
Task

Table2 - a calendar table: 
CalendarID (Primary column)
Organisation
Time

The Table1.ActivityID is unique, but the Organisation is not.

The Table2.CalendarID is unique, but the Organisation is not.

I want a relationship between the two Organisation columns, so that if I select a Activity (unique row in the Table1) I want to see all the Time rows related to that Organisation. 

Lets say I want to display this in PowerAPPs (canvas), then I would have two tables in the App, one containing data from Table1 and another containing data from Table2. Then If I select a row in Table1, Table 2 should be filtered to only show the rows that are related to the same organisation as value in the row selected from Table1. 

 Worth to mention, the Activity table have more tables it has relationships to.

[dpoggemann]

Hi @PhilipL 

Relationships are created in Dataverse using Lookup column type to the table (unless you are doing N:N). 

Example reflecting an Account able with multiple Contacts where the Account would be the (1) side of a (1:N) and the Contact would be the (N) side.

Account (1) --> Contact (N)

To create this relationship you would create a Lookup column pointing to the Account table on the Contact table.  

In the situation with Activities... Dataverse includes a concept of Activities already where this is a polymorphic type of setup with multiple activity types that exist including (Task, Calendar, Email, etc.). These are a "special" type of table in Dataverse where you would actually "Enable" activities on the the table which will enable the ability to track activities.

See the section where it says "Make this table available when"...

[PhilipL]

Hello Drew, 

Thank you for this answer. However, I still don't understand the relationships can work when we are not allowed to define which columns the relationship should do a Lookup for. Let's take another example to explain where my knowledge is missing and perhaps that makes more sense.

Lets say we have:

SalesHeader Table: Contains information about who make the order.
SalesLine Table: Contain information about what they ordered.
Item Table: Contain information about the items that exist on the SalesLine Table.

So for SalesHeader & SalesLine it is a (1:N) relation and normaly I would do a lookup on SalesID : SalesID. 
So for SalesLine & ItemID it is a (N:1) relation and normaly I would do a lookup on ItemID : ItemID.

However, when I am trying things in Dataverse I can match SalesHeader & SalesLine by setting up a relation. But when I attempt to make the Relation between SalesLine & ItemID, it simply does not work and it fetches wrong information and I have nowhere to specify that it is itemID I want to build the relationship on. 

So in SQL/Power Query etc. the Join would be 
SalesTable.SalesID = SalesLine.SalesID
SalesLine.ItemID = ItemTable.ItemID.

But now in dataverse I am unable to get SalesLine.ItemID = ItemTable.ItemID. to work.

Been working with "normal" Join-relationships for 10 years and I am unable to grasp how Dataverse can "just solve a relationship" without me having to specify which columns or even combination of columns that are needed between the tables. 

Of course it works, else D365 dynamics and such would not work, but I need to understand how to build these relationships for Custom Tables in Dateverse, so that I can use these for PowerPages, PowerAPP, PowerFlow etc. etc.

[GuidoPreite]

relationships in Dataverse are built using the primary key or both tables, is not possible to define a custom column when creating a relationship.

hope it helps

[PhilipL]

So how are we expected to be able to make any sort of useful Relationships? This means that all tables must have the same Primary key if the Primary Key cannot be defined. This is what confuses me. 

How am I suppose to be able to tie multiple Dimension tables to a Fact Table.  

I mean if I have Production Table with the Manufacturing Order as the Primary Column, How am I suppose to connect the Production Table with for example Employee Table to to use the "Created by" containing the EmployeeID column in Production Table and match it with the Primary Column of Employee Table which would be EmployeeID.

[GuidoPreite]

maybe I didn't understand your exact situation, but in Dataverse you can't create a relationship between 2 tables forcing a third table for holding the values.

Let's say you have 2 tables: Class (class_id, class_name) and Student (student_id, student_name)

inside Dataverse if you create a N:N relationship, the system will create an interest table (classstudent) with 3 columns: classstudent_id (the primary key), classstudent_class_id (lookup to class) and classstudent_student_id (lookup to student)

this intersect table is not customizable and if you want to fill this table the user need to manually specify with record wants inside this table. Is not possible to add attributes to this intersect table or define a column (or a third table) to indicate which records automatically should be included inside this relationship.

[dpoggemann · Accepted solution]

Hi @PhilipL ,

In your example on the Sales Order, Sales Order Line, Item.  

I would have the following to support this in Dataverse:

1. Sales Order Table
2. Sales Order Line Table with two lookup columns to create the relationship between the Sales Order and Item
   1. Sales Order - This one I would create as a Parental relationship as well (you can modify the relationship to identify if parental or referential) so if the sales order is removed, the sales order line is removed.
   2. Item - This would be kept as the default referential relationship so if you remove the Sales Order Line it does. not remove the Item.  

[anacarolinagr]

Hi @GuidoPreite !
The relationships are always build using primary columns?

This mean that I cant only use one column (primary column) of my dataverse table to creat all relations with other tables?

Ex: I have a table with 5 columns and I would like to creat a relationship with each of these 5 columns with 5 different tables. Is possible to do that?

[GuidoPreite]

N:N ootb relationships are always created by using the primary key

[anacarolinagr]

What about 1:N Relationships?