(inspiring music) - Hello, there. This is a video about SharePoint lists with more than 5,000 items and Power Apps. If you're trying to build a Power App against a SharePoint
list as your data source, that has got more than 5,000 items in it, you're gonna have issues, and then, when you solve those problems, you're gonna have new problems, and the new problem is
going to be performance. Let's solve the first problem first, getting more than 5,000
items back to Power Apps, and then solve the second
problem, which is performance. So, here I've got an app, let's just run that app, and all this app does, is execute a flow, which runs a do until loop, and builds up an array of items, goes around, and round, and round, getting items from a SharePoint list, until there's no more to get, and then returns it to a
collection in Power Apps. Let me just show you the list, the list is 17,000 rows long, with eight columns, it's not really very much data, and it is kind of a
shame that we have to go to such lengths to get
this data into Power Apps. I started looking at this for a friend, who was building his very
first Power App against a list, very, very similar to this, and he ran up against
the 5,000 item problem, and got a bit stuck, and I said to him, you know, "Just put it in a SQL database, "the problem can be circumvented, "but, you know, "just bang it in SQL." And he said, "Well, I can't do that, "our licensing doesn't allow for it, "and I'm not going to be
able to change our licensing "at this point, "because of a certain
virus that's going round." And it was their first Power App, so he wasn't going to be able
to get the licensing changed on the basis of one small application. The other thing he said to me, is that very few people are actually going to be using this app, everybody else would
just be consuming data, very few editors, and so, ideally they wouldn't
even have to use the app, they could view the data in SharePoint, or Power BI, or some other place, so, just bear that in mind, because I'm going to
apply the same solution, that I apply to this problem
that we've just seen here, to the viewing of the data as well. So this took 95 seconds to run, which is actually quicker
than I expected it to run, but it did get us our 17,000 rows of data. Let me show you the flow, and then you'll understand how it works, and you can build a basic version of it. Now, you can come up
with this flow outright, if your operation to get
more than 5,000 items, isn't going be used in a
user interactive process, I.E. it's not going back to Power Apps, then just use the get items method, like I'm just about to show you, because it doesn't matter
how long it takes to run it, if it's just running in the background, but if you've got users
waiting to get data back, then you need to do something quicker, and this is pony, which means not very good, for those that don't those that aren't familiar with English slang. So, let's go over here. Right, so, this is the one that's just run, over 5,000, and we can see here, it took one minute 23, and there was a bit of time, where it took it to actually
get back to Power Apps. I'll go through this flow in detail, I'm not gonna build it here, because it's already built, and at the end, you can download all of
these flows from my blog, and then just modify
them to your purposes, but I want you to understand how it works, because each iteration is a bit quicker, and a bit quicker again, but, essentially, they all work in the same way, so, at the top, three variables are declared, an empty array, an integer, which is just set to zero, this is going to be used, actually I'll explain later
why it's going to be used, a Boolean, boolEmpty, and then we drop straight
into our do until loop. Now the condition of our do until, and in fact, let's not look
at this in the run history, let's edit it, the condition of the do until, is run until boolEmpty is equal to true, and then we drop straight
into our get items, now I do want the, oh no, we've got run history here as well, I want the run history for this version, because it's helpful. So, we use the standard get items action, in Power Automate, to pull 5,000 items from our list, and the filter query, is where ID is greater than int ID, which on the first iteration, is gonna be zero, and then get a batch of 5,000 records, now this field here, top count, it will let you put in any
value that you choose to put in, but it won't pay any
attention to what you put in, anything more than 5,000
will just be disregarded, it will get you 5,000, and that is true, no matter what method you're going to use, 'cause in a later version of this flow, we're going to use the SharePoint API, but that won't let you get
more than 5,000 either, so we need a loop to get
data in batches of 5,000. Now, the problem with this get items, in fact, let's continue here, so, the output from get items, goes to a union, and what this union does, it unions the output of get items, with our existing array, array items, which is currently empty, in the first iteration it'll be empty, and there the value of
that array is actually set, so the union happens here, and then we set the value of array items to the output of union, 'cause this is a composed action. Then we set the last spool ID, which gets the ID of the highest or the last
record from our record set, so we know where to start
from on the next iteration, so if we look at that in the run history, get items ran, that took nine seconds, and we can probably see, no we can't, the results were union-ed, that took one second, then the array value was actually set, that took two seconds, and then, our last ID was set, which was set to 5,000, and then set boolEmpty, which was false on the first iteration, so we ended up having five
iterations of this loop, and for each of those iterations, the get items took nine seconds, eight seconds, eight
seconds, three seconds, and zero seconds, the reason it took zero
seconds on the last iteration, is because that's when we were on our 20,000 to 25,000
version of the loop, and there were no records to get in there, but there is another problem, in addition to get items
taking a long time, what's happening is, our array items is getting bigger, and bigger, and bigger,
with every iteration, so it starts to take two seconds, then it's four, then six, another six, and another six, so that's a lot of time we're wasting, just on setting the value of that array, and the reason that this
get items take's so long, is because the get items action, when it reaches out to
SharePoint to bring items back, it brings back a whole
load of other information, that we're actually not
interested in, metadata, and other bits of, well, it's mainly metadata, and other columns that
are in the SharePoint, that aren't actually
in the SharePoint view, it brings them back, so we need to get rid of that stuff, and the easiest way to do
that is with a select query, so if we now go back to the app, and run a version two, this is get items with a select action, so we'll leave that running, and go back to the flows and
have a look at version two. Version two is almost exactly
the same as version one, apart from to do until loop, the output of get items, is pushed into a select action, and that thins the data down. Now, if you have a look at the output, in fact, I've got it here, so the output of the
version one for one record, looked like this, so, everything from here to there, is information that we
didn't actually request, it's not in the SharePoint view, and so in this case, the metadata that's being
brought back by SharePoint, is actually bigger, than the actual data
that we're interested in, and this resulted in a three
and a half meg JSON payload, coming back into Power Automate, and then also being
loaded into that array, so that array, by the time it was run five times over, is gonna be like 18 to 20 odd meg, it's just too big, and, actually, that data will end up going
back into Power Apps as well, and probably slowing
things down there as well, so, let's go and have a look at our app, see if it's finished, yeah, that's now finished, and took 59 seconds, so we went down, we when we went from 95 second, down to 59 seconds, just by adding that select in, and making the array smaller. So, let's just go and look at the run history of that, version two. So, although our array
build here got quicker, we didn't do anything to speed
up the query into SharePoint, so that's the next thing
that we needed to tackle, because the SharePoint part, is still a heavy going operation, and the easy way to do that, is to use the SharePoint API, so let's go back to our app, and use the SharePoint API to
retrieve the items instead, and this actually makes
for a more simple flow, because we don't need
the select items action, we can use the SharePoint API to say, "Just give us the
information that we want, "and none of the information
that we do not want." So let's go and have a look at that, my flows, version three, and we'll also get the run
history up for that as well, version three, run history, and we can see already
that it took 25 seconds, so that must be finished back here, so, based on the SharePoint API, we've got it down to 30 seconds, our little user state emoji
still isn't very happy, and in my opinion, for a user to open up an app, and wait 30 seconds for a
data response, is too long, they'll be pissed off by then. So, let's have a look
at the do until loop. So now, our SharePoint requests
are down to two seconds, one second, and one second, and here's how it works, instead of using the get items, we say, "Send out a HTTP
request to SharePoint." And we're gonna use the
API method, get by title, get by title, and then the
title of the actual list that we're trying to get items from, we give it in select query, our columns, and then the filter query is the same as we used in the get items. Now, this part here is super important, the header, if we put in, oh data equals no metadata, it will just give us back the information that we're interested in, and won't return the new metadata, which results in a much, much smaller JSON payload, which makes the whole
thing much quicker to run, we don't then need the select action, so we go straight from
our send HTTP request, to union-ing the array, the other operations are all the same. So, that's pretty good, we're getting there, we've got down from 95
seconds to 30 seconds, the problem is, is that if we look at the run history, so we get 5,000 records each iteration, but we don't start getting the next 5,000, until the first 5,000 have been received, and so on, and so on, and so on, so, we need a way to speed that up, and fortunately, Power Automate
has got a way to do that, called parallel branches, so we can execute multiple queries at the same time to SharePoint, and we'll get all the data
back at the same time, more or less, hopefully, so let's give that a go, I'm going to go back to the app, and I'm gonna skip, I'd done six versions, you don't need to see all of them, there's a version with
two parallel branches, and a version with four, another one, which I tweaked a bit, which, we'll run straight to that, so let's try that. The emoji is still happy, still not as quick as I'd like it to be, but it is a massive improvement. And we've got 10 seconds, so we're almost 10 times
faster than our original, no, 11 seconds, let's try it again, that was slightly disappointing, it's always quicker the second time round, nine seconds, so we got there 10 times faster, that's what we want. So, let's go and have a look at this flow, now, I don't know if I've
already mentioned it, but you can just download
all these flows off my blog, and adjust them to your requirements, but it's good to understand
what's going on as well. My flows, version six, let's get the run history, you can see it took seven seconds, again, it took a couple of seconds to return it to Power Apps, so, what we're gonna do, is there's a couple of extra
operations in this one, it uses the SharePoint
API to get the lowest ID, which is still get by the title, and it's just getting the top one, instead of the top 5,000, get the highest ID, and get the total item count, which isn't get by a title, there's a separate API
method called item count, so we use that there, and we'll set a variable, item count, to the value that comes back
from the item count action, then, just like the previous queries, we're gonna define an array, but this time we're
gonna define four them, one, two, three, four, same with the SharePoint IDs, four of those, and same with the Boolean variables, four of those, because each of those four, is going to be used in a
separate parallel branch, and they've got a condition, that says if the item
count is greater than 1000, then do all this fancy
parallel branch stuff, if it isn't, then just go ahead and get
the items from SharePoint in a single batch of 5,000, and respond. Now, if you had less than 5,000, you probably wouldn't
be using this anyway, or even using Power Automate, you'd just be using the
standard connector built into Power Apps, so if it is yes, then it does this, let me just copy this into Notepad, so it's a bit easier to read. So, it subtracts, subtracts the lowest
ID from the highest ID, and divides that by four, that figure, so that just gives us sort of a guess, but it'll be, broadly speaking, more or less correct, of the size of chunk that we want to get per parallel branch, so we'll go back here now, and then we've got four parallel branches, it would've actually been easier to show you this with the
two parallel branches, but hey ho, so, the condition is
the same for each one, if the boolEmpty is true, then you've reached the end, and then we do our HTTP requests. So, the HTTP request, which is slightly easier
to see in the run history, it's going to do select where the ID is greater than minus one, which is the value of our integer, and less than, or rather less than or equal to 4,250, which is the calculation from up here, from this divide total by four, second quarter, does where
the ID is greater than 4,250, so it starts off where
this one finished off, and less than 8,499, yeah, that's good, and then this one, it's gonna be where it's
greater than or equal to 8,499, and less than 12,478, and this one that's saying, greater than or equal to 12,478, so again, it's starting off
where this one finished, and there's no top, there's no ending figure, because this is just
gonna get the remainder of whatever's left, so let's have a look at how that run, so let's just close these up, so we can see what we're doing, so these are only ran one iteration, 'cause I've only got 17,000 rows, each batch could get up to 5,000 records, so we didn't have to have
an additional iteration of the loop, so it took three seconds, four seconds, four seconds, and three seconds, but it only takes as long, as the longest branch took to execute, so in this case, four seconds, and then they all drop down together, to combine arrays and respond, so if we look at that in Power Automate, the final action is just to respond with a union of the four arrays, and that gets the data back
to Power Apps so much quicker, as we can see, in nine seconds, and that's the result, you know, from 95 seconds down to nine, is brilliant. I still don't think you should have to go through all this crap, but you do, so get on with it. Now, I haven't obviously
gone into any detail about how to execute a
flow from Power Apps, I'm not gonna show you
this button that I'm using, because it's full of switch statements, having said, I'm not going to, I am actually going to, here, you can see that it's
just creating a collection, called employee details, V one, and then these are the names
of the flows themselves. Now, earlier on in the video, I said that the guy that I
was helping build the app, or who was asking me for advice, also wanted a way for
people to consume it easily, and actually viewing this
many items in SharePoint, searching through it, et cetera, isn't that easy, and that's your and expert in
setting up SharePoint lists, and creating indexes, et cetera, et cetera, which this person was not, so I thought, "Do you know what? "We can just edit this flow, "make one tiny edit." So, if I go back to the flow, now, I'm cheating a bit here, because this does require
a premium connector, but you could do it with just one license of
a premium flow connector, you wouldn't need the flow connector to be premium for everyone, change that first
condition from Power Apps, to when an HTTP request is received, and then all I did was build, I just got some, you know, there's so many J query
based grid controls, all this grid control does is
connect to that HTTP trigger, and gets exactly the same data that Microsoft Power Automate was using, and in here, we can flick through this whole data, no problem at all, we can search it, let me put something in, Holloway, and there are all the Holloways, and then we can also do fancy things, I mean, this grid can do a
hell of a lot of fancy things, I didn't really play with it, so it could group it by brand, and then we could group it by furloughed, or even move those two things around, and there, it's easy, and that is a really, it's kind of strange to
think it's actually easier to view data that's stored in SharePoint, outside of SharePoint, but this does work really well, it took me five minutes to
put it together, if that, this is using, I forget what it's using, I'll put the code on my blog, DX Extreme, or something, but there's so many J
query based grid controls, and that's it, so that's quite successful, we've brought back 17,000
records in nine seconds, and created another method of being able to view that data, for the people that don't
need the app, pretty good. See how you get on, if you get stuck, leave a comment, I'll try and help you out, otherwise just go to the blog, download the flows, smash them up to your environment, customize them, try it out, and if it works, it works, but do you take the time
to just understand it, if you can, and don't bother with that get items method if
you're using Power Automate to get more than 5,000, just go straight to the SharePoint API, that's gonna make your
life so much easier, and it's going to return
those results so much faster, don't bother with the parallel branches, if you don't need them, if you haven't got that much data, if you have got a lot of data, then you could use four, six, 10, however many you need, to get the data from your list
as quickly as you need it, and it seems like SharePoint can handle
the multiple requests, simultaneously, no problem at all, so that adds some additional value to using SharePoint as a data store, which I don't personally like doing, but I can totally
understand why people do it, based on licensing constraints, so, see how you go, good luck, let me know if you need any help. Cheers, bye-bye. (inspiring music)
