Blog

Data - How to return the last record from a table

A common question from app builders is, how do we return the last record from a table? The reason for this question is because the Last function may not return the expected record. This post examines this behaviour and describes the most efficient way to return the last record from a table.

A common requirement is to retrieve the last record from a table. One reason to do this could be to fulfil a requirement to assign a sequential unique number/identifier to a record. This unqiue identifier number may be prefixed with some other record attribute (such as a customer number) and therefore, it's necessary to retrieve the last record to generate this identifier.

The challenge that app builders encounter is that the Last function doen't return the expected record. This post recreates this issue and describes the most effective way to return the last record from a table.

Demonstration of problem

To highlight this problem, let's take the example of a table called issues which is shown beneath. There are currently 2,502 records in this table, and the 'IssueID' of the last record is 2502.



There is a natural inclination to retrieve the last record by calling the Last function like so:

Last('[dbo].[Issue]')

If we apply this technqiue however, the formula incorrectly returns record 500, rather than our expected record with the IssueID 2502. 


Why does the Last function not work?

Power Apps derrives the last record by fetching records from the source table and returning the last record. For performance reasons, it doesn't fetch all the records. Instead, it fetches a batch of records and the number of records that it retrieves corresponds to the data limit setting, which by defaults is 500.

This is the reason why the Last function returns record 500 in this example.



How to correctly return the last record

The correct way to return the last record is to sort the records in descending order (by ID or 'create date' value) and to return the first record from this result set. The syntax we use would look like this:

First(
      Sort('[dbo].[Issue]',IssueID, Descending)
)

The screenshot beneath shows the output and illustrates how the formula returns the correct record (record ID 2502).


However, notice how there's a delegation message that warns that the first operation is not delegable. It's important to note that for this given scenario, the delegation issue does not prevent us from returning the expected record. For practical purposes, we can therefore ignore this warning.

How to return the last record in the most efficient way

If we only want to return the single last record, the First/Sort technique from above is not efficient. This is because Power Apps fetches a batch of 500 records (ie, the data row limit value)  just so that we can retrieve one record from this batch.

If we monitor the request, we can confirm this behaviour and see all 500 records that are returned in the response.


Therefore, the most efficient way to retrive the last record is to call the FirstN function to return a single record from the table sorted in descending order, and to return the first record from the result of FirstN. The syntax would look like this:
First(
      FirstN(Sort('[dbo].[Issue]',IssueID, Descending), 
             1
      )
)

As we can see from the the screenshot, the formula returns the correct record (record ID 2502) and the delegation warning also disappears.

Conclusion

If we want to return the last record from a table, the Last function may work as expected. The most efficient way to return the last record is to combine the First, FirstN, and Sort functions to return the last record from a table, sorted in descending order.
  •   Categories: 
  • data
Previous
Next
Related posts