cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AMH08
Level: Powered On

Sort Blanks Then Descending Rest

I have a Date Column in a SharePoint list. I would like a PowerApps Gallery to sort the gallery by that date. However, I would like it to show blank date values first THEN shift to descending order once all the blanks are displayed. Is there a sort, sortbycolumn function way to achieve this?

 

Blanks only seem to come first in ascending order but if you put it in descending they are the very bottom of the gallery. I currently have something like this for a formula:

 

Sort(Filter(SampleData, EndsWith(last_name,SearchBox.Text)), 'Due Date', Descending)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Sort Blanks Then Descending Rest

Hi @AMH08,

You could add a custom column to the dataset and then sort it on that one. 

 Sort(
AddColumns(
Filter(SampleData, EndsWith(last_name,SearchBox.Text)), 
"FauxDate",
If(
IsBlank('Due Date'),
Today(),
'Due Date'
)
),
FauxDate,
Descending
)

The formula creates a virtual column and assigns today's date to the blanks in the Due Date column and sorts the list descending on the virtual column.   Now the items with blank due dates will appear at the top of the gallery followed by the Due Dates sorted in descending order.

 

6 REPLIES 6
Super User
Super User

Re: Sort Blanks Then Descending Rest

Hi @AMH08,

You could add a custom column to the dataset and then sort it on that one. 

 Sort(
AddColumns(
Filter(SampleData, EndsWith(last_name,SearchBox.Text)), 
"FauxDate",
If(
IsBlank('Due Date'),
Today(),
'Due Date'
)
),
FauxDate,
Descending
)

The formula creates a virtual column and assigns today's date to the blanks in the Due Date column and sorts the list descending on the virtual column.   Now the items with blank due dates will appear at the top of the gallery followed by the Due Dates sorted in descending order.

 

Community Support Team
Community Support Team

Re: Sort Blanks Then Descending Rest

HI @AMH08,

Do you want the Blank date value items to be displayed firstly within your Gallery in Descending order?

If you want the Blank date value items to be displayed firstly within your Gallery in Descending order, I have made a test, I afraid that there is no direct way to achieve your needs in PowerApps currently.

As an alternative solution, please take a try with the following workaround:10.JPG

 

11.JPG

Set the OnVisible property of the first screen of my app to following:

ClearCollect(
    SortedCollection, 
    Filter('20181122_case14',ProductionDate=Blank()),
    SortByColumns(Filter('20181122_case14',ProductionDate<>Blank()),"ProductionDate",Descending)
)

On your side, you should type:

ClearCollect(
    SortedCollection, 
    Filter('YourSPList', DateColumn = Blank()), /*<- DateColumn represents the Date type column in your data source*/
    SortByColumns(Filter('YourSPList', DateColumn <> Blank()),"DateColumn", Descending)
)

Set the Items property of the Gallery to following:

SortedCollection

On your side, you should type:

Filter(SortedCollection, EndsWith(last_name, SearchBox.Text))

 

Above formula may cause a Delegation issue, if you want to get rid of this issue, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(ListCollection, 'YourSPList');
ClearCollect( SortedCollection, Filter(ListCollection, DateColumn = Blank()), /*<- DateColumn represents the Date type column in your data source*/ SortByColumns(Filter(ListCollection, DateColumn <> Blank()),"DateColumn", Descending) )

Set the Items property of the Gallery to following:

Filter(SortedCollection, EndsWith(last_name, SearchBox.Text))

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AMH08
Level: Powered On

Re: Sort Blanks Then Descending Rest

I like this idea and it seems to work. However, I ran into another problem with a patch formula I have inside the gallery:

 

Button - OnSelect: Patch(SampleData,ThisItem,{ 'Due Date' : Today()})

 

That starts returning a "FauxDate Does Not Exist" error. 

AMH08
Level: Powered On

Re: Sort Blanks Then Descending Rest

Does collections store the {attachments} associated with the datasource? I tried this technique but got invalid names on {attachments}. I looked at a sample of the collection and don't see attachments there either. 

 

In theory, should patch formulas still work with this as well? I have patches in this gallery that makes updates to the affiliated item in SharePoint. 

Highlighted
AMH08
Level: Powered On

Re: Sort Blanks Then Descending Rest

Thank you so much for leading me in the right direction. I wanted to document what I came across:

 

I rewrote the patch formula to not reference "ThisItem" but instead do a lookup based on ID back to the list. 

 

Patch(SampleData,First(Filter(SampleData, ID=ThisItem.ID)),{ 'Due Date' : DatePicker1.SelectedDate})

Then I also did a very small modification to the custom column code which applyed Today()+1 vs Today() because anything patched with today's date would appear on top of everything:

 

 Sort(
 AddColumns(
 Filter(SampleData, EndsWith(last_name,SearchBox.Text)), 
 "FauxDate",
 If(
 IsBlank('Due Date'),
 Today()+1,
 'Due Date'
 )
 ),
 FauxDate,
 Descending
)

It appears with these tweaks it works like I would desire. Thank you. 

Super User
Super User

Re: Sort Blanks Then Descending Rest

Hi @AMH08,

Glad to help.  I was about to offer what you came up with on your own. We make a good team. Kudos to you.smiles.gif