CDS many-to-many relationship and joining tables that have no relationship

[sandra1]

Hello, 

I'm trying to create some entities in CDS for my existing database schema to use it in model-driven applications. I want to do it from scratch (and not use the "Get Data" option). 

Here are my questions:

Question 1:

I have modeled a many-to-many relationship between entity A and entity B. But I don't know how to insert data for this relationship because I don't have a form or view for it. (Normally I would think that a third entity is created with the relationship name).

How can you get/insert data for this relationship? 

Question 2: 

I also want to create a view that contains data from more than one table, even if the tables have no direct relationship. 

So I would join the tables in SQL, but when I want to create a view in CDS, I can only select related fields. 

For example, I have a "likes" table with the data: 

Person A; Person B; 

Anna;       Peter

Peter;       Anna

Mike;       Peter

Anna;      Mike

and only want to get rows where people like each other (output should be Anna, Peter; Peter).

How to create such a view with CDS?

[v-yutliu-msft]

Hi @sandra1 ,

Q1:

Do you want to know how to update entity that has many-to--many relationship?

Since in entity that has many-to--many relationship, there is no lookup field.

So you could update it very easily.

1)update it in powerapps

create an app connected with this entity, use collect/patch function to update

2)update it by clicking "edit data in excel"/"get data"

 3)use data integration to import data to this entity

Here's a doc about how to make this:

https://docs.microsoft.com/en-us/power-platform/admin/data-integrator

Q2:

I'm afraid it's not supported to create view in CDS currently.

I suggest you express your views about adding this function on PowerApps Ideas Forum. Your precious feedback will be very helpful for our work.

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

If you insist in using view, I suggest you try SQLServer.

You could display data of view in SQL Server. (can not write)

Best regards,

[sandra1]

Hello @v-yutliu-msft,

Thank you very much for your answer. 

To question 1: 

Sorry, I don't think I expressed myself very well. I know that I can write data for Entity A and Entity B in the tables. (and I can do that with "get data/gedit data in excel" etc.)

But when I model a many-to-many relationship, I usually want to know which entry of realisation A has a partner in relation B.

For example, a book can be written by more than one author and an author can write more than one book. 

So I thought if I created a many-to-many relationship in CDS, it would create a new entity (table) "written by" for that relationship, where I could write down who wrote a book. But that's not the case (correct me if I'm wrong). So where can I store the information "wirtten by"? Is the solution to create a third entity "wirtten by" with two many-to-one relationships to author and book? But if that's the case, I do not know in which case I should use a many-to-many relationship?

You are also sad :

"So you can easily update it.

1) update it in powerapps

Create an app associated with this entity using the Collect/Patch function."

The Collect and Patch functions are not available for model-driven apps. Is that correct? 

To question 2: 

You recommend using the SQL Server. They are only supported by canvas applications, aren't they? 

Thanks in advance

[SimoneBuscaino]

Hello to all,

I've got the same problem.

I created two entities to link together with a many-to-many relationship, normally I would think of a third table where it is possible to link the two records of the two tables, but in this case it seems not to be.

Can anyone help us?

[MkKhan]

@SimoneBuscaino  The third table is basically not visible in the portal, it is created and maintained by CDS/Dataserve. You will just use relate and unrelate feature to associate and remove that association. 

"With Many-to-many relationships a Relationship (or Intersect) table stores the data that associates the tables. This table has a One-to-many table relationship with both of the related tables and only stores the necessary values to define the relationship. You can’t add custom columns to a relationship table and it is never visible in the user interface."

View this documentation https://docs.microsoft.com/en-us/powerapps/maker/data-platform/create-edit-nn-relationships

Also this for relate and unrelate and further details on relationship

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-relate-unrelate

[Fubar]

There are 2 types of many-to-many relationships,

• One is 'automatic' where you explicitly choose to create a many-to-many.  In this case in the background the link entity/table is created and contains the GIUDs of the records at both ends of the relationship.  In the automatic one you cannot add additional attributes/columns to it - it is just to link not contain additional data.
• The other is 'manual' where you create the intersect entity i.e. another entity/table that has a lookup to entity/table A and another lookup to entity/table B. As you create the intersect, you can add additional attributes/columns.

Key benefit of the automatic is that it is quick to setup (and at least in the Model Driven apps, not 100% sure with a Canvas app) you don't have to do anything else - it just works.  The downside is that there are limitations such as you can't add additional Columns.

For loading initial data into an automatic many-to-many you can also look at XrmToolBox, there is a plugin for that tool that allows you to populate the automatic intersect entity (can use the GUID or text) e.g. you create a file that contains the pairs of GUIDs for the records at the end of the relationships, and import that file.