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

[Steelman70]

Here is an example:

Collect (MySource, {Field1: 123, Field2: "abc"}, {Field1: 456, Field2: "def"}, {Field1: 789, Field2: "geh"})

Where MySource is the data source and Field1 and Field2 are the two data fields of the source.

The code above creates 3 new records in one write operation.

[hpkeong]

Hi

Thanks. But I thought this is always possible with Collect for multiple records.

May i know, as Dang also mentioned, that we can, not only collect multiple records, but also SAVE/PATCH mutiple records into Excel / CDM?

(Though I seldom collect many records at A TIME!)

(Yes, my existing method is AutoSaving using iteration).

Multpile Records Collect

[Steelman70]

Hi hpkoeng, you can save multiple new records in one operation to CDM and Azure at least for sure with the Collect function.  I am not sure for Excel but you should try.

In regards to patching (i.e. updating) multiple records in one operation, then this can be achieved with UpdateIf, please let me know if you need a detailed example.

[hpkeong]

HI

That's a real new things for me. I will make it a try.

Thanks a lot.

(Guess: Excel Environment is different from Data Container, like CDM)

[Steelman70]

Hi again, actually I already had an example here:

https://powerusers.microsoft.com/t5/PowerApps-Forum/Editing-multiple-records-in-Gallery-control/m-p/...

[hpkeong]

Hi SteelMan and Dang:

Great things, esp. SteelMan.

As Dang said, it was not updated in the latest release.

Luckliy we had you trying out because of your experince in other data storage container.

So, Collect can Replace PATCH in this case!

Yes, ClearCollect(MyX, Record1, Records, ...............): Collect(Entity, My X)....works in BOTH CDM and EXCEL.

CDM - Almost Instant (0.5 - 1 sec)

Excel (Dropbox) - Depending on Internet connection to Dropbox (2-4 sec).

Thanks a lot, and we shall try out more options.

If PG is looking at this, then please clarify in the RELEASE NOTES.

[mr-dang · Accepted solution]

I ran some tests now that I know that Collect can batch-save to datasource. I think I may have found footsteps leading to the answer you're looking for, @Steelman70.

My original data source was an Excel Spreadsheet. All the formulas previously used Patch. My goal was to switch over to CDM and use Collect instead of Patch. Here are my steps:

1. I changed instances of Patch to Collect:
   

   Patch(datasource,Defaults(datasource),{})
   
   changed to:
   
   Collect(temp,{},{},{});
   Collect(datasource,temp)

2. I removed my old Excel datasource and added my CDM datasource of the same name. I assumed that if the name stays the same, all the formulas will be intact.'
3. The CDM datasource appeared as "datasource_1."

I thought this was a bug, but it happened because all my formulas involving Collect think they are now pointing to a temporary collection. In order to add my new CDM datasource with the same name as my old Excel datasource, I need to remove all instances of the Collect(datasource) formula.

If I understand your original problem correctly--it may be that PowerApps is momentarily misunderstanding the name of your datasource and unsure of its identity as a Connection vs. temporary collection.

It's a chicken or egg problem: did the name of the datasource first appear as a connected datasource, or did the Collect formula come first?

[Steelman70]

Hi Mr Dang, indeed this is the issue.  I wonder if the program team can somehow fix it, but it is difficult to see how.  Maybe Collect should only work with collections and there should be a separate function for data sources.  It is very annoying when this happens because you need to edit all Collects and then re-link the data source and then re-edit all the Collects back again.

Also I think I was having issues because my data sources require being in quotation marks because they contain non-alphanumeric characters and the autocomplete sometimes provides the quotation marks and sometimes not.

Many thanks for following up.

[NormanYuits]

Hi Guys, 
I'm faced with the following issue using Collect (Entity, Temp) function working with CDM entity.

Consider that i have an entity with a "Title" field of NumberSequence type (so it should be incremented everytime i insert new record).

On the Temp collection, i have 2 records within 2 fields ({Field1: A1, Field2: A2}, {Field1: B1, Field2: B2})
Entity has 3 feilds (Title, Field1, Field2).

When i use Collect(Entity, Temp) it inserts only first record!! Whether it's a bug or i missing something? And could you please advice is it possible to instert Temp records to the Entity in another way?

[Steelman70]

Hi Norman, this could be a bug.  However, are you sure that Temp really contains the two records at the time of Collect?

I am sure you know, but you can check this by looking into the collection via the Content -> Collections menu in PowerApps.

I have found the Title field in CDM to be buggy so far.  Unfortunately apart from Collect I know of no other way to write multiple new records to a source.  Obviously you can always Patch one at a time, but this is a bad workaround, the Collect function should work.

Please report the bug if it is still a problem.