cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gochix
Resolver I
Resolver I

ForAll Patch more than 2000 rows??

Hi,

I have a collection that contains more than 6000 rows of data. I store all info using clearcollect.

Question: How to ForAll Patch exceeding 2000 rows? 

Thought if it's possible to read more than 2000 rows using this formula :

ClearCollect(StoresList,Filter(CatalogueList,PartID <= 2000),Filter(CatalogueList,PartID >2000 && PartID <=4000),Filter(CatalogueList,PartID >4000 && PartID <=6000),Filter(CatalogueList,PartID >6000 && PartID <=8000));

 Then maybe it's also possible to somehow patch over 2000 rows?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
BCLS776
Super User
Super User

The code I shared above will create a new entry in the database table each time it runs -- the Defaults(TestUpdate) argument takes care of that. If you need code that updates existing records or creates new ones, try this:

ForAll(UpdateList As aRecord,
    With({aLookup: LookUp(TestUpdate, Title = aRecord.Title),
        If(IsBlank(aLookup),
            Patch(TestUpdate, Defaults(TestUpdate),
            {
                Title: aRecord.Title,  
                Description: aRecord.Description, 
                Quantity: Value(aRecord.Quantity) 
            }
            ),
            Patch(TestUpdate, aLookup,
            {
                Quantity: Value(aRecord.Quantity) 
            } // If a record exists, update the quantity only
            )
        )
    )
);

Note, you will need to clear your table or remove the duplicate records first.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

View solution in original post

13 REPLIES 13
BCLS776
Super User
Super User

Yes, once you have everything together in an internal collection, you should be able to ForAll-Patch the works into another datasource. Each Patch() operates as a separate API call, so you are not subject to the 2000 row limit for data coming back into the app. That said, a large collection could put you over your API call limits for the day. A good practice would be to do lots of exception handling & error checking within that ForAll so you know things are going well.

 

Hope that helps,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Thanks for a reply.

Can i fetch data even if this is a excel table in a sharepoint?

Idea is that every morning i would need to update just quantities for our stock at work.

 

I have a sharepoint list .

Using clearcollect i'm getting over 6000 rows of data.

ClearCollect(
    StoresList2,
    Filter(
        Update,
        Value(PartID) <= 2000
    ),
    Filter(
        Update,
        Value(PartID) > 2000 && Value(PartID) <= 4000
    ),
    Filter(
        Update,
        Value(PartID) > 4000 && Value(PartID) <= 6000
    ),
    Filter(
        Update,
        Value(PartID) > 6000 && Value(PartID) <= 8000
    )
);

But whenever i try to fetch the same data with same columns from Excel table in sharepoint list then i can't do it. Thing is sharepoint list i have indexed column that helps to avoid data delegation. but excel table doesn't allow to do it at least how far i know.

So is there no option to get this out from excel table?

 

It would be easier if i just update excel file and then i can ForAll patch to my sharepoint list rather to delete/create new list every morning, refresh connections and so on..

 

Any ideas? 

Yes, you can use an Excel table as a data source, but they are SLOW. 

 

Three thoughts for you to chew on:

  • If this job-to-be-done requires no decisions or analysis from the other, would it work easier to set up a Power Automate flow to do the work in the background for you? Watching an app load 6000 rows from an Excel table sounds...frustating
  • You don't have to re-create the Excel table and data connection if you carefully copy & paste-special values into the existing table without wrecking the table definition
  • Do you have a column you can Filter() on to reduce your data query size. When I read "update quantities for our stock" I think, "only change the values that are different than the ones from yesterday". That implies some sort of Filter() operation to find values that are changed and then do updates based on that. Would that work in your situation?

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

  • I did try to use power automate flow for this. But i got only 256 rows updated out of 6000. I've searched more info about this and sadly there is some limits..
  • Do you have a column you can Filter() on to reduce your data query size. When I read "update quantities for our stock" I think, "only change the values that are different than the ones from yesterday". That implies some sort of Filter() operation to find values that are changed and then do updates based on that. Would that work in your situation? - Every day we do stock updates so we can add something in stock and also take out. So i would need whole list update just for quantity..

What i really would have enough is to be able to make a collection from excel table, 6000+ rows, then patch them. Or any other patch solution..

Doesn't matter if i would need to wait 10 minutes for an update.. This will be only once a day every morning so it's fine..

Gochix
Resolver I
Resolver I

Sorry for a double post. I did try to increase threshold a bit to test it out.

After half an hour flow still didn't finish the job..

 

Gochix_0-1632481351856.png

 

My only idea is to somehow Patch the Sharepoint List from excel table.. If only i could get excel table inside collection that is over 2000 rows then it would do the job, no matter how long it would take..

So far tried everything and just can't get more than 2000 rows inside this excel collection.. It works fine to get 6000 rows if collection is from Sharepoint List, but not working with Excel.. meh

You do not have to load the list into an internal collection to Patch() from one data source to another. In fact, that doubles the API calls and work the app must do. Connect both the Excel table and the Sharepoint list to your app and Patch() from one to the other.

 

That said, to work with this many rows and a slow Excel data source, this is best left to a Power Automate flow that can start at a scheduled time each day and ideally complete before anyone needs it in the morning. The flow can also move data directly from the Excel file to the Sharepoint list using an Apply to All block.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

@BCLS776 ,

Hi. So how far i've got is i made an excel table that will show me only changes that has been made. (formulas in excel helped to sort this out)

I've collected a collection in app. Removed all blanks if has any. And now trying to patch with existing list.

All the time this patch is duplicating the rows. I can't get my head around why it's not just updating row. If my title is the same, everything is the same. Just quantity changes but data anyway duplicates..

 

 

Gochix_1-1632511242286.png

 

 

ForAll(UpdateList,Patch(TestUpdate,{Title: Title,  Description: Description, Quantity: Value(Quantity) }));

 

 

 

Is this the right way of updating a list?

BCLS776
Super User
Super User

Try this instead - Patch() within ForAll() needs some help to handle scope:

ForAll(UpdateList As aRecord,
    Patch(TestUpdate, Defaults(TestUpdate),
    {
        Title: aRecord.Title,  
        Description: aRecord.Description, 
        Quantity: Value(aRecord.Quantity) 
    }
    )
);
_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

@BCLS776 ,

Thanks for try, but still gives me duplicates..

Gochix_0-1632513184284.png

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,070)