cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Problems Sorting a text column from a Sharepoint list

Hello everyone,

 

I am getting crazy trying to figure it out this, and I hope you can help me on that.  I have a poweapp that gets data from a Sharepoint list, in this SP I have a column that convert date to a specific text format (ie.: MMM YYYY) this SP List Column convert dates using the formula =text(date, "MMM YYYY"). When I use this field as a filter on my app, I cannot get this info properly sorted.

 

Nov 2019

Dec 2019

Sep 2019

May 2020

Jan 2020

Feb 2020

Mar 2020

Apr 2020

 

I am using the following syntax:

 

Sort(Distinct('CDatabase', MonthYear), Descending)

 

I used Distinct once I have several lines per month and I don´t want them duplicated on the menu.

 

If I use Sort(Distinct('CTUX Executive Dashboard', MonthYear),Result)

 

then i got in alphabetical order,

Apr 2020,

Dec 2019

Feb 2019

Jan 2020

Mar 2020

May 2020

Nov 2019

 

I need to sort the info by date order ie: Nov 2019, Dec 2019, Jan 2020, Feb 2020, etc

 

any idea on what I am missing here?

 

thank you everyone in advance

Fabio

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Problems Sorting a text column from a Sharepoint list

Hi @fyoshida :

Do you want to sort by the actual date represented by the string?

I think the key is to convert the string into date type.

I've made a test for your reference:

My data source(CDatabase):

 

Collect(CDatabase,{MonthYear:"Apr 2020"}
,{MonthYear:"Dec 2019"}
,{MonthYear:"Feb 2019"}
,{MonthYear:"Jan 2020"}
,{MonthYear:"Mar 2020"}
,{MonthYear:"May 2020"}
,{MonthYear:"Nov 2019"})

 

Add a Gallery and set it's Items property to :

Sort(Distinct(CDatabase, MonthYear), DateValue(Result),Ascending)

1.jpg

The date text has been arranged in the correct order.

Best Regards,

Bof

View solution in original post

18 REPLIES 18
Highlighted
Super User III
Super User III

Re: Problems Sorting a text column from a Sharepoint list

Try:

Sort(Distinct(CDatabase, MonthYear), ColumnForSort)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Highlighted
Regular Visitor

Re: Problems Sorting a text column from a Sharepoint list

I tried  

Sort(Distinct(CDatabase, MonthYear), MonthYear)

 

but nothing was showed,  i got  the message "We didn´t find any data"

Highlighted
Super User III
Super User III

Re: Problems Sorting a text column from a Sharepoint list

Create a collection Onvisible or OnStart of the App:

 

 ClearCollect (MyDashboard, Addcolumns('CTUX Executive Dashboard',"MonthSort", Month (DateColumn)))

 

Then use this on your Gallery
Sort(Distinct(MyDashboard, MonthSort),Result)

 

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Highlighted
Regular Visitor

Re: Problems Sorting a text column from a Sharepoint list

Hi my friend,

 

I tried to create the Collection as "OnVisible" which was the available option for me however:

If I use this syntax::

ClearCollect (MyDashboard, AddColumns('CTUX Executive Dashboard',"MonthSort", Month(MonthYear))) 

 

I got an error (in red) where i put the expression in bold.

 

I am sorry if the doubts it too basic, but it is my first app and with no previous knowledge on this.

 

thank you for your help

Fabio

Highlighted
Super User III
Super User III

Re: Problems Sorting a text column from a Sharepoint list

This is just a wild guess but when you added the dates into SharePoint, if you may have added them in sequence, if so, then the hidden ID column should reflect the order.  You can try to sort on it.  

 

Sort('CTUX Executive Dashboard',ID,Ascending)

 

and see what you get.  

Highlighted
Regular Visitor

Re: Problems Sorting a text column from a Sharepoint list

Hi There,

 

actually when the users populate the SP List they must include the date manually (ie.:05/23/2020) so they are not in sequence. I tried your suggestion but I also get the "No data " message.

Highlighted
Super User III
Super User III

Re: Problems Sorting a text column from a Sharepoint list

Ok, it was just a guess anyway.

Highlighted
Super User III
Super User III

Re: Problems Sorting a text column from a Sharepoint list

Use the date column in your Sharepointlist not MonthYear

 

You will still be able to see the MonthYear in the Gallery.

Use the Date collumn in list.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

 

 

Highlighted
Regular Visitor

Re: Problems Sorting a text column from a Sharepoint list

I used the original SP Date field, and I was able to add both sentences with no error.

 

However on the field where the menu should appear I have no info. No data is displayed.01.png

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,204)