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

Most Recent Record per ID

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

DateUserIDStatus
5/9/20191active
8/5/20201terminated
7/8/20172active
5/6/20192vacation
9/5/20212terminated
10/5/20183active
8/15/20193vacation
4/8/20203terminated
9/5/20213active

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

 

DateIDStatus
8/5/20201terminated
9/5/20212terminated
9/5/20213active

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?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
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

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
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

10 REPLIES 10
Nogueira1306
Super User
Super User

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!

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

 

Jseo_0-1643298223655.png

 

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!

@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
Super User
Super User

@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.

_____________________________________________________________________________________
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!
Jseo
Frequent Visitor

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

 

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

Jseo
Frequent Visitor

@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
Super User
Super User

@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.

_____________________________________________________________________________________
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!
Jseo
Frequent Visitor

@RandyHayes Thanks a lot for the great solution!

Helpful resources

Announcements
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.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,484)