cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Remove Drop-Down Items That Have Been Used Before

I have a drop-down control that is bound to a Choice column from a Sharepoint List.  I need the items in this Choice column to essentially be "one time use".  So once someone uses the app to submit the form, whichever item they chose from the drop-down should not show up as an option the next time they fill out the form. 

 

For example, they will periodically inspect a gauge, and use the app to select the time period from the drop-down and enter the value from the gauge.  Once they've submitted the value for the chosen time slot, when they go into the app the next time, that time slot should not present itself as an option in the drop-down.

 

I know I could prevent them from double-filling a time slot by just setting the column in the Sharepoint List to require unique values.  But the requirement from the user is that they not even have the option of choosing an already used time slot.

 

I thought I might be able to accomplish this by adding the chosen item to a collection when the form is submitted and then Filtering the drop-down choices based on that collection, but if that's the way to do this, I can't figure out the syntax.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Remove Drop-Down Items That Have Been Used Before

Hello @AngryBatVoice,

 

One of the reasons that I hate Choice (and Lookup type)  columns in Sharepoint is that they are incredibly difficult to work with from within Powerapps.  IMHO, they were designed for Sharepoint and not Powerapps. Some of the other advisors have workarounds but I simply can't be bothered.  It has been my practice to change them within Sharepoint to either Single line of text or Number type.  That being said, if you do that, you will be able to eliminate the item by filtering the dropdown the next time you edit the data. BTW,  I haven't lost data by simply making the change on the Sharepoint list but you may want to backup your list before doing it.    

Re: Remove Drop-Down Items That Have Been Used Before

Totally steered me in the right direction.  Here's my solution:

 

I created a new Sharepoint List that contains a Single Line of Text column.  Each entry in this list will be a time slot.  This list will not change.  Then on my main list for the time reporting, I made the Time field a Single Line of Text, as you suggested.  

 

In PowerApps, for OnVisible on the form screen, I load all the timeslots from the Time Slot list into a collection, removing any entries that appear in the Reporting List. EDIT:  So I found that the original command appeared to work, but that was because it was actually removing the time slot from the reference Sharepoint List.  So I am doing 2 collections now, first I collect all the entries from the reference list, then I collect the non-matching slots from that collection.  I also found I need to refresh my data sources when I return to the Home Screen.  We're dealing with small amount of data in these lists, so it shouldn't impact performance, but on bigger lists, a more elegant solution might need worked out.

 

So it looks like: 

ClearCollect(collTimeSlots,RemoveIf('Time Slots',Title in 'Reporting'.Time%20Slot))


Home Screen OnVisible and OnStart: ClearCollect(collTimeSlotRef, 'Time Slots'.Title)

Form Screen OnVisible: ClearCollect(collTimeSlots,RemoveIf(collTimeSlotRef, Title in 'Reporting'.Time%20Slot))

Then I use a Flow on the Submit button to feed the input data to the reporting list.

 

Need to do some additional testing, but so far this appears to be doing the trick!  Thanks again!

3 REPLIES 3
Super User
Super User

Re: Remove Drop-Down Items That Have Been Used Before

Hello @AngryBatVoice,

 

One of the reasons that I hate Choice (and Lookup type)  columns in Sharepoint is that they are incredibly difficult to work with from within Powerapps.  IMHO, they were designed for Sharepoint and not Powerapps. Some of the other advisors have workarounds but I simply can't be bothered.  It has been my practice to change them within Sharepoint to either Single line of text or Number type.  That being said, if you do that, you will be able to eliminate the item by filtering the dropdown the next time you edit the data. BTW,  I haven't lost data by simply making the change on the Sharepoint list but you may want to backup your list before doing it.    

Re: Remove Drop-Down Items That Have Been Used Before

Totally steered me in the right direction.  Here's my solution:

 

I created a new Sharepoint List that contains a Single Line of Text column.  Each entry in this list will be a time slot.  This list will not change.  Then on my main list for the time reporting, I made the Time field a Single Line of Text, as you suggested.  

 

In PowerApps, for OnVisible on the form screen, I load all the timeslots from the Time Slot list into a collection, removing any entries that appear in the Reporting List. EDIT:  So I found that the original command appeared to work, but that was because it was actually removing the time slot from the reference Sharepoint List.  So I am doing 2 collections now, first I collect all the entries from the reference list, then I collect the non-matching slots from that collection.  I also found I need to refresh my data sources when I return to the Home Screen.  We're dealing with small amount of data in these lists, so it shouldn't impact performance, but on bigger lists, a more elegant solution might need worked out.

 

So it looks like: 

ClearCollect(collTimeSlots,RemoveIf('Time Slots',Title in 'Reporting'.Time%20Slot))


Home Screen OnVisible and OnStart: ClearCollect(collTimeSlotRef, 'Time Slots'.Title)

Form Screen OnVisible: ClearCollect(collTimeSlots,RemoveIf(collTimeSlotRef, Title in 'Reporting'.Time%20Slot))

Then I use a Flow on the Submit button to feed the input data to the reporting list.

 

Need to do some additional testing, but so far this appears to be doing the trick!  Thanks again!

Super User
Super User

Re: Remove Drop-Down Items That Have Been Used Before

Thanks @AngryBatVoice, glad to be of assistance.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 249 members 5,361 guests
Please welcome our newest community members: