Finding the newest versions of a criteria in a data set

[WarrenBelz]

Have you ever wanted to find the newest/latest of all the different categories in a data set? For instance the latest sale by each sales person, the last registration by a person - and generate a table of records.

Firstly, a disclaimer here as the function used is not Delegable, however if all records you are looking for are in the newest 2,000 records, then read on. I am also assuming SharePoint as a data source in the example below.

Firstly the code required

With(
   {
      wGroup: 
      AddColumns(
         GroupBy(
            Sort(
               YourSPList,
               ID,
               Descending
            ),
            "YourIdentifyField",
            "OtherData"
         ),
         "IDMatch",
         First(OtherData).ID
      )
   },
   ForAll(
      wGroup,
      Collect(
         YourCollection,
         LookUp(
            YourSPList,
            ID = IDMatch
         )
      )
   )
)

To explain what is going on here: -

• The list is firstly sorted by ID descending, presenting the newest 2,000 (if you have your limit set to this) records,
• It is then grouped by the selection criteria (person name field in example above) with the table of all other matching fields in the column OtherData. You can also group by multiple fields.
• The field IDMatch is then added representing the first ID in each OtherData set (which is the newest one).
• The output of the With statement above is a table containing two fields, the selection criteria field and more importantly IDMatch, the ID of the newest record in each matching set.
• A collection is then made by looping through this table and gathering all the records from the original table with the ID in wGroup.