How to filter a Drop Down against 2 distinct tables

[Cscampicchio]

Hi to all,

I’m looking for help to solve a filtering problem with a Custom Page (or embedded Canvas App) in a Model Driven Power App.

I need to filter a Dropdown to find a Provider who can pick up a box in a “Site” and deliver it in a “City”. To do this I created the data model shown in Fig.1 of the enclosed pdf.

My user selects an “Ex Country”, very easily an “Ex Site” can be selected, then he/she select a “To Country” and very easily a “To City” can be selected. I created two different tables: “Provider per Site” and Provider per City” (the “middle tables” for the two N:N relationships between “Providers and Sites” and “Providers and Cities”) in these two tables I have all the record about the Sites and Cities a provider can serve.

It is also very easy to filter two different Drop Downs where it is possible to choose first a provider who can pick up the box and then the provider who can deliver it, but I want to avoid two different selections.

The problem I face is that I tried many ways, but the only one I could make work is the one of Fig. 2 in which I hardcoded the join between the two “middle tables”: with this solution I Will have about a million records in the “Provider per Site and City” table, I think there must be a much better way to do it!

I tried creating a Gallery and filter it against two collections in which I put the “Provider per Site” and “provider per City” tables, but I was unable to make it work. (I wanted to get in the Gallery the list of providers who can do the delivery).

Anyone who could give me some hint? I’m good with Model Driven Power Apps and I have a reasonable experience with Canvas and Power FX as well, but no C# knowledge, so I would like to solve my problem in “low code mode”. I thank everybody very much!

Carlo Scampicchio

Carlo.scampicchio@club-crm.it