cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
szilvasidave
Frequent Visitor

Filter, GroupBy, and Max functions

Hi,

I have a Sharepoint list where I'm storing service request activities. When a service request is created/updated in the tool, it creates a new record in sharepoint and doesnt edit the old one.

Each SR has an SR number and an UpdateCount.

UpdateCount starts at 0 - when case is initially created - then gets incremented by one at each update to the case.

And also a "Yes"/"No" flag that indicates if this is the final update to the case

 

Now what I want to do:

I want to create a gallery, that shows cases:

  • which are still open (FinalUpdate = "No")
  • and only show the highest UpdateCount per case

I have 30+ columns in sharepoint and in the gallery items I might need access to all 30+ columns

 

I tried to play around with Filter, GroupBy SR Number and I assume I need to do something with Max or ForAll but I cant figure it out.

Could you help?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@szilvasidave 

I can start by saying, you're going to run into some issues with the Yes/No column if it is a Yes/No column in your list.  If it is a text column, then you should be fine.  This has to do with delegation and record limits in your app.  If you have less that 2000 and don't anticipate more, then you can not worry about it.

 

Now, with that said, your Items property on the Gallery can be:

ForAll(    
    GroupBy(
        SortByColumns(
            Filter(yourDataSource, FinalUpdate="No"),
            "SRNumber", Ascending, "UpdateCount", Descending
        ),
        "SRNumber", "_recs"
    ),
    Patch(First(_recs), {SRNumber: SRNumber})
)
    

 

This formula first filters your data by the FinalUpdate being "No" (assumed text column in this case, if not Text, change to !FinalUpdate - this is not delegable).

Then from the results of the Filter, it then sorts by SRNumber ascending and UpdateCount descending (highest at top of result).

Then from the result of that, it Groups the records on the SRNumber.  This will have a table result with two columns, the SRNumber and the _recs (all records matching the SRNumber sorted in Descending UpdateCount order).

Finally, all of this is then iterated through with a ForAll...so For all the grouped records, the ForAll will return a table of all of the first record of the SRNumber (remember this is sorted with the highest UpdateCount at the first of the table) and then we also Patch that record with the original SRNumber (as GroupBy removes it from the resulting group).

 

Final result - a table of all of the non-FinalUpdate records with the SRNumbers with the highest UpdateCount.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

1 REPLY 1
RandyHayes
Super User
Super User

@szilvasidave 

I can start by saying, you're going to run into some issues with the Yes/No column if it is a Yes/No column in your list.  If it is a text column, then you should be fine.  This has to do with delegation and record limits in your app.  If you have less that 2000 and don't anticipate more, then you can not worry about it.

 

Now, with that said, your Items property on the Gallery can be:

ForAll(    
    GroupBy(
        SortByColumns(
            Filter(yourDataSource, FinalUpdate="No"),
            "SRNumber", Ascending, "UpdateCount", Descending
        ),
        "SRNumber", "_recs"
    ),
    Patch(First(_recs), {SRNumber: SRNumber})
)
    

 

This formula first filters your data by the FinalUpdate being "No" (assumed text column in this case, if not Text, change to !FinalUpdate - this is not delegable).

Then from the results of the Filter, it then sorts by SRNumber ascending and UpdateCount descending (highest at top of result).

Then from the result of that, it Groups the records on the SRNumber.  This will have a table result with two columns, the SRNumber and the _recs (all records matching the SRNumber sorted in Descending UpdateCount order).

Finally, all of this is then iterated through with a ForAll...so For all the grouped records, the ForAll will return a table of all of the first record of the SRNumber (remember this is sorted with the highest UpdateCount at the first of the table) and then we also Patch that record with the original SRNumber (as GroupBy removes it from the resulting group).

 

Final result - a table of all of the non-FinalUpdate records with the SRNumbers with the highest UpdateCount.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

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 (2,555)