Collect with data source, not with collection

[Steelman70]

Hi, I think the only way to create a batch of records at once in a data source (not a collection) is via the Collect function.  The Patch function with Defaults only creates one record at a time, I believe.

Now, the issue is that when I use Collect with a data source, sometimes the data source disappears from the app* and a collection with the same name instead appears.

Has anyone else come across this?

* meaning that it disappears from the list of connected data sources

[hpkeong]

Hi

Though not really clear of your scenario, but I remembered it happened to me before while collect and patching (autosave) a Batch of data into excel. Sometime it happened and sometimes not.

After hours of trial and error, I found that data filtering prior to batch patching confused and then load certain data from collection into gallery. In my case, Clear( ) was used at the end of Auto-Batch-Saving clear my issues.

Note: The sequence of the syntax when OnSelect really matters. So, OnSelect.Collect(.....); Patch (....): Clear(....) make things differently.

Hope I get your points.

Do refer my my Useful Features of PowerApps on AutoSaving (In fact, I have modified several version but not all are shared in the forum. They are actual example but can be further improved!)

Have a nice day.

[mr-dang]

+1 to hpkeong's recommendation. It seems to be the best way to queue up individual records to patch back to the datasource.

I've used it in nearly all the apps I've made. Ideally, saving to collection and having it all copy pasted into the datasource in one step natively would be the fastest solution though.

[Steelman70 · Accepted solution]

Thank you hpkeong and mrdang for the feedback.

I have done more testing and now Collect to a data source does not give me the issues it did earlier, frankly I am not sure why.

In any case, now that Collect is working properly, I tried both approaches to creating 3 new records:

1) Patch default a series of records

OnSelect = Patch(MySource, Defaults (MySource), [NewRecord1]); Patch(MySource, Defaults (MySource), [NewRecord2]); Patch(MySource, Defaults (MySource), [NewRecord3])

2) Create a collection of the new records then Collect to data source

OnSelect = ClearCollect(MyChanges, [Record1], [Record2], [Record3]); Collect(MySource, MyChanges)

I found performance quicker with the second approach.  This seems to because PowerApps seemed to be doing only one write operation instead of three.

@hpkeong, could you please clarify what your "Auto-Batch-Saving" approach look like in the case of 3 new records to be added to a data source?

Many thanks,

[hpkeong]

Hi

My idea of Auto-Saving in batch was mainly due to customers' request (To Save/Patch every single record, it is too slow with a lapse of about 3-5 seconds in Excel/DropBox environment. Reason: my country's internet connection is SLOW..:(...)

So, the idea of Auto-Saving in Batch came.

This is my link (I have made correction to this but no time to share) for your reference.

https://powerusers.microsoft.com/t5/PowerApps-Forum/Useful-Features-of-PowerApps-12-AutoSaving-of-Ba...

My idea is:

1. Collect (....) for every entry, as many as you wish [reason: Collect is INSTANT]

- Collect is fast to collect all our data

2. Include a Timer, and Set OnTimerEnd, the action of Filter the Collection into a SINGLE RECORD into gallery, so that PATCH can execute to save (one at a time, and iterated until the end of total records usign CountRows).

I am not really clear if what I have accomplish (which meets mine as well as Mr. Dang's) will also meet your needs.

Hope it helps.

I have tried, i would say, hundred of times, never fail and can really sit down relaxly for the Patch to execute.

NB: Thanks to Mr. Dang (USA) and Taufik (Indonesia) for comment and correct my Auto-Saving Syntax couple of months ago.

[Steelman70]

Hi hpkeong, thanks for clarifying, I think it is clear what you have achieved.

If I understand correctly, then you are executing a series of patches in the background one record at a time.

I also saw Mr Dang's use of the "oper" variable for adding (Patch defaults) or editing (UpdateIf) records.

In my experience with Access Web App as a data source, I find that Collect actually performs a batch write operation in the background.  But maybe this is not the case with Excel/DropBox.  In any case I use the first part of your solution (load changes to a collection, which as you say is instant) but then I use Collect to do a batch write operation for new records or Updateif for existing records.

[hpkeong]

Hi Steelman:

Thanks for sharing with me/us your ideas, too.

I am glad and indebted to all of you for many ideas / concepts.

Have a nice day.

[mr-dang]

Confirmed! Collect can write multiple records to a data source*. HOLY MOLY THAT'S BIG NEWS! How did this not make it into any of the release notes?

*I've only tested with CDM so far

[Steelman70]

Hi MrDang, it also works with Azure DB (Access Web Apps)

[hpkeong]

Hi Steelman & Dang:

I am unclear of Collect can write (save?) into DataSource in Batch!

Maybe I am outdated.

More clarification please!