cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mcairney
Level: Power Up

Many to Many Table Relationships in SQL Server DB

I have a database where there is a many to many relationship between "List Name" in Table A and "List Member" in Table B.  I have a joining table that maps members to lists since a member can belong to many lists (call this Table C).

 

Currently, PowerApps doesn't support Views therefore I cannot do an easy filter to show the list of List Names on one screen and then go to the members of that list on the next screen .... I can get the ListID from Table A from the first screen but then that ID doesn't exists in TableB ... only in TableC where it maps to many MemberID values.

 

Is there a work around in PowerApps where I can click on a list name and then get the list of members?   I need this to be a delagated query to SQL Server since I may have more than 500 members in total and so I cannot pull them all back and filter within PowerApps.

 

Alternatively - please provide an update as to when Views will be supported.

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Level 10

Re: Many to Many Table Relationships in SQL Server DB

Hi @mcairney

 

There are a couple of ways to achieve what you need, particularly since you navigate from one screen to the other.

 

One way could be to have your navigation event to ClearCollect the relevant joining table records with a filter, which is delegated:

ClearCollect(MyJoiningRecords, Filter(TableC, ListName = MyListName))

Where MyListName is the chosen list in your first screen.

 

Then in the second screen you can use the 'in' operator, which I think is also delegated, so your items can be:

Filter(TableB, Id in MyJoiningRecords.Members)

If this does not work, you can even try this:

AddColumns(MyJoiningRecords, "ColumnA", LookUp(TableB, ID=MemberID, xxx))

 

I can clarify if needed.

 

Please also see this post for reference:

https://baizini-it.com/blog/index.php/2017/10/10/powerapps-101-many-to-many-relationships-between-ta...

View solution in original post

12 REPLIES 12
Meneghino
Level 10

Re: Many to Many Table Relationships in SQL Server DB

Hi @mcairney

 

There are a couple of ways to achieve what you need, particularly since you navigate from one screen to the other.

 

One way could be to have your navigation event to ClearCollect the relevant joining table records with a filter, which is delegated:

ClearCollect(MyJoiningRecords, Filter(TableC, ListName = MyListName))

Where MyListName is the chosen list in your first screen.

 

Then in the second screen you can use the 'in' operator, which I think is also delegated, so your items can be:

Filter(TableB, Id in MyJoiningRecords.Members)

If this does not work, you can even try this:

AddColumns(MyJoiningRecords, "ColumnA", LookUp(TableB, ID=MemberID, xxx))

 

I can clarify if needed.

 

Please also see this post for reference:

https://baizini-it.com/blog/index.php/2017/10/10/powerapps-101-many-to-many-relationships-between-ta...

View solution in original post

mcairney
Level: Power Up

Re: Many to Many Table Relationships in SQL Server DB

That option appears to work.   I might need to monitor my SQL DB to completely verify but it seems to do the trick for now.

Anonymous
Not applicable

Re: Many to Many Table Relationships in SQL Server DB


@Meneghino wrote:

Hi @mcairney

 

There are a couple of ways to achieve what you need, particularly since you navigate from one screen to the other.

 

One way could be to have your navigation event to ClearCollect the relevant joining table records with a filter, which is delegated:

ClearCollect(MyJoiningRecords, Filter(TableC, ListName = MyListName))

Where MyListName is the chosen list in your first screen.

 

Then in the second screen you can use the 'in' operator, which I think is also delegated, so your items can be:

Filter(TableB, Id in MyJoiningRecords.Members)

If this does not work, you can even try this:

AddColumns(MyJoiningRecords, "ColumnA", LookUp(TableB, ID=MemberID, xxx))

 

I can clarify if needed.


Hy @Meneghino,

 

I can display the Members in a gallery by using Filter(TableB, Id in MyJoiningRecords.Members), but I cannot make changes to the members in an Edit Screen. How can I save the changes?

 

Regards,

Kevin

 

Meneghino
Level 10

Re: Many to Many Table Relationships in SQL Server DB

Hi @Anonymous

As I have mentioned in some other posts, I avoid using edit forms in PowerApps since they are not easy to customise at all.

The best way to update any data source is to use the Patch() function.  Please let me know if you need guidance with this.

Anonymous
Not applicable

Re: Many to Many Table Relationships in SQL Server DB


@Meneghino wrote:

Hi @Anonymous

As I have mentioned in some other posts, I avoid using edit forms in PowerApps since they are not easy to customise at all.

The best way to update any data source is to use the Patch() function.  Please let me know if you need guidance with this.


Hi @Meneghino,

 

The reason why the changes won't save was an error in the Patch function after the SubmitForm. I have corrected the error and I can now save the member.

 

Regards,

Kevin

 

Anonymous
Not applicable

Re: Many to Many Table Relationships in SQL Server DB


@Meneghino wrote:

Hi @mcairney

 

There are a couple of ways to achieve what you need, particularly since you navigate from one screen to the other.

 

One way could be to have your navigation event to ClearCollect the relevant joining table records with a filter, which is delegated:

ClearCollect(MyJoiningRecords, Filter(TableC, ListName = MyListName))

Where MyListName is the chosen list in your first screen.

 

Then in the second screen you can use the 'in' operator, which I think is also delegated, so your items can be:

Filter(TableB, Id in MyJoiningRecords.Members)

If this does not work, you can even try this:

AddColumns(MyJoiningRecords, "ColumnA", LookUp(TableB, ID=MemberID, xxx))

 

I can clarify if needed.


Hi @Meneghino,

 

It seems the Filter function does not entirely work as intended. I thought it was working until I have more than one members in the list. The filter only shows one member. I have checked MyJoiningRecords, it does have more than one record. Any idea why the filter only returns one member?

 

This is my actual formula: Filter('[dbo].[CONTACT]', ContactID in CustomerContacts.CustomerContactID).

 

Regards,

Kevin

 

Meneghino
Level 10

Re: Many to Many Table Relationships in SQL Server DB

Yes, there may be a problem with delegation.  Although the documentation says that in is delegated, we know how buggy PowerApps is at the moment, so better check than trust.

I would try this:

CountRows('[dbo].[CONTACT]', 99 in CustomerContacts.CustomerContactID)

Substitute different values for the 99 and see if the result corresponds.  Try a value where all the records are in the first 500 and a value where they are not.

If the issue is with delegation, then we can think of how to solve it.

Anonymous
Not applicable

Re: Many to Many Table Relationships in SQL Server DB


@Meneghino wrote:

Yes, there may be a problem with delegation.  Although the documentation says that in is delegated, we know how buggy PowerApps is at the moment, so better check than trust.

I would try this:

CountRows('[dbo].[CONTACT]', 99 in CustomerContacts.CustomerContactID)

Substitute different values for the 99 and see if the result corresponds.  Try a value where all the records are in the first 500 and a value where they are not.

If the issue is with delegation, then we can think of how to solve it.


PowerApps is indeed buggy. When I replace 99 with an existing id, it returns all the contacts in the contact table. Then I try with a non-existing id, it returns nothing. I think the IN operator does not work in this case. Is there any alternative to this method? I have tried AddColumns, but it won't save changes to the table.

 

Regards,

Kevin

 

Meneghino
Level 10

Re: Many to Many Table Relationships in SQL Server DB

I would suggest the AddColumns and save changes with a Patch()

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,448)