How to use a joining table in PowerApps

[steph_io]

Would there be documentation or demos that could help in the implementation of an intermediary table in power apps? 

In the CDS, if I have a many to many relationship between tables, the joining table is automatically created (but hidden). 

I'd like to have a custom entity to capture these relationships, but implement a time stamp of when the projects were active on the fund. 

[NewcombR]

I never use the N:N relationship because it is very limited. 

Instead, I create the intermediary table as an entity. 

Entity A has a 1:N to Intermediary Entity

Entity B has a 1:N to Intermediary Entity

typically, I set the Intermediary entity’s Name field to Autonumber and make it not required.  But I make the Lookup fields required.  I might also add some other fields on that Intermediary entity such as date fields or other needed to reporting requirements.

You use the same approach.

[steph_io]

How do I save the multiple project selections to the joining table?

I have a Multi-select Combo box used to select many projects to store in the joining table but it just saves 1 record. The source of the combo box is the Lookup field created by the CDS. 

The resulting content of the joining table should be :

Fund Source to Project
ID	FundSource	Project
A	FS1	234
A	FS1	235
A	FS1	243
B	FS2	566
B	FS2	567

[Drrickryp]

Hi @steph_io 

You shouldn't use a multiselect in the joining table.  Each line should be unique and describe a single fund source and a single project. That is the beauty of a joining table, you can approach the one to many relationships from both sides of the table. When you want to show the results in your app, put a dropdown and a gallery on the screen.  If you have the dropdown select a fund source and you filter the gallery with the joining table on the fundsourceID. The gallery can display the projectID and you use a label with a lookup(Projects,ID=ThisItem.projectID,Projectname) to show the name.   Alternatively, select a project in a dropdown and display the Fund source in the gallery by filtering the joining table on the project selected in the dropdown and looking up the fund source name based on the fund source ID.    The Joining table should contain the Primary key from each outer table.  For further details on how to do it, check out my post https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-... 

[steph_io]

Yes the Gallery works well for the selection and display, but what is the best method to create/write to that joining table?

At first I just had a many to many relationship between Fund Source and Projects.  I had a FundGallery and a combobox to select Projects.  Then with an Add Icon OnSelect = Relate(Gallery_Fund.Selected.'Fund Source ID',ComboBox_Prj.Selected);Reset(ComboBox_Prj_1)

With the CDS 1:N relationship, I now should use the lookup fields to create the connections. How do I do that?

[Drrickryp]

Hi @steph_io 

You can use an Edit form to enter the data into the junction table.  The way I have done it is to replace the textinput cards for the related tables with a dropdown control.  For example, make the Update property of the ProjectID card, Dropdown1.Selected.ID where the Items property of he dropdown is Projects.  For the UpDate property of the FundSourceID card, Dropdown2.Selected.ID where the Items property of the dropdown is FundSource.  If there are other fields that would be appropriate to the joining table, there should be additional cards for each one. To see how this is done in PowerApps, look at my post showing how to design the forms and galleries. https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Imple... 

If you design your joining entity and set up he relationships as one to many to the other entities, I think that when you create the edit form, PowerApps will automatically place a combobox in the card.   

To test it out, I let PowerApps create a new app based on the junction table.  I checked the Edit form and added the lookup fields.  They  automatically populate with combobox controls that list the items in the outer tables. Give it a try and let me know how it goes.