The Challenge

Many a times we come across a scenario where we need to showcase the total count of items in a SharePoint list or library in Power Apps.

Aggregate functions in Power Apps are not delegable to the data source – meaning Power Apps will load a max number of 2000 records in memory for such queries (assuming the limit for delegation in your App has been set to 2000)

To learn about delegation – check out my delegation video series.

CountRows function will provide the count of number of rows in your data source but will run into the delegation limit and will showcase a max number of 2000 even though your data source has exceeded this number.

The Solution

SharePoint lists/libraries showcase the total number of items contained in them when you go to “View all site content” page.

Url: sharepointsiteurl/_layouts/15/viewlsts.aspx?view=14

In order to grab the Item Count value, there is a rest API endpoint available

/_api/Web/Lists/getByTitle('listname')?$select=ItemCount

Now since Power Apps cannot make rest API calls, we will call a flow to get the item count.

In your Power App, add a button control.

Select the button and from the ribbon go to Action > Power Automate

This will open a dialog which will list all the flows that your account has access to and has a Power Apps trigger.

Select the “Create a new flow” button

This will showcase the list of templates associated with the Power Apps trigger.

Select the “Power Apps button” template.

Give the flow a meaningful name – “Get item count”

Create a variable to store the list/library name (Goal is to make this flow dynamic). We can also add a variable for site URL. This would make the flow reusable since we can then get the item count for any list or library across any site.

Create variable using the “Initialize Variable” action as shown below.

Ensure to rename the action to a meaningful name.

Then for the Value property – select “Ask in PowerApps” – this will create a parameter which would need to be passed from Power Apps to flow.

Note: the name of the variable will be the name of the action.

Next add the “Send an HTTP request to SharePoint” action to call the rest API endpoint

Set the action as follows

Check how the action has been renamed to “GetItemCount” and the Uri property has the rest API endpoint with the list/library name coming from the variable which will be getting its data from the Power App.

The site address property can also have its value coming from another variable.

Next add the Respond to a “PowerApp or flow” action

Add a Output property of type Number

Set the value of the property to the following expression

body(‘GetItemCount’)?[‘d’]?[‘ItemCount’]

Save the flow and ensure there are no errors.

Now go back to Power Apps and select the button that should call the flow.

For the selected button, go to Action > Power Automate and selected the newly created flow from the dialog.

This will add a function call to flow on the OnSelect property of the button

The flow Run method will show required parameters depending upon the number of “Ask in PowerApps” actions called in flow.

In this example we created 1 variable for the list or library name so we will pass that value as a string.

Also, the flow will return the item count as an output property which we can store in a variable to use in the App as follows:

Set(varItemCount,PowerAppsGetItemCount.Run(“Students”));

Above formula will run the flow when the button is selected, pass the list name as “Students” (replace Students with your list or library name), the flow will run the rest api call and get the total item count and send that result back to Power Apps which will be stored in a variable named “varItemCount”.

To get the item count values from flow (rest API call) use the following

varItemCount.total_items

where total_items = the name of the output property created in flow.

This technique will give you the total item count in your list or library in Power Apps and you will not run into any delegation issues.

Hope this helps.

Regards,

Reza