Answered

Date Sorting as Number Rather Than Date

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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))

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

yashag2255 24,769 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hey @Anonymous

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!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

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,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@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?

Was this reply helpful? Yes No