Lookup Filtering in the modern Power Apps Grid Control

(0) Share

Report

Posted on by shalem120kjh

105

Hi all,

I'm using the modern Power Apps Grid Control with a subgrid inside a form, and I want to filter the results in a lookup column, based on a related entity:

The problem is that the modern designer only allows me to choose a "Lookup view", and does not allow me to filter the results:

The feature/setting I'm looking for does exist in the classic designer ("Only show records where" on the second screenshot ahead). It works as expected on the old Editable Grid control, but does not take effect on the modern Power Apps Grid control:

Any help or advice will be greatly appreciated.

Thanks.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @shalem120kjh

You are correct it is still not able to do this as its not supported directly.

You either have to use the old, or leverage scripting (aka javascript and fetchxml) and even then its not fully supported. The Natural Language filtering is still in review, but you can do filtering "after" the fact as the user... but that doesn't give you what you want, so your options are limited to what you can do beyond what I put here, such as building your own PCF Control, or PowerPage/Canvas App in the grids place.

If these suggestions help resolve your issue, Please consider Marking the answer as such and also maybe a like.

Thank you!
Sincerely, Michael Gernaey

1 people found this reply helpful.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,704 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @shalem120kjh

As said by @Michael E. Gernaey by OOB behavior we cannot achieve the same you can use Custom Logic to achieve the same.

Please mark as answer if my suggestion helps.
Subscribe here for More Useful videos : https://www.youtube.com/@rampprakash3991

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

Like (0)

Report
Copy link

Link copied!

@Ram Narayan

Why would someone Mark your answer, which just agree's with my answer, versus @shalem120kjh actually marking mine since it's the actual answer..

Come on people stop trying to just grab onto my answers for points and please posters, regardless of who @shalem120kjh please do not post if you do not plan on coming back to mark things or comment, we are free volunteers and its massively disrespectful to just ghost us after answering you.

Was this reply helpful? Yes No
shalem120kjh 105 on at

Like (0)

Report
Copy link

Link copied!

Hi @Michael E. Gernaey,

I too didn't understand the point in Ram's answer, which didn't put any new information on top what you already wrote, and it appeared "strange" to me as well.

I do appreciate your help and have a lot of respect for you and others in this forum. I even marked your answer as "helpful" even before your latest comment. But in this case, after thinking about it, I decided not to "mark it as an answer". I imagined someone searching for this issue and then sees this post with the "Answered" tag, go inside the post and sees that the "answer" is "This is not possible". I tried the JavaScript and that is also not supported for my case, and AI filtering is for the rows and not the lookup values, so.... it might be an answer, but not the "solution" I was hoping for.

Thank you anyway for the great help and contribution to this forum!

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

Like (0)

Report
Copy link

Link copied!

Hi @shalem120kjh

Thank for your the details its appreciated :-)

I am certain you can get this with JavaScript and in the end, you want to pre-filter correct? You don't want them to be able to filter (after the fact) correct?

If you can validate that for me, let's see if we can create what you want.

Could you do me a small favor and just verify it this way too, just how my brain works

1. Entity a has a 1 to Many to Entity b

2. You want the grid to show entity b but with a pre-defined filter correct (essentially at design time)?

I have another option I think would work as well but want to Check with you first.

Cheers!

Was this reply helpful? Yes No
shalem120kjh 105 on at

Like (0)

Report
Copy link

Link copied!

@Michael E. Gernaey, thanks again for taking the time to investigate this and trying to help. This is very much appreciated.

Both entities A and B have a many-to-one relationship with entity C, but on top of that - entity A also has a many-to-one relationship with entity B.

The subgrid is for entity A, and one of the columns in this subgrid is the lookup to entity B. In this column I want the user to see only B items that are related to the same C item that A is related to.

Back to entity names (you can now refer to the screenshots in my first post), a Project Mat is related to an Estimate. In the Estimate form I have a Project Mats subgrid. One of the columns in the subgrid is for a Project Phase, because a Project Mat is also related to a Project Phase. Now, because every Project Phase is also related to an Estimate - I want the user to be able to select only a Project Phase that is also related to the same Estimate as the Project Mat.

This desired behavior might be complicated to explain in plain words, but is a very basic requirement, therefore it has a section of its own for lookups in Forms. See the Filtering section in the Phase Lookup properties:

This is exactly the filtering capability I'm trying to achieve - with one difference: I want it to work in the subgrid of Project Mats and in addition to the Project Mat form.

As mentioned (with screenshots) in my original post, this is possible through UI for the old Editable Grid (which has bad UI, therefore I'm trying to avoid it). For the new modern Power Apps Grid - the modern UI doesn't have this feature. The classic designer for this grid does have this feature, but it does not take effect in practice.

If you survived this second round of explanations (which entitles you a medal even before answering) - it probably verified for you that yes, I want to pre-filter for the user. I don't want to user to see irrelevant options and filter them by themselves.

I tried the JavaScript approach with the help of some articles and Co-Pilot (specifically, the OnRecordSelect event), but got stuck because apparently the modern Power Apps Grid does not expose the type (e.g., a lookup) of controls in the API, and therefore methods like the addCustomFilter cannot be used here, as mentioned (and verified by me) in this post ("a lookup control doesn’t have the addCustomFilter method, so we cannot implement filtered lookup functionality on Power Apps grid controls").

And the PCF way is out of my scope for now...

Thanks,

Gil.

1 people found this reply helpful.

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

Like (0)

Report
Copy link

Link copied!

Hi @shalem120kjh

This is great and perfect explanation. I am prepping for my trip to the PPCC but will find time to look at this and get something working, even if its a PCF Control for you that others can then use.

Of course... I dont mean that rudely its me lol thinking to myself, you could embed a small Power App, which would allow you to show this and it could be the modern controls in the Power App.

As I ponder on this, would you consider doing that approach? Essentially (as we do not want to use the old Grid), are there specific features, needs that would preclude you. I am also wondering and have to double check if the Grid is GA... I need to double check the Release date, but... I want to make this work as now I am annoyed lol just in general about it. So thank you for the Challenge.

I will work on a solution or 2 while you think about whether embedded a Canvas App, in place of the grid, that would allow you to filter but not the user.. hmmm... lol PCF, Canvas, Javascript/Typescript, a button in the Command bar that would allow you to have a (admin only) popup to set a filter that gets stored in Dataverse...

Sorry I started thinking and blabbing lol my wife knows when I have that thinking face and runs away from me :-)

Was this reply helpful? Yes No
shalem120kjh 105 on at

Like (0)

Report
Copy link

Link copied!

Hi, @Michael E. Gernaey,

It is noble to suggest creating a PCF for the community, but at least in my case I think it is an overkill. Since the desired behavior is practically there (in the UI) just doesn't work as expected, I was kind of hoping someone in the forum will just tell me that I missed a small configuration bit and it is all going to work. The fact that it is working for the old grid but not for the modern grid is almost like a bug. Anyway, embedding a canvas app or creating a PCF is a route that I prefer not to take since it will require a lot of work just to get parity with current editable grid behavior, so please don't put this effort for me. That said, I already found myself solving some challenges with JavaScript magic (written by Copilot) so I was willing to make that effort to solve this challenge as well - but couldn't do it myself.

Thanks again and enjoy PPCC!

Gil

Was this reply helpful? Yes No