Power Automate: Need more than 100 records?

I’m sure you noticed by now, but if you didn’t, here’s something you should know. When you do a “Get Items” from a SharePoint List, for example, Power Automate only fetches the first 100 records. There are several reasons to do so.

1. Save resources. It’s resource-intensive to compute a JSON file to send to Power Automate based on the items in a SharePoint list.
2. Save time. If your list has many values, it could take some time to get all items so that your Flow could start iterating them.
3. Best Practice. It’s never a good practice to get all items from any data source. You should always be in control of the items you want a limit them as most as possible.

But what if you’re not getting the items you want?

First, limit the information.

Your strategy should always be to limit the information that you fetch. Let’s check some strategies to do so.

Filtering

First, let’s filter the data that we get. It’s always a good practice to only fetch the data. We need to use the “Filter Query” field using OData queries. If all of this is strange to you, I’ll soon create an area to explain all of this (here’s a little sneaky announcement for you). If the link doesn’t work yet, don’t worry. It will come soon.

With simple queries like:

nameOfTheFiels gt 10

You can filter the information that you get and have less to parse.

Top

The top is another way to filter the information. The top will return the first X number of items, but always less than 100. If you insert a number greater than 100, SharePoint will only return 100 records.

The description can be a little bit misleading. The default result is not “all” is 100 or less. If you get, 100 don’t assume that you only have 100 records.

You can use cool tricks like reversing the order of your data and then fetching the top items. Then you’re always getting the most recent values for your list.

Limit the columns

I always like to mention the limit of columns because it’s quite rare that we will need all columns in a SharePoint list, for example. You can define a view that has only a subset of columns and fetch only that information.

This will make your Flow fetch the information faster than having all the columns returned.

If you can’t filter

Of course, there’s only so much we can do with 100 items. So let’s check how to increase this limit. To do so, you need to activate pagination. Pagination is a strategy where you get 100 items and a link to the next 100 items.

Enable pagination

To test, let’s use a Flow that fetches all elements of a list and counts them.

The count is as follows:

length(outputs('Get_items')?['body/value'])

As you can see, we get the first 100 (there’s more, and we’ll see them in a bit).

Now let’s activate pagination:

Enable pagination and add the items you want per page:

Here’s the result with pagination.

In this case, since it’s less than a full-page, we’ll get all items.

Get to the next page.

If you set a lower number than the total, you’ll get a “next link” to the next X number of records. It’s the first item in the body of the response.

With this strategy, you can provide, for example, a pagination experience in your Power App, for example, where the users can fetch only the information they need.

Please take into consideration 2 things:

1. The page has the number of items you define, but they need to be bigger than 100. Otherwise, Power Automate will default to the 100 max items.
2. There are some reported cases where the pagination link doesn’t work. There is no workaround reported, and it’s already reported for Microsoft’s team to fix, but keep this in mind if something “strange” happens.

Final thoughts

Like I mentioned before, pagination is your best friend when it comes to getting more information from SharePoint Lists (or other data sources). But keep in mind the small “gotchas.” Please keep in mind also that you should always limit as most as possible the information that comes from the server.

Photo by Charlie Larkman on Unsplash