Patch a Collection

[mr-dang]

The release notes for 2.0.531 indicates an improvement to the Patch function that I'm excited about: 

7. Update multiple records at once by using the Patch function.

Create or update more than one record at a time by specifying a table of base records and 
a table of change records as arguments for the Patch function.

I think this is what I've been waiting for, but I'm not sure how to use it. 

Can I save a Collection with identical fields back to the datasource? For instance, can I Patch the rows in Collection1 into DataSource1 if they have identical columns? I've been trying:

Patch(DataSource1,Collection1)

but that does not result in any new rows written. What am I doing wrong?

EDIT: could it be that the default fields (Title, CreatedOnDateTime, etc.) are blank in the Collection, so it cannot be Patched with those blank?

[hpkeong]

Hi

Me, too, trying out several method but in vain.

Reading the blog..

https://powerapps.microsoft.com/en-us/blog/performance-refresh-forall-multiple-field-lookups-531/

... Performance, Refresh button, ForAll, and multiple field lookups

Finally, one of the reasons you gave us for needing iteration was the bulk update of data.  And ForAll can be used for that.  But as we think it may be common, we also added the ability to use the Patch function on a set of records instead of just modifying one record at a time....

Looks like ForALL and PATCH a set of records may have to work concurrently..??

[rgruian]

You can use:

    Patch(datasource, Collection1)

however you do need to have the right keys in the collection, so that Patch can find the proper records to patch. For example, if the primary key of your datasource is "Title", each row in Collection1 has to have a value for the "Title" field. The rest of the fields in the collection specify new values to be patched. The rows in Collection1 that do not have values for "Title" will be filtered out and will not contribute to the multi-patch operation.

Re. ForAll and Patch -- yes, these are concurrent functions, and they will not rely on, or guarantee, any specific order of operations. They are designed for parallel data processing.

I hope this helps.

[mr-dang]

@rgruian

If I use a single Patch, my Title field, which is my key, can be autogenerated as the next number in the sequence. It's like Max(Title)+1, but it solves for duplicates when two computers Patch at the same time.

In my setup, I might be able to use ForAll to make a temporary entry in the Title field for the rows I want to Patch, then Patch multiple rows.

Refresh(datasource);

Patch(datasource,
ForAll(Collection1,"datasourcename" & Text(Max(datasource,Value(Right(Title,10)))+1),"0000000000")
)

But there is still the possibility for duplicates since two computers could refresh at the same time and identify the same Max value. I am also unsure if the ForAll calculation can put its result in the Title column of Collection1.

Would it be possible to Patch multiple rows of Collection1 to empty rows in the original datasource--and at the same time autogenerate new Title or ID values that are next in the sequence without duplicates?

[rgruian]

I am not sure I fully understand. Are you looking to add multiple new rows to your datasource? If that is the case, you should use Collect instead of Patch:

    Collect(datasource, Collection1)

Since you were using Patch though, I was assuming that you needed to replace existing rows in your datasource. Perhaps I misunderstood?

Patch has multiple overloads. You can use one of the following overloads to achieve what you need:

    Patch(DS, table_of_keys_and_field_updates)

    Patch(DS, table_of_keys, tables_of_field_updates)

    Patch(DS, record_of_key_and_field_updates)

    Patch(DS, row_with_key, record_of_updates, record_of_updates, ...)

    Patch(record, record, ...)

I hope this helps.

[mr-dang]

Collect works, but I think the problem I had last time was that PA became unsure if the datasource in Collect(datasource,Collection1) is a temporary collection or a connected datasource. So if I were to remove the datasource from PowerApps, all the formulas would continue to work, but they would point to a temporary collection. I have not checked to see if this is still the case. 

[hpkeong]

---

@rgruian wrote:

You can use:

    Patch(datasource, Collection1)

however you do need to have the right keys in the collection, so that Patch can find the proper records to patch. For example, if the primary key of your datasource is "Title", each row in Collection1 has to have a value for the "Title" field. The rest of the fields in the collection specify new values to be patched. The rows in Collection1 that do not have values for "Title" will be filtered out and will not contribute to the multi-patch operation.

Re. ForAll and Patch -- yes, these are concurrent functions, and they will not rely on, or guarantee, any specific order of operations. They are designed for parallel data processing.

I hope this helps.

---

Hi

Thanks for the explanation and I am trying out the first option: Multiple New Records, using

- Collect, &

- Patch

Anyway, I am unclear of the following in TUTORIAL

Patch function in PowerApps

Modify or create a set of records in a data source

Patch( DataSource, BaseRecordsTable, ChageRecordTable1, [, ChangeRecordTable2, … ] ),

But, with your:

Patch(DS, Collection1), it Works.

Just wonder of the Differences  in tutorial and yours!

 Multiple New Records

[hpkeong]

Hi

Thanks for the explanation and I am trying out the first option: Multiple New Records, using

- Collect, &

- Patch

Anyway, I am unclear of the following in TUTORIAL

Patch function in PowerApps

Modify or create a set of records in a data source

Patch( DataSource, BaseRecordsTable, ChageRecordTable1, [, ChangeRecordTable2, … ] ),

But, with your:

Patch(DS, Collection1), it Works.

Just wonder of the Differences  in tutorial and yours!

---

@rgruian wrote:

You can use:

    Patch(datasource, Collection1)

however you do need to have the right keys in the collection, so that Patch can find the proper records to patch. For example, if the primary key of your datasource is "Title", each row in Collection1 has to have a value for the "Title" field. The rest of the fields in the collection specify new values to be patched. The rows in Collection1 that do not have values for "Title" will be filtered out and will not contribute to the multi-patch operation.

Re. ForAll and Patch -- yes, these are concurrent functions, and they will not rely on, or guarantee, any specific order of operations. They are designed for parallel data processing.

I hope this helps.

---

[hpkeong]

Hi

Now, I have a gallery and I wish to filter those RECORDS under a category to be UPDATED at a Batch, and then Patch back, without changing the KEY fields (say First Column). Is that possible?

I try using:

Patch(DS, BaseRecorsTable, ...)... what is your advice?

[My experience with School or even Factory Data where BATCH updating is faster:

e.g.: Attendance List : Present or Absent using CheckBox for a CLASS (say 40 students]

Use TextInput to append "Wish-Records" for updating in BATCH... Possible? If yes, how can I write Patch(...)

[rgruian]

I am assuming that the items in Gallery1 are all editable, e.g. via text input controls?

If so, please try the following:

Dropdown1.OnSelect = ClearCollect(SourceBook, Filter(TableBook, Language = Dropdown1.Selected.Value))

Gallery1.Items = SourceBook

Button2.OnSelect = Patch(TableBook, SourceBook, ForAll(Gallery1.AllItems, { Book: TextInput1.Text, Author: TextInput2.Text, Language: TextInput3.Text }))

Basically the idea is that you need to project the gallery items (which hold the state for each row after your manual changes) to a new table of {Book, Author, Language}. You can use ForAll to achieve that. You can also use AddColumns and ShowColumns. And there are other ways to do it too.

I hope this helps.