web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / NEED HELP!!! SHOW MANY...
Power Apps
Unanswered

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

(0) ShareShare
ReportReport
Posted on by 8

I have two tables, user and bookings

Thomas_JX_0-1659496008120.png

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.

 

Categories:
I have the same question (0)
  • PaulD1 Profile Picture
    2,914 on at

    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 Profile Picture
    155,779 Most Valuable Professional on at

    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 Profile Picture
    8 on at

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

     

    Thomas_JX_0-1659501977713.png

     

    got this error

  • Thomas_JX Profile Picture
    8 on at

    Hi @WarrenBelz ,

     

    Thanks for your help.

    Got the error for BookingTableName

    Thomas_JX_0-1659502211137.png

     

  • PaulD1 Profile Picture
    2,914 on at

    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 Profile Picture
    155,779 Most Valuable Professional on at

    @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 Profile Picture
    8 on at

    Hi @PaulD1 ,

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

    Thomas_JX_1-1659519576441.png

    Thomas_JX_2-1659519594768.png

     

    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_0-1659519565133.png

     

  • Thomas_JX Profile Picture
    8 on at

    Hi @WarrenBelz 

     

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

    Thanks for your help.

    Kind regards,

    Thomas

  • WarrenBelz Profile Picture
    155,779 Most Valuable Professional on at

    @Thomas_JX ,

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

  • PaulD1 Profile Picture
    2,914 on at

    Hmm - not sure why you are still getting that error assuming all the table and field names are correct.

    Is the Department an Option Set? Option Sets are another Dataverse 'enhancement' that are not sound and are difficult to work with.

    You might have more luck posting on the Dataverse subforum as I believe the issues you are hitting now are due to the use of Dataverse-specific 'features'. I'm afraid my focus is more on SQL as a datasource.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 477

#2
WarrenBelz Profile Picture

WarrenBelz 341 Most Valuable Professional

#3
11manish Profile Picture

11manish 317

Last 30 days Overall leaderboard