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

Date Sorting as Number Rather Than Date

I have a sort button that sorts by a column called "date_different_format". Because of data type issues between Excel and PowerApps, the data is coming in from Excel as text, which means that the formula below sorts it as text rather than as a date. 

 

I have a general inkling that I need to cram a DateValue() into the formula somewhere, but I cannot find a variation that doesn't end in lots of angry red and white x's replacing all my gallery content.  Can anyone help?  @Shanescows ?

 

SortByColumns(Search([@time_logs_1], EntrySearchBox.Text, "date_different_format", "project_id", "staff_person"), "date_different_format", If(SortDescending1, Ascending, Descending))

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Date Sorting as Number Rather Than Date

Hi @justtegan ,

Do you want to sort your Gallery Items based on the "date_different_format" acted as Date value rather than Text value?

 

Based on the issue that you mentioned, I think the DateValue property could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Set the Items proeprty of the Gallery to following:

SortByColumns(
               Search(
                       AddColumns([@time_logs_1], "DateDifferentFormat_DateValue", DateValue(date_different_format)),
                       EntrySearchBox.Text, 
                       "date_different_format", "project_id", "staff_person"
               ), 
               "DateDifferentFormat_DateValue", 
               If(SortDescending1, Ascending, Descending)
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

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.

View solution in original post

4 REPLIES 4
Dual Super User
Dual Super User

Re: Date Sorting as Number Rather Than Date

Hey @justtegan 

 

I carried out a test on my side and if the column is formatted as date as format "dd/mm/yyyy" in excel, then it sorts the results properly.

If this is a text type field. Can you try to update your expression as:

SortByColumns(Search([@time_logs_1], EntrySearchBox.Text, "date_different_format", "project_id", "staff_person"), Text(date_different_format,"yyyymmdd"), If(SortDescending1, Ascending, Descending))

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

justtegan
Level: Powered On

Re: Date Sorting as Number Rather Than Date

This isn't working for me-- I THINK the reason it isn't working is that I had to wrap date_different_format in a DateValue() function wherever it is displayed in the app to get around a reformatting issue that was happening with some versions of iOS.

Community Support Team
Community Support Team

Re: Date Sorting as Number Rather Than Date

Hi @justtegan ,

Do you want to sort your Gallery Items based on the "date_different_format" acted as Date value rather than Text value?

 

Based on the issue that you mentioned, I think the DateValue property could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Set the Items proeprty of the Gallery to following:

SortByColumns(
               Search(
                       AddColumns([@time_logs_1], "DateDifferentFormat_DateValue", DateValue(date_different_format)),
                       EntrySearchBox.Text, 
                       "date_different_format", "project_id", "staff_person"
               ), 
               "DateDifferentFormat_DateValue", 
               If(SortDescending1, Ascending, Descending)
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

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.

View solution in original post

justtegan
Level: Powered On

Re: Date Sorting as Number Rather Than Date

@v-xida-msft This does work, thank you. I had figured out a work around by adding another data card to the form (which I made invisible), then feeding the datepicker selected date into it as a different format, then using that invisible card value to sort the gallery.

 

Your solution is much more elegant, and I would like to better understand how it works. It looks to me like your formula adds a temp column within the search function named "datedifferentformat_datevalue" and uses DateValue() to reformat the date_different_format column value as a date, then feeds the result into the temp column. Is that correct? 

 

UPDATE: @v-xida-msft Coming back 24 hours later because I realized that, while this did fix the sorting problem, it caused another problem in other areas of the app. Somehow, changing the gallery items formula to yours caused a disconnect between the data source and the entry and edit forms.  All the elements that reference the selected value from the gallery screen are throwing errors. I'm thinking this is because wrapping the data source reference up in the AddColumns() function is somehow interrupting record selection.  Any ideas?

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,466)