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?

[KrunalPatel]

Hi @Meneghino,

Thanks for the reply. This has worked for me also.

I was trying it with the Patch so it was not working.

[mr-dang]

Here's my rough understanding of how to bulk Patch using ForAll:

ForAll(RenameColumns(collection,"PrimaryId","PID"),
	If(IsBlank(PID),
		Patch(datasource,Defaults(datasource),
			{field1: field1,
				field2: field2,
				field3: field3
			}
		),
		
		Patch(datasource,First(Filter(datasource,PrimaryId=PID)),
			First(Filter(collection,PrimaryId=PID))
		)
	)
)

This method is only useful if your device is too slow to handle Patching one record at a time and you would prefer to batch all at once. First, you'll need to Collect() changes that you make to the datasource into a temporary collection--which I call 'collection.' This may also include new data you would like to add. Be sure to use a condition so that it does not Collect() the record more than once.

Then ForAll() will perform actions for each record in 'collection.' If the record is not in the datasource yet (IsBlank(PID)), the formula will write it to a new row, but you may need to determine the data to be recorded for each field. If the record is already in the datasource, it will replace it with whatever changes you collected into 'collection.'

I have not been using Collect() because it's hard to gauge if a record lines up with the original datasource to successfully write. ForAll() can run concurrent Patches so it is not terribly slow. It also has more flexibility in how it is written.

[Anonymous]

I'm trying to apply your logic to a problem I've having, but not having much luck.

Maybe it's not the same problem and deserves a question of its own;

I am collecting items in the app with forms and local collection

I then want to update a datasource (SharePoint list) with some of the columns in the collection.

Notes:

The collection does not have the same column names as the data source

There are lookup fields in the datasource that I am not updating

All the items in the collection will be new records in the datasource

I figured I could do something like this;

ForAll(CollectionGalleryList.AllItems, Patch(Source, Defaults(Source), {SOURCEColumn: CollectionColumn.Value, SOURCEColumn1: CollectionColumn1.Value,SOURCEColumn2: CollectionColumn2.Value.....}))

But I'm getting an error across the entire line and I don't get it;

Invalid argument type (Table). Expecting a Record value instead. The function 'Patch' has some invalid arguments.

 [Edit: I've since renamed all the collection columns that I'm patching to match those of the source and have tried to Collect(Source, Collection) but only a few fields are actually updated]

[mr-dang]

Hi @Anonymous

Below is your formula:

ForAll(CollectionGalleryList.AllItems, 
	Patch(Source, Defaults(Source), 
		{SOURCEColumn: CollectionColumn.Value, 
			SOURCEColumn1: CollectionColumn1.Value,
			SOURCEColumn2: CollectionColumn2.Value.....
		}
	)
)

I think it is not working because "CollectionColumn.Value" points to the entire "Value" column in the datasource. If you want it to save the respective value within that column, just put the name of the column from CollectionGalleryList there. No parent/child name or period needed.

[Anonymous]

Thanks mr-dang,

Busy trying it out, but battling - might've helped if I hadn't gone and aligned my collection column names to my list column names, now I'm getting confused

So now it keeps telling me columns don't exist...I notice that my data scope for the patch source (the SharePoint list) isn't pulling through when I type the formula, only the collectionlist columns are available to the formula....this might be a problem....

My first column in the SharePoint list is "Title", (usual default SharePoint list column) and the formula seems happy with that but then the next one gets redlined...

ForAll(CollectionLists.AllItems, 
Patch('SharePoint List', Defaults('SharePoint List'), 
       {Title: Title, 
         Next_0x0020_Column: Next_0x0020_Column
        }
        )
)

It tells me the specified column (Next_0x0020_Column) does not exist.  It certainly does, but I'm guessing I've messed the scope up or something...

It's nearly 12am here, my brain is hurting...going to go sleep and tackle it in the morning again 🙂

Kind regards

R

[mr-dang]

---

@Anonymous wrote:

 [Edit: I've since renamed all the collection columns that I'm patching to match those of the source and have tried to Collect(Source, Collection) but only a few fields are actually updated]

---

This will sound strange, but I suggest keeping the columns with different names so that PowerApps can disambiguate which datasource and which column you mean exactly. It might be confusing the Title column that is used in each source.

ForAll(CollectionLists.AllItems, 
	Patch('SharePoint List', Defaults('SharePoint List'), 
		{Title: [Corresponding column in your collection], 
			Next_0x0020_Column: [Corresponding column in your collection]
		}
	)
)

If this does not resolve, then perhaps it could be that you can't use a Gallery as the argument for your datasource (red). I usually set that to a temporary collection, not the gallery housing it.

[Anonymous]

Seems to have worked!

It also helped to know I was mistyping the space code "_x0020_" as "_0x0020_" which is why it didn't find the columns....  

Thanks mr_dang - So what's the best way here - should you create a 'blank' collection of columns to use for speedy input collection and referencing during runtime and just collect it back into the source?

i.e. hide some command to create the collection somewhere

Collect(shellCollection, Filter(Source, 1=2)) - just to create the shellCollection without data

Then

Use the collection as the source for input fields

Then

Collect(Source, shellCollection) when you're done?

[Mike8]

Hello,

I tried the examples above but I am doing something wrong and I cannot make it work.
Can someone please give me a simple example in order to understand.

If I have Collection1: ClearCollect(Collection1,{Letter:"A",Number:1},{Letter:"B",Number:2},{Letter:"C",Number:3})
And Collection 2: ClearCollect(Collection2,{SecondNumber:4},{SecondNumber:5},{SecondNumber:6})

How can I end up having Collection1 like this 
ClearCollect(Collection1,{Letter:"A",Number:4},{Letter:"B",Number:5},{Letter:"C",Number:6})
using the Patch and ForAll functions?
Need to replace the Number column of Collection1 to SecondNumber column of Collection2

Also maybe is a little different but if someone can also help me end up like this:
ClearCollect(Collection1,{Letter:"A",Number:1,SecondNumber:4},{Letter:"B",Number:2,SecondNumber:5},{Letter:"C",SecondNumber:6}).
Need to add the SecondNumber column of Collection2 to the Collection1. Collection1 had two columns and I would like them to be three.

Thank you

[Meneghino]

Hi @Mike8

Here is what I think you need, which should address both your questions:

ClearCollect(Collection1,{Letter:"A",Number:1},{Letter:"B",Number:2},{Letter:"C",Number:3});
ClearCollect(Collection2,{SecondNumber:4},{SecondNumber:5},{SecondNumber:6});
ClearCollect(Collection3, AddColumns(Collection1, "New_column", Last(FirstN(Collection2,Number)).SecondNumber))

[Mike8]

Hi,

Yes. This solves the second part.
Thank you @Meneghino. 🙂