Filter gallery where related table has no rows

(1) Share

Report

Posted on by DrRobotnik

Hi folks,

Having some difficulty identifying the correct method to achieve the following:

- I have multiple related Dataverse tables - TableA (a list of customers) has a One to Many relationship with TableB (a list of orders).

- I have a gallery within Power Apps that filters all the rows from TableA (customers currently assigned to user)

- I want to be able to filter the gallery to show only customers from TableA - that don't have any orders in TableB

Hopefully I've explained the problem in enough detail, appreciate any suggestions on how this could be achieved!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (8)

Answers (2)

Verified answer

Ami K 15,687 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!
@DrRobotnik - example below:

Filter( TableA, !( 'Your Primary Column' in Distinct( TableB, 'You LookUp Column'.'Your Primary Column'' ) ) )

Please note however that the Distinct function and Not ( ! ) operator are not delegable.

Was this reply helpful? Yes No
DrRobotnik 15 on at

Like (1)

Report
Copy link

Link copied!

Thanks Amik - unfortunately I will have large data sets. Can you think of any way to make this delegable?

Was this reply helpful? Yes No
Michael E. Gernaey 53,969 Moderator on at

Like (3)

Report
Copy link

Link copied!
Hi @DrRobotnik

@Amik gave the best direct route.

Here are a couple of other ideas.

1. Create a View and have the view based on your criteria, then you just have to loop through the views list as it won't have any associations

2. You can write a Power Automate Flow that calculates them at night

3. You can run code like this to do it in real-time but I have no idea how many records you have or how long it will take.

// here loop through ForAll('Parent Table 'As MPD, Collect(MyParentDataWithNoChildren, With( {ChildCheck: Filter(ChildDVTable, MyParentDataId = MPD.ParentID) }, If(CountRows(ChildCheck) = 0, ThisRecord or MPD.ThisRecord Winging it here lol ) ); ); );

I wrote the last one to avoid all Delegation but it will take a bit probably if you have lots of records

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@DrRobotnik - the server will not accept being delegated the execution of non-delegable operations unless you can return a query which falls below your delegation limit (default 500, max 2000 if increased via settings).

If you can "pre-filter" your data to return rows below your delegation limit using delegable Filters, you can then run the non-delegable operations on that smaller data subset.

See example below using the Filter function to filter on a Single Line Text column and the Created On field. As long as I am certain that this query will return rows below my delegation limit, I will be fine to run the non-delegable Distinct function and Not operator:

With( { _prefiltered_data: Filter( TableA, Column1 = "Text1", 'Created On' > DatePicker1.SelectedDate && 'Created On' < DatePicker2.SelectedDate ) }, Filter( _prefiltered_data, !('Your Primary Column' in Distinct( TableB, 'You LookUp Column'.'Your Primary Column' )) ) )

Was this reply helpful? Yes No
DrRobotnik 15 on at

Like (0)

Report
Copy link

Link copied!
Thanks @Amik @FLMike - I've tried the following, and I'm hitting errors if you can shed any light?

I'm creating a pre-filtered collection and the below code is to filter this:

Filter( _prefiltereddata, !(CustomerID in Distinct( 'TableB', 'TableALookUpColumn'.CustomerID )) )

I get the following errors:
"The specified column is not accessible in this context"
"Invalid Schema, expected a one column table"

I'm struggling to understand why, because the Distinct query works as expected:

Distinct( 'TableB', 'TableALookUpColumn'.CustomerID )

Thanks 🙂

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@DrRobotnik -

Please could you share how the Collection is being generated.

Are you certain there is a many-to-one relationship between Table B (many) and Table A (one) ?

Was this reply helpful? Yes No
DrRobotnik 15 on at

Like (1)

Report
Copy link

Link copied!
This ended up being the correct solution, I rewrote it several times and in the end I think the issue was potentially because TableA and the lookup column to TableA are named the same which was causing some confusion (either to me, or to Power Apps - or both!)

Filter( TableA, !( 'Your Primary Column' in Distinct( TableB, 'You LookUp Column'.'Your Primary Column'' ) ) )

Thanks again @Amik 🙂

Was this reply helpful? Yes No
Verified answer

Ami K 15,687 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!
@DrRobotnik - for situations like this, use the As operator to handle disambiguation:

Filter( TableA As data, !(data.'Your Primary Column' in Distinct( TableB, 'Your LookUp Column'.'Your Primary Column' )) )

Further reading: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/operators#thisitem-thisrecord-and-as-operators

Was this reply helpful? Yes No