cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MIA27
Post Prodigy
Post Prodigy

How to go to a particular record after filter

I am doing the below filter, after that I can to reach to the max value of the a particular column

My Filter of the gallery is as below and is working fine:

Sort(Filter(InqTabA,HistSearch1.Text in InqMach || HistSearch1.Text in InqSerNo),ID,Descending)

 

BUT I want to got the max of ID value of the filtered record.

I am aware to use Lookup(), but confused how to use the lookup along with the above

 

Please advise

 

1 ACCEPTED SOLUTION

Accepted Solutions
RusselThomas
Community Champion
Community Champion

Hi @MIA27 ,

Yes, but because you can only have one Default: value, you have to shift your Default: property from an expression to a variable.  What this means is, instead of having 

Last(Filter(TblMachHist,TextInput3.Text in SerialNo))

as your Default: property, you must have a variable record - which gets defined by pressing either one of two buttons.  As long as that variable record points to a record that exists inside the gallery, it will work.

 

Set your gallery Default: property to;

varDefaultRecord

Change your button Text: to "Last record" and set its OnSelect: property to;

UpdateContext({varDefaultRecord: 
                 Last(Filter(TblMachHist,TextInput3.Text in SerialNo))
              };
Reset(MachData)

Copy and paste the button to create a new button, change it's Text: property to "First Record" and its OnSelect: property to;

UpdateContext({varDefaultRecord: 
                 First(Filter(TblMachHist,TextInput3.Text in SerialNo))
              };
Reset(MachData)

That should do it 😊

Kind regards,

RT

View solution in original post

7 REPLIES 7
RusselThomas
Community Champion
Community Champion

Hi @MIA27 ,

The Max ID of a table would be the

  • Last() record, sortedy by ID, Descending OR
  • the First() record sorted by ID Ascending

It looks like you're already sorting by Descending, so wrapping this inside a Last() statement should give you the record;

Last(Sort(Filter(InqTabA,HistSearch1.Text in InqMach || HistSearch1.Text in InqSerNo),ID,Descending))

and if you want a specific field from the record (like the ID);

Last(Sort(Filter(InqTabA,HistSearch1.Text in InqMach || HistSearch1.Text in InqSerNo),ID,Descending)).ID

 Hope this helps,

RT

Dear Mr. Russel,

 

Thank you for your reply.

I applied Last and First() function, but it does the same type of result like filter.

For example to keep simple , I applied the below to test:

Last(TblMachHist) , it bring only one record instead of going to Last record

MIA27_0-1657871584318.png

 

 

My requirement is like below screen , the user should see the screen as below (note record 266 is the last)

and once required they can roll the records up and down as normal gallery:

MIA27_1-1657871756463.png

 

Just to explain you better, please note that in Access VBA we use to use a command called

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdRecordsGoToLast
End Sub

 

This use to go to the last record at the same time, when required, u can roll on the records.

 

BUT lookup, Last, first, all these command is filtering particular records and does not allow to roll.

 

Please advise

Regards

 

RusselThomas
Community Champion
Community Champion

Hi @MIA27 ,

So it sounds like you want the gallery to set focus on the last record - is that what you mean?

 

If so, then you need to set the Gallery Default: property of the Gallery to the Last() record of the table that is supplied to the Gallery Items: property, then Reset() the gallery.

So, for example, if your Items: property is on your gallery is

 

Sort(Filter(InqTabA,HistSearch1.Text in InqMach || HistSearch1.Text in InqSerNo),ID,Descending)

 

Then the gallery Default: should be set to

 

Last(
Sort(Filter(InqTabA,HistSearch1.Text in InqMach || HistSearch1.Text in InqSerNo),ID,Descending)
)

 

Then you just need an action to trigger the gallery reset in order to apply the default - you can test by adding a button and setting its OnSelect: property to

 

Reset(yourGallery)

 

The last item in the gallery should then be selected when you hit the button - if you want to be sure, you can also set the gallery TemplateFill: property to 

 

If(ThisItem.IsSelected, LightBlue, RGBA(0, 0, 0, 0))

 

This will highlight the currently selected item to make it easier to see.

 

Hope this helps,

RT

Dear Mr. Russel,

As you mentioned, I checked with the test with simple sorting without filtering on 2 screens (one gallery with 3 records and another gallery with 266 records).

Found On On little record gallery result comes but more records gallery screen result does not show it gets done but in different way. 

I will first mentioned how I tested and the result screen:

 

On Item for Gallery : 

Sort(TblMachHist,FormNo,Ascending)

 

On Default For Gallery:

Last(Sort(TblMachHist,FormNo,Ascending))

 

On TemplateFill of the gallery :

If(ThisItem.IsSelected, LightBlue, RGBA(0, 0, 0, 0))

 

Made a button and Onselect : 

Reset(MachData)

 

If the data is less -like I tested with 3 records. It open the screen and the selection is on the last record , this the result is correct:

MIA27_0-1657887340844.png

 

BUT If the data is 266 records, which on opening it should be  the screen - but remain as it is - Then I press the button still it remains as it is :

MIA27_1-1657887399732.png

 

Now this 266 records gallery works, if I roll and remain some where to 262 records on the screen (and the 266 records can be seen on the same screen) and then press button, I find the last record got select.

 

I think your advise code is working but the screen does not roll to focus to the last record. 

Please advise how to solve this.

 

 

RusselThomas
Community Champion
Community Champion

Hi @MIA27 ,

Short answer is that you should apply the filter, but this may not always give you the results you expect.

 

Hopefully I can explain this without completely confusing you as some may be lost in translation...so I'll try avoid the complex stuff and stick to the basics;

 

Galleries connect to tables that come from one of two places;

  • an external data source (like SharePoint or SQL), or
  • from inside Power Apps (like a collection or a table output from a command). 

For performance reasons, galleries behave differently when connected to an external source as opposed to when they are connected to a collection or a table inside PowerApps.

 

For example, if a gallery is connected directly to a SharePoint list (without a filter function), the gallery knows there is data transfer involved in the query, so it will try and be efficient by fetching only the first 100 rows of data to reduce performance impact. 

Obviously if your source has less than 100 rows, this doesn't matter, but if it has more, the gallery will fetch the next 100 only if you continue to scroll down to the end of the gallery (you'll see the query dots run on top of the screen, then the scroll bar will jump and you'll have 100 more rows in your gallery) - if you continue scrolling the gallery will eventually get to the end of your data, even if you have 10000 rows in your source.

 

To get the reset working, the Default: record must exist in the current gallery dataset when reset() is called.

The reason your reset doesn't work is that your Default: expression record is at the end of the dataset, and this doesn't exist in the gallery, until all the rows have been loaded -  which only happens when you scroll to the end.

 

So the quick solution here is probably to apply your filter function, as this automatically fetches the maximum number of rows allowed by your data row limit, instead of just the first 100.  Provided the filter result does not exceed your data row limit, it will work.  As soon as the filter result exceeds your data row limit, and your Default: points to a record beyond this limit, (which Last() will always do) there will be a mismatch between the Last() result applied to the dataset and the Last() result in the gallery items.  This is why we apply the filter() to the last function as well, so that the resulting record is inside the same dataset returned by Items:

  

Note: Now, I have to warn you - there are always data row limit and delegation considerations that will affect this experience - so when your source has a large amount of rows, things start behaving quite differently - but it may be a bit much to explain for now, so I've included some links to read when you can - but just bear in mind things start to change when you have a large amount of data in your source - say > 500 rows.  You can change your data row limit in your app settings to 2000, but that's the max.  Eventually, you'll have to figure out the best way to filter large data sets - but for now.... 

 

The short-term solution is to try and make sure your gallery contains all the rows you're going to need up front. 

You can do this by specifying a filter that only fetches the rows you need, and just make sure that same filter is applied to your Default: Last() function.

You can also Collect() the data into a collection and connect your gallery to that instead, using the collection also in your Default: Last() function.

 

Whatever way you choose to go about it, just remember that the resulting record from Default: must exist inside the gallery at the time of reset.  When you're connecting to an external source with more than 100 rows or more than your data row limit - this may not always be the case.

 

Hope this helps explain somewhat - sorry if sounds complicated - but it is a little complicated 😁

Kind regards,

RT

Dear Mr. Russel,

Thank you so much for your time and details explanation which is helping me to learn more and more on powerapp.

Based on your guidance, removed the sort and performance is better. 

Items: Filter(TblMachHist,TextInput3.Text in SerialNo)

Default: Last(Filter(TblMachHist,TextInput3.Text in SerialNo))

Button - Onselect : Reset(MachData)

 

It works, the screen opens normal, later when searched or click button from that time onward it always move to the last record, which was the requirement.

 

Now planning to put 2 button, one click to get the above job, and another button to reach to first record.

So user can either go to first or last based on button click.

But default can be applied once only.

 

Is it possible to put some code like First(Filter(TblMachHist,TextInput3.Text in SerialNo))

on the another button so that the first record get selected.

Please advise 

 

 

 

 

RusselThomas
Community Champion
Community Champion

Hi @MIA27 ,

Yes, but because you can only have one Default: value, you have to shift your Default: property from an expression to a variable.  What this means is, instead of having 

Last(Filter(TblMachHist,TextInput3.Text in SerialNo))

as your Default: property, you must have a variable record - which gets defined by pressing either one of two buttons.  As long as that variable record points to a record that exists inside the gallery, it will work.

 

Set your gallery Default: property to;

varDefaultRecord

Change your button Text: to "Last record" and set its OnSelect: property to;

UpdateContext({varDefaultRecord: 
                 Last(Filter(TblMachHist,TextInput3.Text in SerialNo))
              };
Reset(MachData)

Copy and paste the button to create a new button, change it's Text: property to "First Record" and its OnSelect: property to;

UpdateContext({varDefaultRecord: 
                 First(Filter(TblMachHist,TextInput3.Text in SerialNo))
              };
Reset(MachData)

That should do it 😊

Kind regards,

RT

Helpful resources

Announcements

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

  It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd. at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3rd, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!       Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪🌠

Tuesday Tips: Community Ranks and YOU

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!This Week: Community Ranks--Moving from "Member" to "Community Champion"   Have you ever wondered how your fellow community members ascend the ranks within our community? What sets apart an Advocate from a Helper, or a Solution Sage from a Community Champion? In today’s #TuesdayTip, we’re unveiling the secrets and sharing tips to help YOU elevate your ranking—and why it matters to our vibrant communities. Community ranks serve as a window into a member’s role and activity. They celebrate your accomplishments and reveal whether someone has been actively contributing and assisting others. For instance, a Super User is someone who has been exceptionally helpful and engaged. Some ranks even come with special permissions, especially those related to community management. As you actively participate—whether by creating new topics, providing solutions, or earning kudos—your rank can climb. Each time you achieve a new rank, you’ll receive an email notification. Look out for the icon and rank name displayed next to your username—it’s a badge of honor! Fun fact: Your Community Engagement Team keeps an eye on these ranks, recognizing the most passionate and active community members. So shine brightly with valuable content, and you might just earn well-deserved recognition! Where can you see someone’s rank? When viewing a post, you’ll find a member’s rank to the left of their name.Click on a username to explore their profile, where their rank is prominently displayed. What about the ranks themselves? New members start as New Members, progressing to Regular Visitors, and then Frequent Visitors.Beyond that, we have a categorized system: Kudo Ranks: Earned through kudos (teal icons).Post Ranks: Based on your posts (purple icons).Solution Ranks: Reflecting your solutions (green icons).Combo Ranks: These orange icons combine kudos, solutions, and posts. The top ranks have unique names, making your journey even more exciting! So dive in, collect those kudos, share solutions, and let’s see how high you can rank! 🌟 🚀   Check out the Using the Community boards in each of the communities for more helpful information!  Power Apps, Power Automate, Copilot Studio & Power Pages

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

Top Solution Authors
Top Kudoed Authors
Users online (4,357)