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.
Solved! Go to Solution.
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.
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
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:
Bryan
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..
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
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.
@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?
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)
}
)
);
User | Count |
---|---|
159 | |
97 | |
82 | |
75 | |
59 |
User | Count |
---|---|
195 | |
176 | |
103 | |
94 | |
86 |