Most Recent Record per ID

[Jseo]

I have a SQL server database with data in the format below:

Date	UserID	Status
5/9/2019	1	active
8/5/2020	1	terminated
7/8/2017	2	active
5/6/2019	2	vacation
9/5/2021	2	terminated
10/5/2018	3	active
8/15/2019	3	vacation
4/8/2020	3	terminated
9/5/2021	3	active

I would like to import into my app only the most recent records.

Date	ID	Status
8/5/2020	1	terminated
9/5/2021	2	terminated
9/5/2021	3	active

I was able to do this by creating a View in SQL, but Power Apps does not allow you to edit the data or add another line to the original database. Can someone please help?

[Nogueira1306]

ForAll( Distinct(DataSource, ID), 

Collect( CollectionLatesteEntries, LookUp(Sort(DataSource, Date, Descending), ID = ID)

)

Something like that.

In the condition of the lookup you need to use the ID that you receive from the ForAll and the ID from the datasource 

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[Jseo]

@Nogueira1306  For some reason it still isn't working.  Below is a picture of the structure of the data in my SQL database.

[Nogueira1306]

ForAll(

Distinct(DataSource, ID), 

Collect( CollectionLatesteEntries, LookUp(Sort(DataSource, Date, Descending), ID = ID))

)

Try like that. It was missing ")" in the end

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[Jseo]

@Nogueira1306 I keep getting these 2 errors. I am not sure if I should be using Quotes around the column names.

Date_ENG is my Date column

Ticker is my ID column 

Below is the filter I am trying to use.

ForAll(

Distinct('[dbo].[Ideas]', "Ticker"),

Collect( CollectionLatesteEntries, LookUp(Sort('[dbo].[Ideas]', "Date_ENG", Descending), "Ticker" = "Ticker"))

)

[RandyHayes · Accepted solution]

@Jseo 

The ForAll you are trying to implement is Backward and used like a ForLoop in programming.  This will lead to performance issues in your app.  ForAll is a function that returns a table of records based on your iteration table and record schema.

However, a ForAll is not needed in this scenario.  You can utilize the GroupBy function more effectively.

ForAll(
    GroupBy('[dbo].[Ideas]',
        "Ticker", "_records"
    ),
    With({_top: First(Sort(_records, Date_ENG, Descending))},
      {Ticker: Ticker,
       Date_ENG: _top.Date_ENG,
       Status: _top.Status
      }
    )
)

This will return a table of all of the grouped Ticker records and the top record details.

The record will have a Ticker, Date_ENG and Status column as defined in the ForAll properly.

I hope this is helpful for you.

[Jseo]

Thanks a lot Randy! Worked great...appreciate your help.

@Nogueira1306 also appreciate your effort even though wasn't the right solution

[Jseo]

@RandyHayes

The solution worked for a small test data set, but no longer works as I added data. Is there a way to make this solution delegable so that more than 2000 records can be seen? 

Below is the solution you provided:

Search(SortByColumns(Filter(ForAll(
GroupBy(Ideas_1,
"Ticker", "_records"
),
With({_top: First(Sort(_records, Date_ENG, Descending))},
{Ticker: Ticker,
Date_ENG: _top.Date_ENG,
Analyst: _top.Analyst,
Location: _top.Location,
QB: _top.QB,
L_S:_top.L_S,
MyID:_top.MyID,
Comment: _top.Comment

}
)
),QB = "HO"&& Location = "QB"),"Analyst",Descending,"L_S",Descending,"Date_ENG",Descending),TextSearchBox1_1.Text,"Analyst","Ticker")

[RandyHayes · Accepted solution]

@Jseo 

Yes, I should have mentioned in case you were not aware - GroupBy, like all functions in PowerApps is limited to the record limit set in your app, with a maximum of 2000.

So, to avoid this, you will need to prefilter your datasource.

Most likely (since you are interested in dates) by the date.

SortByColumns(
    Search(
        Filter(
            ForAll(
                GroupBy(Filter(Ideas_1, Date>DateAdd(Today(), 1, Years),
                    "Ticker", "_records"
                ),
                With({_top: First(Sort(_records, Date_ENG, Descending))},
                   {Ticker: Ticker,
                    Date_ENG: _top.Date_ENG,
                    Analyst: _top.Analyst,
                    Location: _top.Location,
                    QB: _top.QB,
                    L_S:_top.L_S,
                    MyID:_top.MyID,
                    Comment: _top.Comment
                   }
               )
          ),
          QB = "HO"&& Location = "QB"
        ),
        TextSearchBox1_1.Text,"Analyst","Ticker"
    ),
    "Analyst", Descending,
    "L_S", Descending,
    "Date_ENG", Descending
)

(NOTE: always do the sorting as the last function after all the inner filtering and searching - there is no sense in wasting time sorting records that will then be discarded by another function).

In the above, the pre-filter is for records that are newer than 1 year.  If the pre-filter of 1 year still produces more than 2000 records, then change the time range to be less.

[Jseo]

@RandyHayes Thanks a lot for the great solution!