Answered

Removing blank record from dynamically populated dropdown list

(0) Share

Report

Posted on by AKB2021

Hello All,

I am trying to dynamically add "All" value to a dropdown list. The values for this dropdown list is coming from SharePoint List - Text column, which contains the user's name. The formula below works fine but I get a Blank record at end of the list which I am unable to remove. Can someone help me filter out the blank record from the final dropdown values?

With({choiceUserList: SortByColumns(Distinct(TotalList,AssignedToUser),"Result")},

ForAll(Sequence(CountRows(choiceUserList)+1),{Value: If(Value=1,"All",Last(FirstN(choiceUserList,Value-1)).Result)}))

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

GarethPrisk 2,828 on at

Like (0)

Report
Instead of coding it so it is technically always getting the list of records, could you switch to a variable/collection model. In most scenarios, users won't need dynamic updates in a given app session.
You could do something like:
Clear(colAssignedToUsers); Collect(colAssignedToUsers,{Result: "ALL"}); // This single record needs to match the definition below Collect(colAssignedToUsers,SortByColumns(Distinct(TotalList,AssignedToUser),"Result"));

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@AKB2021

Take a look at my video on adding All to any list. I feel you might have already as this is pretty much the same formula that I designed in that video. SO...your formula is good! You just need to filter out the blanks.

Specifically for your formula:

With({choiceUserList: SortByColumns( Filter( Distinct(TotalList, AssignedToUser), !IsBlank(Result) ), "Result" )}, ForAll(Sequence(CountRows(choiceUserList)+1, 0), {Value: If(Value=0,"All", Last(FirstN(choiceUserList,Value)).Result)} ) )

I hope this is helpful for you.

Was this reply helpful? Yes No
AKB2021 57 on at

Like (1)

Report

I already have a lot of collections which is kind of slowing down the app, hence thought of using dynamic approach here as users are assigning request to different user and then I had to refresh the entire collection each time they are making the change.

Was this reply helpful? Yes No
AKB2021 57 on at

Like (0)

Report

I was filtering by encapsulating the complete with statement, which made the code crazy when it was just filtering within the With before ForAll. Thanks for your help!

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@AKB2021

The formula I provided is great for dynamic needs. A lot of time your app will be updating sources and you want this reflected in your app immediately.

If you don't need the dynamic nature for your app, then you can skip the collection (always best to skip a collection unless you need the ability to create, edit or delete records in your app) and change your formula to the following and put it in the OnStart (or some other "initializing" action):

Set(glbUserList, With({choiceUserList: SortByColumns( Filter( Distinct(TotalList, AssignedToUser), !IsBlank(Result) ), "Result" )}, ForAll(Sequence(CountRows(choiceUserList)+1, 0), {Value: If(Value=0,"All", Last(FirstN(choiceUserList,Value)).Result)} ) ) )

This will give you a variable that can then be used for your control Items property. It will be the table and static.

Bad news on collections is that you can't search for them like you can a variable. Also, a collection has more overhead than a straight variable. Variables can be anything you want, so in the above, it becomes a table that can be used anywhere you need that table.

Was this reply helpful? Yes No