NEED HELP!!! SHOW MANY TO MANY RELATIONSHIP DATA IN GALLERY

[Thomas_JX]

I have two tables, user and bookings

this is many-to-many relationships, I want to show the user's data which does not exit in the booking table.

in this case the user Joel will be listed in gallery.

[PaulD1]

Looks like a one-to-many (one user can have many bookings). You'll want a User table, a Booking table and an intersect table (UserBooking) for 'many-to-many', unless this is the funky Dataverse many-to-many (which just hides the intersect table and causes trouble).

You want to find all users who do not have a booking (UserID exists in User table but not in Bookings table). How you do this will depend on your datasource. If you are using SQL you can do this in the DB by creating a View:

Select 
  UserID
From 
  Users 
Where 
  UserID Not In (Select UserID from Bookings)

For SharePoint things are trickier as you cannot process this on the 'DB'. You can write a similar filter to the SQL above in PowerFx: 

Filter(Users, Not(UserID in ShowColumns(Bookings,"UserID")))

...but it will not be delegable and will first become slow and later fail to return correct results as the data in the lists grows and then exceeds the delegable limits.

You can try the same in Dataverse as 'Not In' might be delegable now (Dataverse appears to be the primary focus for MS and so gets more new features/capabilities than SQL or SharePoint), but if not, you will have the same problem as with SharePoint.

[WarrenBelz]

Hi @Thomas_JX ,

Try

Filter(
   UserTableName,
   !(UserID in BookingTableName.UserID)
)

This is not Delegable

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Thomas_JX]

Hi @PaulD1 , I am using power app and Dataverse, is there any function I can do like left join.

or I have tried this function

Filter(
Users,
Not(
UserId in Bookings.UserId
)
)

got this error

[Thomas_JX]

Hi @WarrenBelz ,

Thanks for your help.

Got the error for BookingTableName

[PaulD1]

If you are using a Dataverse 'Many-to-Many' relationship I advise you change to a proper 'two parents and one child (intersect) table' design. This feature may be causing the 'specified column not available in this context' error, but I avoid Dataverse so can't say for sure.

The Dataverse 'features' to make data modelling easier, e.g. many-to-many, multi-value-fields, polymorphic-relationships' should be avoided at all costs (imo) - they just cause problems later, such as when you are trying to filter.

The delegation warning is as was mentioned - the filter will slow and then fail to return the correct results as the volume of data increases. The is one of the flaws of Dataverse - it does not have proper support for relational querying.

[WarrenBelz]

@Thomas_JX ,

You were meant to replace BookingTableName with the actual Table name, however if you are using Dataverse, I will leave you to @PaulD1 - I am a SharePoint user.

[Thomas_JX]

Hi @PaulD1 ,

I have create two tables with one-to-many relationships.

if I want to show the employees who do not have department by using filter function in power app, got the same error

[Thomas_JX]

Hi @WarrenBelz 

I have changed the table name to bookings, does not work for me.

Thanks for your help.

Kind regards,

Thomas

[WarrenBelz]

@Thomas_JX ,

As I said, I was assuming SharePoint as you had not specified your data source (the structure would be correct there)