I am making a leave request list on SharePoint. I want the user to pick which department he/she is in via dropdown list of all the departments (AD groups). Then in the next column, I want only the people in that department the user chose to fill in the dropdown list.
Please see the photos below. I have done this through PowerApps and made a custom list on the SharePoint site to achieve this. I want to replicate this, but want the cascading dropdown to link with our AD groups instead if that is possible.
In the first pic, the department I have picked is Finance; there is only 1 person in the finance group, so naturally his name 'Reza' is the only one that will come up. In the second pic, the user chose department 'IT', and there are 2 people in the IT group so therefore only their names will show up in the dropdown list, and so on. Please note this is just to help visualize what I am after. I then want the leave request item to be sent to whoever's name is in that 'Supervisor' section.
I understand I am not wording this correctly, and I have been researching Sharepoint, PowerApps, AD, Power Automate, Flows, lists and so on for quite a while now and I have yet found a scenario that is similar to what I am after. Safe to say, I am pretty confident this cannot be done or that it is an extremely tricky thing to do. But I would like a second opinion just to confirm.
My company is sadly unhappy with the PowerApps method, along with the automated flows via SharePoint. They REALLY want this cascading dropdown list and have it correspond to their groups in their Active Directory...
