Filtering a gallery, CDS and Many to Many relationship

[MagnusGöransson]

Hello,

I'm building a race management system (for RC cars) using CDS and PowerApps. When done I will put it in the gallery so that you can download it. However, I'm banging my head against the wall because of an irritating issue.

Among other entities and one called RaceDrivers and one called RaceSessions. The RaceSession keeps the different competitions and information regarding which track, what date and so on. The RaceDrivers entity is the driver roster.

Between RaceDrivers and RaceSessions there are a many to many relationship because many drivers can attend many competitions.

I use Relate to "bound" a driver to a session (competition) and it works great.

My problem is to create the Items formula for a gallery that shows all drivers for a specific RaceSession. I have tried basically everything here but with no luck.

RaceDriver entity relationships:

RaceSession entitys relationships:

One of my tests for gallery Items

Any one have any ideas? 🙂

[GarethPrisk]

There is a simpler way to accomplish this.

The inclusion of an out-of-box N:N relationship provides a table of the relationship records for each side of the relationship. Simply, this means that if Entity A and Entity B are related in N:N, there will be a table of records on each side like Entity A.Entity B and Entity B.Entity A. No need to filter, it will include the child records for a given record.

In your example, make your Items property something like RaceSessions.RaceDrivers

This explains some of the high-level mechanics (starting with using the records relationship as a source), and then how to easily relate/unrelate (which I think you already figured out). https://powerapps.microsoft.com/en-us/blog/relate-records-in-many-to-many-relationships/

[yashag2255]

Hi @MagnusGöransson 

Can you try to update the expression as:

Filter(RaceSessions, RaceSession = ActiveSession.RaceSession).RaceDrivers

You can also refer to: https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Drrickryp]

Hi @MagnusGöransson 

One of the problems with using the OOB solution of a many to many relationship is that it does not allow for more details to be included in the relationship.  The typical way that databases describe a many to many relationship is with a junction table that is basically a table that includes two one to many relationships.  The advantage of doing it this way is that table can include additional fields that describe the details specific to only that particular episode.  This would involve creating your app using all three tables but instead of the OOB many to many relationship, create two one to many relationships to the junction table.  Then you can approach it using lookups.  I describe this approach in my blog post https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-...  and the follow up post https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Imple... .  

[MagnusGöransson]

Thanks a lot for your quick responses!

I know that i can accomplish this using a junction table ( two one-to-many relataionships), but as many-to-many is available i would like to try it out 🙂

I tried this:

But i get this "column"-error. Is that a limitation when using many to many realtionships?

/Magnus

[yashag2255]

@MagnusGöransson 

Have you enabled the experimental feature for common data service experimental connector? Reference: https://powerapps.microsoft.com/en-us/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[GarethPrisk]

Try ActiveSession.RaceDrivers as your Items property.

This will leverage the relational data functionality of CDS, and show the child records from the (presumed) global variable for your current RaceSession record. You may need to first include a field from the Drivers side, for it to populate, but this will give you the related records without needing to do filter/explicit retrieve - the data is already included with the parent record.

[Django]

I followed the default way of getting N:N relationships:

galCurrentUser.Selected.'SolProFS Engagements (isp_solprofsaudit_Preparer)'

So linked the SolProFS Engagement entity to the User table with a N:N. The expression above works fine!

However when adding SortByColumns

SortByColumns(
    galCurrentUser.Selected.'SolProFS Engagements (isp_solprofsaudit_Preparer)',
    "createdon",
    Descending
)

or even Search

SortByColumns(
    Search(
        galCurrentUser.Selected.'SolProFS Engagements (isp_solprofsaudit_Preparer)',
        txtOFZoeken_EN.Text,
        "isp_solprofsauditname"
    ),
    "createdon",
    Descending
)

to this logic we get Delegation Warnings.

I understand that this N:N is a different approach but looking athttps://docs.microsoft.com/en-us/connectors/commondataservice/ ==> these expressions should delegate right?

[Drrickryp]

Hi DJ

Take a look at https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-Gallery-from-CDS-many-to-many-relatio...  to see if this approach helps you.

[Django]

Hi @Drrickryp , please forgive me but can you explain some more how this post can help me?

Do you mean to create different views? That workaround would not be satisfying nor scaleable...