PowerApps Form Drop-Down Remove Items

[NoGoodKeith]

Hi Everyone,

I am sure the solution to this must be relatively straightforward, but I cannot for the life of me work out the direction I need to take.

I am designing an app which integrates with a SharePoint Document Library, and MS Teams.

I have created 5 collaboration channels, let's call them 'Collab-1' through to 'Collab-5'.

A user uploads an Excel document to the Document Library (called 'Collaboration Trackers') and selects what channel the collaboration will be held in, from a drop-down called 'Collaboration Channel'.

Each collaboration has a state of 'New','Open','Closed' & 'Completed'.

When a user attempts to upload a new document to the document library, I want them to only be able to select from a list of channels, which aren't already in use.

I am struggling to get a comprehensive list of which channels are currently in use, and then subsequently have those removed from the drop-down for 'Collaboration Channel'.

The form currently has the following code in the Items field for DataCardValue2:

Choices([@'Collaboration Trackers'].'Collaboration Channel')

I have tried applying filters to this, after calling a clear collect to get the list of records where a room is not in an 'Open' state. But I haven't seemed to get the syntax right, so not sure it can be done this way?

Any help here would be greatly appreciated.

[madlad]

Hi!

Firstly, the idea here is good, so more likely than not it's a minor syntax issue which needs to be resolved. 

I'm wondering, what does the collection of items to include look like? Following that, how are you referencing those in the filter for the dropdowns items?

Offhand, I'd say your best bet would be something like these:

ClearCollect(OpenCollabs, Filter(*YourListName*, *CollabStatusColumn* = "Open"));

Filter(
  Choices([@'Collaboration Trackers'].'Collaboration Channel'), 
  Value in OpenCollabs.*CollaborationName*
)

^Replacing everything *surrounded by asterisks* with appropriate values from your data source(s)

Let me know!

[NoGoodKeith]

Thanks @madlad for the great suggestions.

So what you have written above is very close to what I have coded.

The OnVisible for the current screen has the following code:

ClearCollect(RoomsInUse,Filter('Collaboration Trackers', State.Value="Open"))

Filter(Choices[@'Collaboration Trackers'].'Collaboration Channel'), Value in RoomsInUse.CollaborationChannel)

I get a "can't convert text to a record" error with this.

I note from your examples above though, that the ClearCollect statement is referencing a list *YourListName*, where mine is directly referencing the 'Collaboration Tracker' table and filtering those records, so perhaps this is my issue.

Thanks for your response. 🙂

[madlad]

@NoGoodKeith 

*YourListHere* is just a placeholder I threw in there, but from what I can see your code looks pretty good.

I think it might be in your filter statement. is RoomsInUse.CollabortationChannel a choice column too?

If so, try:

Filter(
  Choices([@'Collaboration Trackers'].'Collaboration Channel') As CT, 
  CT.Value in ForAll(RoomsInUse.CollaborationChannel As RIU, RIU.CollaborationChannel.Value)
)

After some playing around, this is what I got to work in a very similar test set up to what you have described, so hopefully it will work for you too 

Let me know!