Power Platform Community Forum Thread Details

Cascading dropdown but initial drop down isn't related to core record

I have a table called Recruitment. It is for relating people to a unique combination of a site and a study.

In my Dataverse database, I have the following master tables: a People table, a Region table, a Site table and a Study table.

Regions can have 1:N Sites

Studies can be at multiple sites and sites can have multiple studies (N:N). This is accomplished with a Site-Study mapping table.

Recruitment is the unique pairing of a Person with a Site-Study combination (thus the identifier of the mapped relationship).

When a recruitment record is created, I'd like to allow the end-user to start by selecting a region, then finding a site, then selecting a study. Ultimately, I want this selection to be tied to a unique site-study record.

So my challenge is this: I can put a lookup on my model-driven form but this will just be a lookup to the Site-Study mapping table. What I really want is to be able to have a dropdown for the region, which would filter the dropdown for the site, which would then filter the dropdown for the study. Technically, none of these tables have direct relationships to the Recruitment table.

Is this achievable using OOTB functionality or should I be thinking about building a PCF component for this? If it's the latter, does anyone know of an existing PCF component that would help be accomplish this?

Hi @mhomol ,

Based on your model, which sounds like the one below...

You will not be able to filter as you expect out of the box. My suggestion here is you might want to do one of a couple things to provide a nice interface for your users to filter as you need and identify the right Site / Study to relate to the Recruitment.

Recommended Options

Create an embedded Canvas App that will provide the ability to select the Site or Region from those respective tables and show the related Site Study records that exist. The user can then select one of these and you could populate the appropriate Site Study field on the Recruitment record. https://learn.microsoft.com/en-us/power-apps/maker/model-driven-apps/embedded-canvas-app-add-classic-designer
Custom Page - You could also create a custom page instead that would basically do similar process but you would handle the full user experience on that page in your model app to create the appropriate recruitment records. https://learn.microsoft.com/en-us/power-apps/maker/model-driven-apps/add-page-to-model-app

Quick Links

Subscribe to this forum!