Filtered lookup field - filtered by the selections in 2 other lokup fields

[chrisuk01]

Hi

Can anyone offer any help in how I get about setting up the below, as I have tried a number of things including junction tables and its still not working as desired. This is a model driven app (dataverse)

I have 3 lookup fields on my form: Team >Category and Subcategory. Each of these have their own table.

Whatever I choose in the Team lookup field filters the available options in the category lookup field. I have this working, by adding a  lookup field to the man table to the Team table and configuring the related row filtering settings.

The part I am stuck on, is that based on the selection in Team and Category this should now filter the subcategory lookup field.

I have successfully filtered this column based on category alone, but I can't get it to include both.

I have also created a junction table which holds a lookup to all 3 tables to create the matrix of possibilities but still no joy.

It's worth pointing out that some of the categories can belong to multiple teams, and subcategories can belong to multiple categories.

Any help would be appreciated

Thanks

[ivan_apps]

As long as you have a lookup on the Subcategory table that links back to the Category table it should work the exact same way you configured Category filtering based on Team.

It should look something like this in the 'Filtering' properties for the Subcategory lookup.

Relationship to current table should be your Category lookup.

Relationship to this lookup's table should be "One-to-many" and the lookup in the subcategory table that links back to the Category.

[chrisuk01]

Thanks Ivan. I think I am understanding correctly but don't think that works and probably due to the way I explained it sorry.

I can filter the category based on the Team and I can filter the subcategory based on the Category. However, the subcategory needs to be filtered by Team and Category.

Example:
Team > Category > Subcategory

Team A > Category A > Subcategory A - This is fine

Team A > Category A > Subcategory B - this is fine
Team B > Category A > Subcategory A - this is fine

Team B > Category A > Subcategory B - Not fine. This Team shouldn't be able to see Subcategory B. They can see it because the subcategory is filtered by the Category. Not the Category & Team

Hope that makes sense and apologies if I have miss-understood what you replied with. 

[ivan_apps]

Oh I see, Category A exists in both Team A and B - but subcategory B only exists in Team A and Category A.

In this scenario if you want to continue with out-of-the-box configuration and not javascript, I would suggest that you duplicate Category A as "Category A (Team A)" and "Category A (Team B)" and then add the proper subcategories under each Category. It's duplicative but you'll have granular control over it if it's only for categorization purposes.  If you're reporting on this, you can easily use Power BI to remove/transform (Team A) or (Team B) so that Category A looks all the same.

Using javascript I believe you can pass custom fetchXml to the lookup as the filter criteria in order to add your double criteria condition. I know it was possible in the past but I haven't had to do this recently so I don't have code examples for you.

[EricRegnier]

Hi @chrisuk01,
Unfortunately, with configuration, we can only filter related lookups on model-driven forms based on only one field as @ivan_apps has shown. It is a common scenario so I would recommend to submit that idea at: https://ideas.powerapps.com/d365community/forum/f1458c72-ae29-ed11-9db2-000d3a8c451e and I'll vote 🙂
Confirming what Ivan is alluding to that it is possible with JavaScript using addCustomFilter from the client API. Where you only need to the FetchXml filter conditions. There's a good example in the doc at: info:  https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference/control...  
Hope this helps!