cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sienna28
Responsive Resident
Responsive Resident

How to sort a collection by 2 values to return highest date at top

I have a collection and am trying to sort it.

How can I sort by the NameID and get the highest date alongside each name?

For example NameID 1 would be highest date 15/02/2022,  Name3 would be 26/11/2022

 

NameID  DateValue

1             01/02/2022

2             17/06/2022

3             26/11/2022

1             12/02/2022

1             15/02/2022

1             04/02/2022

3             11/05/2021

3             02/02/2022

 

Any help appreciated.

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User
Super User

@sienna28 

Playing around with it a little more, for a test, put a data table on the screen and put the formula below as its items property.

AddColumns(
            Sort(
                  GroupBy(
                          SortByColumns(
                                         Col2, "cr583_startdate", Descending
                           ), "cr583_nameid","restofdata"   
                   ), cr583_nameid, Ascending
             ),"startdate",First(restofdata)."cr583_startdate",
               "enddate",First(restofdata)."cr583_enddate"
)

View solution in original post

17 REPLIES 17
365-Assist
Power Participant
Power Participant

Does this help?

SortByColumns(colItems,"Title",Ascending,"Date",Descending)

 

365Assist_0-1653795167478.png

 

 

Drrickryp
Super User
Super User

@sienna28 

This formula for the gallery removes the duplicate Titles in the above screenshot and has only one Title and its most recent date.

//Produces a table sorted on Title field, containing a table of dates sorted descending
//For the items property of the gallery:
Sort(
     GroupBy(
              SortByColumns(
                             colitems,"Date",Descending
               ),"Title","Date"
      ), Title, Ascending
)

// Inside the gallery, add two labels:
ThisItem.Title 
First(ThisItem.Date).Date
 

 

 

Drrickryp
Super User
Super User

@sienna28 

This formula for the gallery removes the duplicate Titles in the above screenshot and has only one Title and its most recent date.

//Produces a table sorted on Title field, containing a table of dates sorted descending
//For the items property of the gallery:
Sort(
     GroupBy(
              SortByColumns(
                             colitems,"Date",Descending
               ),"Title","Date"
      ), Title, Ascending
)

// Inside the gallery, add two labels:
ThisItem.Title 
First(ThisItem.Date).Date
 

 

 

sienna28
Responsive Resident
Responsive Resident

Many thanks for your help.

 

One question....
When I do the following I would like to display more values from the collection it is working on.
This will currently output name and date, but I also have another date in the collection it is using.

 

 

//Produces a table sorted on Title field, containing a table of dates sorted descending
//For the items property of the gallery:
Sort(
     GroupBy(
              SortByColumns(
                             colitems,"Date",Descending
               ),"Title","Date"
      ), Title, Ascending
)

 

So I have...

 

ClearCollect(
col3,
Sort(
GroupBy(
SortByColumns(
Col2,
"cr583_startdate",
Descending
),
"cr583_nameid",
"cr583_startdate"
// "cr583_enddate"
),
cr583_nameid,
Ascending
))

 

There is a column called EndDate in col2 that I need to bring across

sienna28
Responsive Resident
Responsive Resident

Just to explain further - the first gallery has collection 1 which has the sorted recs.
The 2nd gallery has the sorted and grouped values - I would like to bring the EndDate into it also for the grouped item.


sienna28_0-1653824507755.png

 

Drrickryp
Super User
Super User

@sienna28 

ClearCollect(
             col3, Sort(
                        GroupBy(
                                SortByColumns(
                                               Col2,   "cr583_startdate",  Descending
                                 ), "cr583_nameid", "cr583_startdate", "cr583_enddate"
                         ), cr583_nameid, Ascending
                    )
)

and in the gallery put three labels:

ThisItem.nameid
First(ThisItem.StartDate).StartDate
First(ThisItem.StartDate).EndDate

 

sienna28
Responsive Resident
Responsive Resident

Thanks very much

Nameid comes through in the gallery, but this does not appear

First(ThisItem.StartDate).StartDate

I can only use First(ThisItem.cr583_startdate).cr583_startdate and it says invalid use of "."

sienna28
Responsive Resident
Responsive Resident

Also in the code, when I add the extra 

cr583_enddate

I notice the grouping is lost and it shows all records

Drrickryp
Super User
Super User

@sienna28 

Try this

ClearCollect(
             col3, Sort(
                        GroupBy(
                                SortByColumns(
                                               Col2,   "cr583_startdate",  Descending
                                 ), "cr583_nameid", "restofdata"
                         ), cr583_nameid, Ascending
                    )
)

and in the labels

First(ThisItem.restofdata).cr583_startdate

First(ThisItem.restofdata).cr583_enddate

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,747)