I recently got a task where I needed to get the first record, the last record, the first 10, and the last 10 records in Power Apps. Fortunately, Power Apps provides some functions like First, FirstN, etc., to achieve it very easily.
In this Power Apps tutorial, I will explain everything about the First function in Power Apps, Last function in Power Apps, FirstN in Power Apps, and LastN in Power Apps with different scenarios.
Also, we will see how to work with Power Apps lookup first record and Power Apps lookup last record.
First Function in Power Apps
Power Apps First is a function that returns the first item of a data source or table. The First function returns a single record or an item.
Syntax:
First( Table )
Where,
Table = This is required. You need to specify the table name to operate on
FirstN Function in Power Apps
PowerApps FirstN is a function that helps return the first set of records of a table. The second argument defines the number of records to be returned.
Syntax:
FirstN( Table [, NumberOfRecords ] )
Where,
- Table = This is required to specify the table name to operate on
- NumberOfRecords = This is an Optional that will help you to return the Number of records. If you do not specify this argument, then the function will return only one record or item
Power Apps Lookup First Record
I have a SharePoint list named “Employee Onboarding,” and this list contains the below fields.
Column Name | Data Type |
Employee ID | It is a default single line of text |
Name | A single line of text |
A single line of text | |
Gender | Choice |
Joining Date | Date and time |
Department | Lookup |
This Lookup column [Department] is added from another SharePoint source list named “Departments,” which contains the fields below.
Column Name | Data Type |
Department | It is a default single line of text |
Manager | Person or Group |
In Power Apps, there is a Data table control with SharePoint list records. I would like to display the first record on the data table using the Power Apps First function.
To achieve it, insert a Data table control on the Power Apps Screen and set its Items property using the code below.
Items = First('Employee Onboarding')
Where,
- ‘Employee Onboarding’ = SharePoint Online list
Now, Preview the app. The data table displays the first record of the SharePoint list, as shown below.
Similarly, to get the first four records from the SharePoint list, apply the formula below to the Data table’s Items property.
Items = FirstN('Employee Onboarding', 4)
Where,
4 = Number of records you want to display in the data table.
This is how we can use the First and FirstN Functions in Power Apps.
Last Function in Power Apps
Power Apps Last function returns the last item or record of a data source or table.
Syntax:
Last( Table )
Where,
Table = This is required to specify the table name to operate on
LastN Function in Power Apps
PowerApps LastN is a function that helps to return the last set of records of a table, whereas the second argument defines the number of records that are to be returned.
LastN( Table [, NumberOfRecords ] )
Where,
- Table = This is required to specify the table name to operate on
- NumberOfRecords = This is an Optional that will help you to return the Number of records. If you do not specify this argument, then the function will return only one record or item
NOTE:
When you use the data source, all these functions can’t be delegated. You can get only the first portion of the data source and then the function applied. When the data source item crosses the limits, a warning may appear to remind you of the Power Apps Delegation.
Power Apps Lookup Last Record
In this example, I will take the above SharePoint lookup list [Employee Onboarding] to display the last record on the data table using the Power Apps Last function.
To do so, insert a Data table control and set its Items property to the code below.
Last('Employee Onboarding')
Likewise, To retrieve or display the last 5 records, you can apply the below formula on the Data table’s Items property:
Items = LastN('Employee Onboarding',5)
Where,
5 = Number of records that you want to display in the data table
Once you preview the app, you can view the last four records in the data table control, as shown in the screenshot below.
This is how we can use the Last and LastN Functions in Powe Apps.
Power Apps Get First item in Collection
To get the First item or FirstN items in the collection in Power Apps, follow the example below.
Scenario:
I have created a Power Apps collection [colBooks] containing some records, as shown below.
Input:
Now, I would like to get the first record in the collection using the First function and display the result on the Gallery control.
Output:
To work around this, follow the below steps. Such as:
1. To create a Power Apps collection, select the App object [From the left navigation] and set its OnStart property as:
OnStart = Collect(
BookCollection,
{
BookName: "The Testaments",
Author: "Margaret Atwood"
},
{
BookName: "Cheque book",
Author: "Vasdev Mohi"
},
{
BookName: "The Overstory",
Author: "Richard Powers"
},
{
BookName: "Come! Let's Run",
Author: "Ma. Subramanian"
},
{
BookName: "Spare",
Author: "J. R. Moehringer"
}
)
Where,
- BookCollection = Power Apps collection name
2. On the Power Apps Screen -> Insert a Gallery control and set its Items property to the code below.
Items = First(BookCollection)
3. In the same way, if you want to get the LastN [Last 4 items] in the collection, you can use the below code on Gallery’s Items property.
Items = FirstN(BookCollection,4)
This way, we can get First, FirstN items in the collection.
Power Apps Get Last Record in SharePoint List
Finally, I will discuss how to get the last record in the SharePoint Online list using a simple example.
Example:
I have a SharePoint Online list named “Product Details” and this list contains the below fields.
Column Name | Data Type |
Product Name | It is a default single line of text |
Manufacturer | Choice |
Price | Currency |
Quantity | Number |
Purchase Date | Date and time |
Now, I want to get the last record in the SharePoint list and display it on the Power Apps gallery control.
To do so, on the Power Apps Screen, insert a Gallery control and set its Items property to the code below.
Items = Last('Product Details')
Similarly, if you want to get the last 2 records from the SharePoint list and display them on the gallery control, you can follow the code below.
Items = Last('Product Details',2)
This is how we can work with the Power Apps get last record in the SharePoint list.
First vs FirstN Expression in Power Apps
In this example, I will show the difference between Power Apps First and FirstN functions. I will also take the above SharePoint list. Now, I want to get the third record of the Product Name field [“Air Conditioner“] on the Power Apps text label.
To do so, on the Power Apps Screen, insert a Text input control and set its Text property as:
Text = FirstN(
'Product Details',
3
).Title
Where,
- ‘Product Details’ = SharePoint Online list
- Title = SharePoint text field
Here, the problem is that when I use the First function to get the first value, the output returns the expected value. Not only the first function but also the Last function returns the last value.
But when I use the below syntax as FirstN, it generates an error message: “Expected Text Value. “ That means the property on this control expects text values.
To resolve this issue, you can set the below code to the Text label’s Text property.
Text = Last(
FirstN(
'Product Details',
3
)
).Title
Once it is done, Preview the app. The Power Apps text label displays the third record of the specific SharePoint list filed, as shown below.
Also, you may like:
- Navigate Function in Power Apps
- Power Apps Today Date Without Time
- Power Apps Rating Control
- Replace in Power Apps
- Display SharePoint List in Power Apps
- Power Apps Start Timer On Button Click
- Power Apps Len Function
From this Power Apps tutorial, we learned in detail information about the Power Apps First, FirstN, Last, and LastN functions, including:
- Power Apps Lookup First Record
- Power Apps Lookup Last Record
- Power Apps Get First item in Collection
- Power Apps Get Last Record in SharePoint List
- First vs FirstN Expression in Power Apps
I am Bijay a Microsoft MVP (10 times – My MVP Profile) in SharePoint and have more than 17 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com
Bijay, great tutorial. Now I understand the 4 functions a lot better, ready to try them in my PowerApps app.
Estuve buscando la forma de traer el segundo o tercer registro y gracias a ti lo pude hacer mil gracias!!
English: I was looking for a way to bring the second or third record and thanks to you I was able to do it thank you very much!!