How to make a lookup in a child table form filter on a value set for a field in the related parent table?

[HenningStrand]

A bit difficult to describe, but here goes:

I have two related master data tables for Countries and Cities, where the City table has a lookup for Country.

Then I have two related tables for Deliveries and 'Delivery details'.

The Form for adding a new delivery has a subgrid component displaying the related delivery details.

In the Deliveries form there is a Country lookup that connects to Countries table (+ a lot of other fields). while in the delivery details form (that opens from the subgrid) I only need to have a lookup to City.

(One delivery is always country specific while each related delivery row ('Delivery details') needs city specified, while not having to repeat the already set Country value set for the Delivery parent).

So then: How do I make the City lookup in the 'Delivery details' form filter to only show the cities related to the country defined for the parent Delivery row?? I cannot see that I get access to the parent row's field values when trying to set the filter for the child row's lookup fields.

Any smart tips? Thx

[EricRegnier]

Hi @HenningStrand,

It is possible filter out-of-the-box lookups based on another lookup and to filter subgrids based on a lookup on the form:

Filter Lookups

On your form designer, switch to classic view by clicking the "Switch to Classic" button in the top menu, then in the classic form designer, select your lookup and click "change properties". Configure the filtering as per below:

Filter subgrids

There are 2 main ways: create a quick view form (QVF) or Form Component Control on the lookup entity that you want to filter. Add a subgrid on that form with filtering on the current record (see image below), then add the QVF or set the Form Component Control on the main form and you'll see the related records filtered to the lookup.

Hope this helps! 

[HenningStrand]

Hi and thanks, but the related records filtering still only gives me access to filter on the fields in the same table. Switching to classic does not alter that. For the City lookup it does not give me the option to filter on the Country value. So my question is still how do I filter on values set for the related parent record. 

I am thinking that I might need to add an OnLoad event to the details form that can look up the Country value of the master row and then use that to filter the City field. Not sure if this would work well, though..

[EricRegnier]

Hi @HenningStrand, yes that's right. It only works if the related field is on the table. The only option is with JavaScript as you mentioned on the OnLoad of the form and the OnChange of the Delivery field on the "Delivery Details" form.
Hope this helps!

[HenningStrand]

My current solution is to do the OnLoad event on the child table form, using the parent lookup ID to fetch the country lookup value on the parent item, then patching a hidden lookup field connected to the same countries table and using this field to filter the cities. 

[Fubar]

@HenningStrand Trying to understand your description of what you did - think you are saying you have added another Lookup on the Child entity and then used related lookup filtering based on the other lookup - if so, you could have used a custom filter rather than adding an extra lookup field

formContext.getControl(arg).addCustomFilter(filter, entityLogicalName)

// where "filter" is a FetchXML condition <filter> on the lookup in question

https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference/control...

[EricRegnier]

@HenningStrand, you made me thought too, a way to avoid code is to duplicate the field (ie country) on the child table from the parent record. You can prepopulate that field when the child record is created or parent is updated with sync workflows and then using the OOB filtering like described above to filter the lookup. It's a few more steps than JS but has no cusotmizations (ie code) involved. Cheers