Dropdown with choice column and "All" value

[Ektor]

Hi.

I am trying to create a dropdown menu to filter some datatable results with a choice field. Also i want to have an "All" value to my dropdown list so the table return with all records.

My table name is Projects and the choice field name is 'Project Status' and its a datevere table.

I followed the instructions of Shane Young video https://www.youtube.com/watch?v=kLGglidmPxg&t=835s&ab_channel=ShaneYoung.

On Screen OnVisible property i put 

ClearCollect(Droplist, {Result:"All"}); Collect(Droplist, Distinct(Projects.'Project Status') formula and also this 

ClearCollect(Droplist, {Result:"All"}); Collect(Distinct(Projects.'Project Status') but i take a wrong message "Invalid number of arguments"

At dropdown Item property i put Droplist

I also tried this formula

ClearCollect(Droplist, {Result:"All"}); Collect(Droplist, (Choices(Projects.'Project Status'))) and i take no wrong message, but when i click on dropdown menu there is the All Result and 2 placeholders which work but thy are blank.

When i go to File Collections i see that

So the "All" value come out as Result and the Choice values of the column as Values.

My question is how can i return them all as Results, or as Values;

Thank you very much

[rubin_boer]

hey there @Ektor 

have a look at this ClearCollect(Droplist, {Result:"All"}); Collect(Distinct(Projects.'Project Status') but i take a wrong message "Invalid number of arguments" - this is because of no Collection Name. Collect(Droplist, Distinct(Projects.'Project Status')

On the first collect you state the column name as Result and the second collect its Project Status. you will only see the Result column in your dropdown which is All.

Try 

ClearCollect(Droplist, {'Project Status':"All"}); Collect(Droplist, Distinct(Projects.'Project Status')

[Ektor]

Thank you very much for your reply.

I used this formula

ClearCollect(Droplist, {'Project Status':"All"}); Collect(Droplist, Distinct(Projects, 'Project Status')) and i take no wrong message.

But ay the dropdown values now i take this

Now my table values are to the result column and tje "All" value is at the the 'Project Status' column.

Am i missing something?

[rubin_boer]

sorry my error, ClearCollect(Droplist, {Result:"All"}); Collect(Droplist, Distinct(Projects.'Project Status')

[Ektor]

Thank you again.

So i used 

ClearCollect(Droplist, {Result:"All"}); Collect(Droplist, Distinct(Projects.'Project Status'))

and i get again an error at the second Distinct  "invalid number of arguments received 1, expected 2"

Can you please explain me the error and the solution?

[rubin_boer]

Distinct(Projects,'Project Status') change the . to ,

[Ektor]

ClearCollect(Droplist, {Result:"All"}); Collect(Droplist, Distinct(Projects,'Project Status'))

Now another error "the function collect has some invalid argumens" and also an error ait the first part "the function clearcollect has some invalid argumens" 

[RandyHayes · Accepted solution]

@Ektor 

Another consideration is to ditch the collection all together and put this on your Items property of your dropdown:

With({_items: Distinct(Projects,'Project Status')},
    ForAll(Sequence(CountRows(_items)+1, 0), {Result: If(Value=0, "All", Last(FirstN(_items, Value)).Result})
)

This will add an "All" to your list. 

If your interested, you can also view my video on this topic including how to incorporate it into your Filter.

I hope this is helpful for you.

[Ektor]

Hi Randy.

Thank you very much.

I solved it with this formula from your great video

With({test: Choices(Projects.'Project Status')},
ForAll(Sequence(CountRows(test)+1, 0), {Value: If(Value=0, "All", Last(FirstN(test, Value)).Value)})
)

Keep the good job.

[Copernico23]

Great! Worked like a charmed. 

Thanks,