ForAll Patch more than 2000 rows??

[Gochix]

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.

[BCLS776]

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

[Gochix]

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? 

[BCLS776]

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

[Gochix]

• 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]

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..

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

[BCLS776]

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.

[Gochix]

@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..

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

Is this the right way of updating a list?

[BCLS776]

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) 
    }
    )
);

[Gochix]

@BCLS776 ,

Thanks for try, but still gives me duplicates..