Many to Many Table Relationships in SQL Server DB

[mcairney]

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.

[Meneghino · Accepted solution]

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

[mcairney]

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]

---

@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]

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]

---

@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]

---

@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]

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]

---

@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]

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