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

How to use a joining table in PowerApps

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. 

visiotabls.JPG

 

 

 

 

5 REPLIES 5
Highlighted
Advocate II
Advocate II

Re: How to use a joining table in PowerApps

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.

Highlighted
Helper I
Helper I

Re: How to use a joining table in PowerApps

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
IDFundSourceProject
AFS1234
AFS1235
AFS1243
BFS2566
BFS2567

 

Highlighted
Super User
Super User

Re: How to use a joining table in PowerApps

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-... 

Highlighted
Helper I
Helper I

Re: How to use a joining table in PowerApps

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?

 

 

 

Highlighted
Super User
Super User

Re: How to use a joining table in PowerApps

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.   

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,920)