cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Collection more than 2000 rows

Hello there!

I am having 7000 rows inside of my SharePoint database. Since I am using PowerAutomate to automatically update my SharePoint list, I can't use the unique ID that comes with every row to make a big collection. 

At first I was only having 4000 rows at maximum, but this became 7000. I was using this code earlier, which wont work anymore;

ClearCollect(col1; ProjectLijsten);;
ClearCollect(col2; FirstN(Sort(ProjectLijsten; ID;Descending);2000));;
ClearCollect(colCombined;col1;col2)


How can I make a big collection? (I am using the Distinct operator too, so I need to make a collection)

9 REPLIES 9
Highlighted
Super User III
Super User III

Re: Collection more than 2000 rows

@TimoMigchielsen ,

I believe I have answered something similar for you before. The only real way is as per the “big collection” process in this blog on Delegation I wrote a little while back.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Post Prodigy
Post Prodigy

r ansRe: Collection more than 2000 rows

Thank you for your answer @WarrenBelz 

I have looked at your blog multiple times, I don't think I can use any of these. Since I can't use the unique ID of the row, right? 

Highlighted
Super User III
Super User III

Collection more than 2000 rows

@TimoMigchielsen ,

You can indirectly by creating the "shadow" ID numeric field as noted. I was just trying to point out that there is simply no other way of reliably collecting more than 4,000 rows (your example would produce duplicates and I have a more accurate/reliable process for 4,000 in my blog on using the With() statement ) other then by using this type of counter.

Delegation is a restriction that needs to be managed by planning data structure (it is not an error) and unfortunately comes more when using a non-premium data source.

One example would be if all the Distinct records were in the newest 2,000 records, or if you could filter to under 2,000 with a Delegable filter first.

Highlighted
Post Prodigy
Post Prodigy

Re: Collection more than 2000 rows

Whenever we use the Distinct operator, there will be less than 2000 rows. So I guess we can do something with that @WarrenBelz 

I am using the Distinct filter to get out the double rows with the same information. I have multiple rows for each project with a different number behind it.

example;

Project;    Number
19037      1
19037      2

Highlighted
Super User III
Super User III

Re: Collection more than 2000 rows

@TimoMigchielsen ,

If you have another unique numeric identifier, you can also look at using that. Also the Distinct filter will only find Distinct values from the first 2,000 records, not the first 2,000 Distinct records.

Highlighted
Post Prodigy
Post Prodigy

ptioRe: Collection more than 2000 rows

Sadly that is not an option @WarrenBelz 

There are around 8000 rows in there, here is an example of why there are soo many rows in there and I am using the distinct function. 

Here is how my database looks; https://gyazo.com/17dbb4432e3d65f9c33565d4f5014a33

 

There are rows with the same Order number, but with a different Bon nummer. I am having 2 screens; 

The first screen is showing all of the unique orders, so for example, in the screenshot above, you see 16018 4 times. It will show up only once.

If you click on one of the projects, you will go to a new screen with a gallery, this will show all of the rows with that selected Order number. This all works great, except it not being able to load these 8000 rows & distinct it on the order number. I honestly don't know what to do right now. 

Highlighted
Super User III
Super User III

Re: ptioRe: Collection more than 2000 rows

Hi @TimoMigchielsen ,

I am not sure this will help, but keeping in mind my comment on planning data structure, if ever I need to get a set of unique values from a big list, I maintain a separate reference list and test on every new and edited record save function if it already exists in the list and if not Patch the list with the value.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Highlighted
Post Prodigy
Post Prodigy

Re: ptioRe: Collection more than 2000 rows

Thank you for your answer @WarrenBelz 

This sadly wont do the job for the project I need this for. I am having a gallery that is showing all of the projects. If they click on a project, it shows all of the details for that selected project. They can not add/edit it, only view it. 

 

I have been searching a lot of information about what I want exactly but I do not think it is possible sadly. The only thing I could come up with is adding an extra column using Power Automate and do number++ for every row. So I am going to look into that now.

Highlighted
Super User III
Super User III

Re: ptioRe: Collection more than 2000 rows

@TimoMigchielsen ,

Then create the list yourself the “low tech” way.

  1. Export the list from SharePoint to Excel
  2. Use Data > Remove Duplicates (I think that is what it is called - not on PC presently)
  3. Import result into new reference list.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,593)