Cant filter Many to Many relationship

(0) Share

Report

Posted on by CSegares

I have 2 dataverse tables with a N:N relationship:

On a Gallery, I can load Employees correctly, even allows me to get it associated Regions on a subgallery:

BUT my problem is when I try to filter the data:

 Filter(
 'Employees' As Employee,
 galRegionFilters.Selected in Employee.'Regions'
 )

Then it return only "blank" records:

Why is this happening? Is like the Employee.'Regions' is not working as expected.

Thanks.

Categories:

Building Power Apps

All responses (8)

Answers (0)

Sort by

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @CSegares ,

I am assuming that Regions is the field in the gallery selected record.

Ungroup( ForAll( galRegionFilters.Selected As _Data, Filter( Employee, _Data.Regions in Employees.'Regions' ) ), Value )

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.

MVP (Business Applications) Visit my blog Practical Power Apps
CSegares 27 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey @WarrenBelz
I addapted your code to make it work:

Ungroup( ForAll( colSelectedFilters As filters, Filter( Employees As Employee, filters in Employee.'Regions' ) ), Value )

But again, it return Blank records, its like when I try to use this fields: Employee.'Regions' some kinds of bug/error happen on the query and return wrong data. Starting to think Power Apps doesnt like N:N Relationships

However, if I remove that field from the query, it will return valid data:
Ungroup( ForAll( colSelectedFilters As filter, Filter( Employees As Employee, Employee.Name = "Carlos" ) ), Value )
Thanks for your help!
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

HI @CSegares ,

You need the field name in the Collection after filter, otherwise you are trying to compare a text value with a Table.
CSegares 27 on at

Like
a
(0)

Report
Copy link

Link copied!
colSelectedFilters contains a list of Region, which if am not wrong "in" operator is capable to compare 1:N tables/entitys of the same type (Regions in this scenario)

However I did the change just to test comparing String:
Ungroup( ForAll( colSelectedFilters As filter, Filter( Employees As Employee, filter.Name in ShowColumns(Employee.'Regions',Name) ) ), Value )

The same happens, as long the field Employee.Regions is part of the query, it will fail and fetch empty records:
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @CSegares ,

Firstly I see a Delegation warning there (which is expected) - how many records are there in Employees ? I also assume that both Name fields are Text - if so

Ungroup( ForAll( colSelectedFilters As filter, Filter( Employees, filter.Name in Regions.Name) ) ), Value )
CSegares 27 on at

Like
a
(0)

Report
Copy link

Link copied!

Employees only have 3 Records in total, and Regions 2.
Just to know, can you replicate this same issue in your environment, am insisting about something wrong with Power Apps when using N:N inside a Filter, look this other scenario:

Scenario 1: Regular equals String filter, 1 result

Scenario 2: String filter + N:N filter, 1 record returned but all the data blank

Scenario 3: N:N Filter + String filter, 3 blank results
As you can see switching the order of filters affect the result, is like if the N:N filter break something and leave the query result on an invalid status and doenst even evaluate the String filter.
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@CSegares ,

I am using SharePoint, but if both fields are Text, it should work.
CSegares 27 on at

Like
a
(0)

Report
Copy link

Link copied!

Well, thats the problem, is not a simple Text : Text comparison.

One side yes, is a simple Text filter, but the other need to make reference to the N:N feature that Power Apps offers:

Which is the same (or at least that what I thinks) to use Employee.Regions