cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KrunalPatel
Level: Powered On

Re: Patch a Collection

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
Level 10

Re: Patch a Collection

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.

Microsoft Employee
@8bitclassroom
Anonymous
Not applicable

Re: Patch a Collection

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
Level 10

Re: Patch a Collection

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.

 

Microsoft Employee
@8bitclassroom
Anonymous
Not applicable

Re: Patch a Collection

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 Smiley LOL

 

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
Level 10

Re: Patch a Collection


@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.

 

Microsoft Employee
@8bitclassroom
Anonymous
Not applicable

Re: Patch a Collection

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....   Man Embarassed

 

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
Level 10

Re: Patch a Collection

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
Level 10

Re: Patch a Collection

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
Level 10

Re: Patch a Collection

Hi,

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,937)