Filtering a form's dropdown by a related table's column value (form in a model-driven app)

[JDDigiSol]

I have a form for a table in which one of the columns is presented with a dropdown.  The values allowed in that dropdown should be filtered to a value based on a column in a referenced table.

I have a Stores table that has a many-to-many relationship with a Treads table.

I have a Pickups table that has a Store column (many-to-one).

I have a PickupItems table that has a parent Pickup column (many-to-one), and a Tread column (one-to-many).

On the form for PickupItem, the dropdown for the Tread column should be restricted to those Treads that are available at the Store referenced by the PickupItem's Pickup.

To rephrase, each PickupItem has a parent Pickup at a given Store, which has a list of Treads available there, and the user should only be able to select from that Store's Treads.

On the PickupItem, how do I filter the list of Treads down to those that are connected to the Store for the parent Pickup?  I have a good idea how to do this in a canvas app, but is it possible in a model-driven app?

[kelseytran]

Hi @JDDigiSol ,

To do this you'd want to get the store ID from the parent Pickup, then filter the Treads based on the store ID.

Lets say for example your form control is 'PickupItemForm' and the dropdown control for Tread is 'Dropdown_Tread'

First, get store ID from the 'Pickups' table for the current 'PickupItem':

LookUp(Pickups, PickupID = ThisItem.Pickup, Store)

Then filter the Treads based on the ID:

Filter(Treads, StoreID = LookUp(Pickups, PickupID = ThisItem.Pickup, Store))

And set the 'Items' property of the 'Dropdown_Tread' control to this:

Filter(Treads, StoreID = LookUp(Pickups, PickupID = PickupItemForm.LastSubmit.Pickup, Store))

To handle default values, you can change the 'DefaultSelectedItems' property of the dropdown so it displays the current selected Tread if the form's in edit mode:

LookUp(Treads, TreadID = ThisItem.Tread)

Then, your Items property of the 'Dropdown_Tread' would look like this:

Filter(Treads, StoreID = LookUp(Pickups, PickupID = ThisItem.Pickup, Store))

------------------------------------------------------------------------------------------------------------------------------
If I answered your question, please accept my post as a solution and if you liked my response, please give it a thumbs up.

Thanks!

[JDDigiSol]

Thank you.  I have a good idea how to accomplish this in a canvas app.  What about in my model-driven app?