Auto-updating Dropdown Choices Based on List Data

[wwmn]

Please bear with me, I'm trying to explain this as clearly as I can.

TLDR; Is there a way to update dropdown choices through powerapps without needing to manually do-so from list settings? Or is there a way to make those dropdown choices the entries entered in a column, so that when column data changes the dropdown choices change with them?

I am building an app that we use to manage our employee positions. This app shows all positions for each Division and Subdivision, and each supervisor has the ability to edit/update the positions they are in charge of. When updating a position you can select who the supervisor is from a drop down. Right now that drop down is updated manually via sharepoint list settings, but I'd like to find a way for it to update those choices as we update positions. For example, if we hire someone for a supervisor position, when we enter their name into that position in the app it will also update the dropdown with their name, and remove the name of the previous supervisor, whether that be by overwriting the previous entry or deleting it all together.

Right now I'm using two lists, one list has all the positions (called Position Info), the other list is just the name and email of the supervisors (called Supervisors). I use the Supervisors list to enable specific features of the apps that only supervisors have. When you update a supervisor position in the app, it also updates that data in the Supervisors list. If I can somehow use that Supervisors list as my source for dropdown choices that would be awesome.

I often times don't know when we have turn-over to go in and manually edit the drop down choices, so I'm trying to make this app as "self-updating" as I can. 

[Keith_HP · Accepted solution]

Yes, you can do this.  Instead of creating a "Choices" column in sharepoint, create a "Lookup" column in sharepoint.

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/sharepoint/scenarios-choice-to-lookup

If changes are made to the source while you're in the app, you might not see them until you reload the app or refresh the data source: https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-refresh

[wwmn]

Aye! That did it! I was way overthinking this one. Thanks!

[tim94]

Hi @Keith_HP ,

but for that I already need a complete list of options for a column set up in a sharepoint list right? 
I am looking for a filter option like there is in Excel, so that your dropdown basically updates with all the CURRENT options for that specific column from the data source. And if another option is added to the Sharepoint list, then the dropdown is updated accordingly. Is there a way to do this?

Thanks!

BR,

Tim

[wwmn]

The lookup column is the way to do this. Since I posted this question I've used a lookup column quite a bit. Anytime the values in the column change, it changes my dropdown options. The easiest way I can explain it is the lookup column takes all of the values in that column and creates the options from those values. Any time those values change, the lookup column also makes the same changes.

Create a Lookup column in your list, and in the column settings point it to the column you want it to watch over.

In PowerApps, create a dropdown, and in the Items control point to the lookup column.

Choices([@'Your List Name'].LookupColumnName)

[tim94]

Hi @wwmn,

thanks for your reply.

Maybe I am not thinking straight but this is the order of reference, right?:

I have my dropdown in my PowerApps that references to the lookup column in my list (let's call this "primary list") whose possible entries need to be pre-defined in another column from another list (let's call this "secondary list')? So this column at the end of the chain from the secondary list needs to have the selection options already in there, kind of pre-set. I cannot just enter random entries in that reference column from the primary list that then appear in my PowerApps dropdown. Am I missing something here?

Thanks!

BR,

Tim  

[wwmn]

If I understand what you're saying, you are partially there, but I think you're getting hung up on thinking a dropdown needs "choices" to choose from, because by default that's how they work, but with a lookup column creating choices is not needed. The lookup column is not grabbing anything pre-set choices, it's grabbing whatever items are entered into the list.

Lets use this as an example;

You have a list that breaks down the names of employees and what department they belong to. Your columns are the departments, and one of them is named "Technicians". You have 10 employee names in that column, and you want to create a dropdown where you can select any of those Technician names, but the names in this column change often.

In your list settings, create a Lookup column. In the column settings set "Get information from:" to your list, and "In this column:" to the Technicians column.

When viewing your list your lookup column will be blank if you have it visible. All it's doing is working in the background to collect each item from the "Technicians" column. Any time an item, or line, changes in the Technicians column, it changes in the lookup collection. Someone changes a technicians name, it's immediately reflected in the lookup column collection. There is no need to pre-set choices.

I hope this answers your question. Once you see it all in action it will make sense.