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:
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?
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;
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?
Hi @sandra1 ,
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:
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.
If you insist in using view, I suggest you try SQLServer.
You could display data of view in SQL Server. (can not write)
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
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
There are 2 types of many-to-many relationships,
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.
Check out new user group experience and if you are a leader please create your group
We are excited to announce that Demo Extravaganza for 2021 has started!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Don't miss the call this month on June 16th - 8a PDT