Unanswered

Filtering Gallery by Entity Many-to-Many Relationship

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi all!

I wanted to know how I could filter an entity in a gallery by a field in it's many-to-many related entity?

Background info: I have Entity1 and Entity2. Entity1 and Entity2 have a many-to-many relationship between themselves. 'Filter Field' is a two-option type field in Entity2 that I would like to use in my filtering. 'Entity1 Name' is the primary field for Entity1.

Eg: I want to have a gallery that shows a list of records in Entity1, and I want to filter it by 'Filter Field' in Entity2. This is what I have but it doesn't work fully:

Filter([@Entities1], 'Entity1 Name' in Distinct(Filter[@Entities2[, 'Filter Field' = 'Filter Field (Entities2)'.No), 'Entity1 Name').Result)

The aforementioned works but returns a delegation warning saying "The 'Filter' part of this formula might not work correctly with large data sets." Since I am working with large data sets this is a problem.

I've also tried the following work-arounds, but sadly none of them work:

LookUp([@Entities2], 'Filter Field' = 'Filter Field (Entities2)'.No).Entities1 // only returns the first record of Entity1 that satisfies the criteria, not every record. Taken from the following article https://powerusers.microsoft.com/t5/Common-Data-Service-for-Apps/Filter-gallery-by-related-entity-records-Many-to-many/m-p/401368#M2950
Filter([@Entities2], 'Filter Field' = 'Filter Field (Entities2)'.No).Entities1 // error because of Entities1, says "The specified column is not accessible in this context
Distinct(Filter([@Entities2], 'Filter Field' = 'Filter Field (Entities2)'.No),Entities1.'Entity1 Name') // just plain doesn't work

If anyone knows how to filter an entity based on a field in another entity it has a many-to-many relationship with, please let me know!

Thanks!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Joel CustomerEffective 3,224 on at

Like (0)

Report
Copy link

Link copied!

so I'm going to state the obvious here: N:N relationships are a pain to work with. I've recently had some similar requirements, and the way I got around them was to collect the data in a collection. Thanks to input from my smart colleagues @PowerAddict and @GarethPrisk I was able to make it work. But things like AddColumn in collect are also not delegable, so the question is do you have more than 2,000 related records in this scenario? Delegation may be ok if your data set is under 2,000 records.

Also helpful (although not exactly your scenario) is @nickdoelman 's latest blog post https://readyxrm.blog/2020/09/04/how-to-implement-a-common-data-service-many-to-many-field-on-canvas-power-apps/

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

Like (0)

Report
Copy link

Link copied!

@jlindstrom
Unfortunately I do have more than 2,000 related records. I'm thinking of just doing a separate entity that's called "Entity1 Entity2 Link" with two lookup fields, one to Entity1 and one to Entity2. Basically create a child to Entity1 and Entity2 that tracks their many-to-many relationships. It's a pain that I have to do this, and kind of a waste since I have no relationship metadata I need to track, but I don't see much of a workaround. Here's to hoping Microsoft will address the sorely lacking functionality of their CDS many-to-many relationships.

Thanks for linking that blog post; it is quite helpful and I used it last week in another part of my app. I'm sure whoever is googling this issue will get good use out of that article.

Thanks again!

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

Like (0)

Report
Copy link

Link copied!

@jlindstrom

As a follow-up, how did you collect all of the data in a related entity into your collection?

I have two galleries, one on each page. On Page1 I have a gallery for Entity1 where the OnSelect property is the following:

Select(Parent);
Set(varSelected, ThisItem.cr123_GUID);
Collect(myCollection, ThisItem.Entities2);
Navigate(Page2)

On Page2 I have a gallery with the "myCollection" as the data source. This gallery has the number of records in my related entity, but the information is all blank. When I go into myCollection, I see that the only information stored in them is the GUID for the records in Entity2. How do I pass all fields into myCollection, without calling them out individually? As in, without doing the following:

Collect(myCollection, ThisItem.Entities2.Field1, ThisItem.Entities2.Field2, ... etc.)

Thanks again!

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

Like (1)

Report
Copy link

Link copied!

I believe I came up with a solution for my follow-up collection problem that works out well. With a mix of collections, lookup, and variables I am able to filter a second gallery by a field in the many-to-many related entity featured on the first gallery.
Eg: Entity1 on Page1 has a gallery called "Gallery1." Entity1 has a many-to-many relationship with Entity2. I want to select a record of Entity1 in “Gallery1” and travel to Page2, where I will see a gallery of Entity2 that is filtered by the records in Entity2 that are related to the record of Entity1 that I selected on Page1. In other words, if Entity1 is Categories and Entity2 is Books, I would select a category and on the next page see a list of books that pertain to the selected category. The following code is how I achieved this:
The “Items” property of Gallery1:
            Entity1;

The “OnSelect” property of Gallery1:
            Select(Parent);
            Set(variable, ThisItem.Entity1GUID);
            ClearCollect(myCollection, LookUp([@Entities1], Entity1GUID = variable).Entities2);
            Navigate(Page2);

The “Items” property of Gallery2:
            myCollection;

And there you have it! My follow-up question is solved!
Now, I still can’t figure out how to answer my original question. Originally, I wanted a gallery for Entity2 filtered by a field value in Entity2. The gallery I want to filter is on my home screen, but cannot get it to work. Right now I have created a collection that has all of the GUID’s of the records in Entity2 that meet my field value I want to filter by. I want to get the Entity2 record, and tried the following in the OnVisible property of the home screen, but it didn’t work:
            ClearCollect(collection1, Filter([@Entities1], ‘Filter Field’ = ‘Filter Field (Entities1)’.No).Entity1GUID);
            ClearCollect(collection2, Entity2);
            ClearCollection(collection2, ForAll(collection1, LookUp([@Entities1], Entity1GUID in collection1).Entities2));

Then the “Items” property of my gallery is the following:
            collection2;

This doesn’t work, and I feel like there is something so simple I’m just not getting here. If anyone has any idea what to do to solve my original problem, please let me know.
Thanks!

-----------------------------------------------------------------------------------------------------------------------------------------------
EDIT: My proposed solution in the first half actually doesn't work. I don't need to make the collection, so remove the "ClearCollect" line from the "OnSelect" property of Gallery1. Then, just set the "Items" property of Gallery2 to:
LookUp([@Entities1], Entity1GUID = variable).Entities2

This seemed to work and pass in all of the information about Entity2. Not sure why making a collection doesn't work, but at this point it doesn't matter.

Was this reply helpful? Yes No