Last and Last N not correct

[NatEvansIT]

Hey guys,

Putting this out there in case there is an issue or I am missing something...

I have an app that I need to work out the last record, and then get X previous records. This was working fine, until recently. Now if I use Last(SharepointTable).ID I do not actually see the last value, but several before. For this example the last ID I see from this is 2020.

If I check the Sharepoint table and see that the last row ID is 2027, for example, then do a LookUp(SharepointTable, ID = 2027).Title IT WORKS!!!!, so my app can see the data but the Last functionality does not...

I have this issue on two apps I have tested so far, I have deleted and re-added the table connector. I have no filters applying, I just want the last record in a Sharepoint list. I have done this many times, yet now the Last function is not correct (by about 5 records by the time of writing this).

Anyone else seeing this?

[timl]

@NatEvansIT 

When you call Last on a data source, Power Apps retrieves X records from the data source and returns the last record (this value depends on the data row limit setting value in the settings).

Therefore, If the number of records in your SharePoint list exceeds the maximum value of 2000, Last will not return the expected record.

The technique to return the correct value is the call First/FirstN based on the data source sorted in descending sequence. eg -

First(
   Sort(YourSharePointList, ID, Descending)
)

[NatEvansIT]

Thank you Timl,

I did try SortBy column in that way, and also just tried your suggestion by sort. No change...

It looks like the values are changing as in the number is higher than when I last ran it (as there are more records), but it is still not the actual last record.

Last(Sort(Helpdesk_Tickets,ID,SortOrder.Ascending)).ID = 2093

Manual look at Sharepoint to see 2097 as the latest row

LookUp(Helpdesk_Tickets, ID = 2097).ID = 2097

[NatEvansIT]

To add sort feels weird as to do descending gives me the lowest ID...

Last(Sort(Helpdesk_Tickets,ID,SortOrder.Descending)).ID = 95 (confirmed lowest ID on Sharepoint)

[MudassarSZ365]

Hi @NatEvansIT ,

You Can try this

FirstN(Sort(SharepointTable, ID, Descending), N)

If this solution helps, please mark it as the answer and give a thumbs up. Thank you! 😊

[NatEvansIT]

Thank you Mudassar...

Already doing this, I didn't paste the full code around it, and am testing on smaller parts but the app was working on the Last 1000 (and is gathering 1000, rows). As I am working with big data, I collect 1000 rows, then on user request can get the last of that collection and collect another 1000 rows.

The fact that this was working before today makes me wonder if something has changed I am not aware of.

I have run only the code below and it is still incorrect:

Last(LastN(Sort(Helpdesk_Tickets,ID,SortOrder.Ascending),1)).ID = 2093

Last(LastN(Sort(Helpdesk_Tickets,ID,SortOrder.Ascending),10)).ID = 2093

[timl]

Hi @NatEvansIT 

Based on your post above, can you confirm what "Helpdesk_Tickets" is? It it the actual SharePoint list or is it a collection?

If it's a collection, note that Collect/ClearCollect collects up to a maximum of 2000 records. This value depends on the data row limit setting. This would explain why you're not seeing the latest records.

The fact that this used to work and doesn't work now is almost certainly down to the size of the SharePoint list.

[NatEvansIT]

Hey timl,

I have tried both, but I am only interested in collecting the last 1000 records from Helpdesk_Tickets which is a SharePoint list. I was trying to use the last record as a way to gather the most recent records in the first place - but maybe there is a better way to do this. I have probably hit a limit that I only now have with the big data (as I have never had big data big enough to learn on this scenario before).

Thanks

[timl · Accepted solution]

@NatEvansIT 

On the basis that Helpdesk_Tickets is the actual SharePoint list, can you retry my earlier suggestion of using the First function (rather than the Last) function to see if that returns the correct/most up to date record?

First(Sort(Helpdesk_Tickets,ID,SortOrder.Descending)).ID 

[NatEvansIT]

Apologies, I thought I had tested your comment. I think I had, but I was focused on the first vs last without combining it with the Sort order early yesterday.

Today I have tried your formula and it correctly gives me the latest SharePoint record. I really appreciate your patience with me! that was my human error missing your comment. I will now mark it as the solution - thank you!