Dynamically update choices in a SPO list?

[f2]

Hi,

I wanted to try automate the account creation process with Power Automate while using a Sharepoint List/Form (and maybe a powerapp to tie it all together). I was wondering what would be the best method for things like;

HR would pick a choice of "Location" then the next option would only show "Room Number" choices for that location:

Location1 > Room1_01, Room1_02 etc

Location2 > Room2_01, Room2_02 etc

Same thing for type of "Department" then "Job Title"

Finance > Finance_Officer, Finance_Manager etc

IT > Helpdesk, Technician etc

I've saw people talk about hard coding these choices but I would imagine that would be pretty cumbersome plus I'm sure there will no doubt new things to add and I might end up making a mistake. Another user on here suggested something in a previous post I made about having a "working" sheet. The way I understood it is that I could update the "working sheet" and point the choice cell in my list/form to that, which would make updates and additions so much easier. However I have no idea how to go about that, I've had a search and obviously using the wrong search terms (or I picked the other user up wrong and thats not what he meant).

Any ideas? Hopefully I explained it correctly.

[Hardesh15]

@f2  First of all i did not understand your query because both Title and content are different.

By using PowerApps you can definitely do filtering based on choices and store in dropdowns.

Pls elaborate your query in details.

Please 'Thumbs Up' the posts that helped you and 'Accept as Solution' if my post answered your question.

@Hardesh /Gopenly.in

[f2]

Hi Hardesh,

Sorry about that, I will update the post title to something else. Hopefully this will make it a bit clearer.

So here is the list I'm referencing in my flow:

I want to make certain columns "choices" to save users typing in and prevent any errors. If possible, I would like certain choices to dynamically update from a separate excel sheet so that if I need to add more job titles or if I have missed room numbers it would be easier to simply update an excel sheet rather than a bunch of choices/dropdowns in the SPO list.

Here is an example of the excel sheet:

SPO List: With "Department" being a choice, if I selected say "Finance" then the next column "Job Title" would dynamically update with the "FinanceRoles" and only allow the user to select:

Finance > Finance Assistant/Procurement Officer/Finance Manger

Same with IT and so on.

Similar idea with "Campus" being a choice, it would update and only show the rooms relevant for that campus:

Campus1 > Room1_01, Room1_02 etc

Campus2 > Room2_01, Room2_02 etc

Campus3 > Room2_01, Room2_02 etc

I found this post that talks about hardcoding them in:

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-do-I-change-the-choices-for-one-field-ba...

I'm a total novice when it comes to this so I might be getting myself mixed up. There are so many different rooms, job titles etc I just thought there must be a better way to do it rather than updating choices manually. Then if I need to add or remove a room number I would have to go back through all this code and assumed if I pointed it to a separate working/backend excel sheet it would make things much easier to maintain?