Add an "All" to your Dropdown Lists - Dynamically- not working as planned

(0) Share

Report

Posted on by NitroPepsi

221

I am trying to add an all to my drop down. But it is saying that I did something wrong.

With({lclProviderName: Choices(D_PROVIDER.provider_name)},
ForAll(Sequence(CountRows(lclProviderName)+1), {Value: If(Value=1, "All", Last(FirstN(lclProviderName, Value-1).Value)})
)

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
@NitroPepsi

That's almost the exact title to my video on doing this process! 😁

Your formula is correct. Is this a SharePoint list?

With({lclProviderName: Choices(D_PROVIDER.provider_name)}, ForAll(Sequence(CountRows(lclProviderName)+1), {Value: If(Value=1, "All", Index(lclProviderName, Value-1).Value)} ) )

You can also use this formula:

Ungroup( Table({Items: ["All"]}, {Items: Choices(D_PROVIDER.provider_name)} ), "Items" )

What error are you getting?

Was this reply helpful? Yes No
NitroPepsi 221 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi, no it is Microsoft SQL server for the data source.
The first error it is giving me is choices has invalid arguments and Name isn't valid. {0} isn't recognized.

in the lower portion of this it is saying last has some invalid arguments . Incompatible type. We cant evaluate your formula because of a type error.

For this formula, the choices command is not feeling it. I did try to put in my primary key but it still was not feeling it.
Ungroup( Table({Items: ["All"]}, {Items: Choices(D_PROVIDER.provider_name)} ), "Items" )

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

Like
a
(0)

Report
Copy link

Link copied!

@NitroPepsi

Yes, choices is not a valid function for SQL data as there is no Choice (or complex) type columns in it!

What is it that you are trying to get out of your table? All the provider_name records? A Distinct list of them?

Was this reply helpful? Yes No
NitroPepsi 221 on at

Like
a
(0)

Report
Copy link

Link copied!

See, I was originally using this formula for my dropdown - Distinct('[incentive_test].[D_eTime]', Employee_Display_Name)

To filter people in a gallery

SortByColumns(Search(Filter('[incentive_test].[D_eTime]', DELETE_IND = "N", Employee_Display_Name= Dropdown1.SelectedText.Value),'Search Bar Table of Sub'.Text, "Employee_Display_Name"),"ImportMonth", Descending, "Employee_Display_Name", Ascending)

But the problem is that the first name on the drop down is the name that it shows in the gallery. So instead of showing all the names in the gallery it shows the first name. Similar to your video, how in the end, the default was all and it showed everyone in the gallery. That is what I am trying to do is show everyone or have the all in the gallery and then the individual is able to select their name and it filter their name from the drop down.

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

Like
a
(1)

Report
Copy link

Link copied!
@NitroPepsi

So, first thing...you are using SelectedText in your second formula. That is a deprecated Property and should be avoided. Use the proper Selected.<columnName> instead!

You can add the All to the Distinct list formula.

Ungroup( Table({Items: Table({Result: "All"}) }, {Items: Distinct('[incentive_test].[D_eTime]', Employee_Display_Name)} ), "Items" )

To clear up the SelectedText issue, your formula for that would then be:

SortByColumns( Search( Filter('[incentive_test].[D_eTime]', DELETE_IND = "N", Employee_Display_Name = Dropdown1.Selected.Result ), 'Search Bar Table of Sub'.Text, "Employee_Display_Name" ), "ImportMonth", Descending, "Employee_Display_Name", Ascending )

Was this reply helpful? Yes No
NitroPepsi 221 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your assistance in this! It worked, I just needed to update my formula in the gallery to this

SortByColumns(Search(Filter('[incentive_test].[D_eTime]', DELETE_IND = "N", Employee_Display_Name= Dropdown1.Selected.Result || Dropdown1.Selected.Result="All"),'Search Bar Table of Sub'.Text, "Employee_Display_Name"),"ImportMonth", Descending, "Employee_Display_Name", Ascending)

Just one more question for you the ascending, portion, it does not like it, where can I put this to make it work?
ngroup(
Table({Items: Table({Result: "All"}) },
{Items: Distinct('[incentive_test].[D_eTime]', Employee_Display_Name)}
),
"Items", Ascending
)

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
@NitroPepsi

You would not want to sort the entire table after you add the ALL to it...otherwise it could possibly put your ALL someplace else in the list alphabetically.

Instead, sort the Distinct table...

Ungroup( Table({Items: Table({Result: "All"}) }, {Items: Sort(Distinct('[incentive_test].[D_eTime]', Employee_Display_Name), Result)} ), "Items" )

You don't even need to specify the Ascending in the Sort...it is the default, so if you don't include it, it will be ascending.

Was this reply helpful? Yes No
NitroPepsi 221 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you so much!

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

Like
a
(1)

Report
Copy link

Link copied!

@NitroPepsi

Happy to help! 😁

Was this reply helpful? Yes No