Answered

Many to Many Table Relationships in SQL Server DB

(0) Share

Report

Posted on by 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.

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

Verified answer

Meneghino 6,949 on at

Like (2)

Report
Copy link

Link copied!
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-tables/

Was this reply helpful? Yes No
mcairney 2 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

@Meneghino wrote:
I would suggest the AddColumns and save changes with a Patch()

Thank you for advice. This is what I did to solve the problem. I display the contact names in a gallery by using the AddColumns function. Then I use the ContactIDs in the gallery to display and save the contact.

Here are the formulas:

Browsescreen
AddColumns(Filter('[dbo].[Customer_CONTACT]', CustomerID = CustomerKey), "FullName", LookUp('[dbo].[CONTACT]', ContactID = CustomerContactID).FullName, "ContactID", CustomerContactID)

Detailscreen
LookUp('[dbo].[CONTACT]', ContactID = BrowseGallery_Contact.Selected.ContactID)

Editscreen
SubmitForm(EditForm_Contact); If(EditContact = false, Patch('[dbo].[Customer_CONTACT]', Defaults('[dbo].[Customer_CONTACT]'), {CustomerID: CustomerID, PrimaaryContact: 0, Email: EmailAddress}))

Regards,
Kevin

Was this reply helpful? Yes No