Adding blanks in Dropdown list & gallery filtering

(1) Share

Report

Posted on by F01536

Hi All,
Despite hours of searching, i couldn't find any solution to what seems a simple problem. How can my dropdown list -used for gallery filtering- include a blank choice selection ?

I have a dropbox linked to my Subject column on my SP :

Choices('EDITABLE DATA GRID'.Subject)

And my gallery sorting is as below :
 
Sort(Filter(
       'EDITABLE DATA GRID',
        varReset && (IsBlank(DropdownCanvas3C.Selected.Value) || Subject.Value = DropdownCanvas3C.Selected.Value)),
    'BM Sub-MasterID', SortOrder.Descending)

This all works fine as long as you keep using the SP choice list you defined. I have my gallery filtered by my dropdown choice.
But once in your gallery when you add a "Suject" from the list, there is no way to remove it (put it black to blank).

So i would like to be able to add a blank in my Subject choices.

The most logical would be to add a Blank in the choice SP list but doesnt seems possible?

Should this be handled in PowerApp ? 

On Dropdown Item property, I tried : "Table({Subject: Blank()}, Distinct('EDITABLE DATA GRID', Subject))" ==> but that only returns blanks

I also tried "Table({Subject:Blank()},Choices('EDITABLE DATA GRID'.Subject))" but while it shows a blank line in the dropbox, it doesnt seems to interact with real blank value that are in the SP column (filtering doesnt work)
 
Thanks alot :)
Gregory

Categories:

General Questions

Using Apps Using Formulas

All responses (6)

Answers (0)

Pstork1 65,908 Most Valuable Professional on at

Like (1)

Report

Adding *blanks* in Dropdown list & gallery filtering

@F01536 If you add a value to the dropdown that isn't one of the values in the Choice column then you need to update the Update field to save the right value and not save anything if its Blank.

Distinct() is used for columns that have duplicate values. That won't be the case with a Choice column.
F01536 14 on at

Like (1)

Report

Adding *blanks* in Dropdown list & gallery filtering

@Pstork1 : Thanks. The column is a choice column, but i just see that the distinct() will only show the choices that are already in my dataset but not extra choices not yet used in the data, so it's not going to work :(
I tried this and seems to work : "Table({Value: Blank()}, Choices('EDITABLE DATA GRID'.Subject))"

But then it doesnt want to save, when i put the blank & save it puts back the data that was before. Seems like blank is not
one of the choices of the SP List choice definition and doesnt like it ?

@Ramole : Thanks also :) But the "or" after VarReset makes no filter from my dropdown box work anymore :(

"varReset && ((IsBlank(DropdownCanvas3C.Selected.Value) || Subject.Value = DropdownCanvas3C.Selected.Value))),"

=> this one filter works but when i select blank cell it shows me all data instead of only blanks.

"varReset && (Subject.Value = DropdownCanvas3C.Selected.Value)),"

=> this one filters all correctly (blanks selected = blanks visible), but then i have no way to have a total view anymore, it's always a view of one of the selected :(

Thanks

Gregory
Suggested answer

Ramole Super User 2024 Season 1 on at

Like (1)

Report

Adding *blanks* in Dropdown list & gallery filtering

Hi,
Try this

Sort(Filter(

'EDITABLE DATA GRID',

varReset Or (IsBlank(DropdownCanvas3C.Selected.Value ) Or Subject.Value = DropdownCanvas3C.Selected.Value)),

'BM Sub-MasterID', SortOrder.Descending)

The problem comes from this varReset
Pstork1 65,908 Most Valuable Professional on at

Like (1)

Report

Adding *blanks* in Dropdown list & gallery filtering

I actually tested my code and it works too. It depends on what kind of field you are pulling into the Editable Data Grid collection that you are using as the basis.
F01536 14 on at

Like (1)

Report

Adding *blanks* in Dropdown list & gallery filtering

Thanks alot @Pstork1 !

There was a little typo in your formula should be Subject.Value (if someone else once need this). :)

Now this works but i'm getting an issue with my Gallery update :

Sort(Filter(

'EDITABLE DATA GRID',

varReset && (IsBlank(DropdownCanvas3C.Selected.Value) || Subject.Value = DropdownCanvas3C.Selected.Value)),

'BM Sub-MasterID', SortOrder.Descending)

With my current gallery code, my dropdown filter works with all items except my new blank line. This is because of the orange highlighted line.
When i select blank from my dropdown, it will show all data from my list.

I tried removing this orange part, and then my filters works, and when i select blank in dropdown it shows me the blank.
But at this point how can i do to see my full data set again ? I'm stuck with always one of the choice.

I have a reset button containing

"Reset(DropdownCanvas3C)"that was removing the choice and showing all data... But now with this reset my dropdown is blank and shows only blank... I have no way to show all data anymore.

Have a nice weekend
Gregory
Pstork1 65,908 Most Valuable Professional on at

Like (1)

Report
Adding *blanks* in Dropdown list & gallery filtering

Try the following formula instead. You need the .Value after the Distinct to specify that you want a single column Table with just the Subject field. Then add the Blank() value as the Value field also.

Table({Value: Blank()}, Distinct('EDITABLE DATA GRID', Subject).Value)