How to go to a particular record after filter

[MIA27]

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

[RusselThomas]

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

[MIA27]

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

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:

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]

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

[MIA27]

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:

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 :

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]

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

[MIA27]

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 · Accepted solution]

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