cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sandra1
Helper I
Helper I

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

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?



5 REPLIES 5
v-yutliu-msft
Community Support
Community Support

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"

1212.PNG

 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,

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Frequent Visitor

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?

@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
Solution Sage
Solution Sage

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.

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (93,995)