cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TML
Advocate III
Advocate III

Using ForAll(collection) with Patch - what am I doing wrong?

I have successfully created a collection.  When the user clicks a button, I have the below code in the OnSelect section.  It tells me that The function 'Patch' has some invalid arguments.  If I remove the ForAll piece and use a label.Text instead of collection!field (I also tried collection.field), then the Patch function doesn't complain and a record is created.  But if I use ForAll and try to create multiple records, I cannot get it to work.  What am I doing wong?

ForAll(cResponses, Patch(
	'[dbo].[Items]', 
	Defaults('[dbo].[Items]'), 
	{Survey_Key: Value(lblNewSurveyKey.Text)}, 
	{SurveyResponse_Key: cResponses.Response_Key}, 
	{Survey_Status: rbStatus.Selected.Value}, 
	{Survey_Comments: txtTraining.Text}, 
	{Survey_CreatedBy: lblUserName_1.Text}, 
	{Survey_CreatedOn: Now()}, 
	{Survey_ModifiedBy: lblUserName_1.Text}, 
	{Survey_ModifiedOn: Now()}))
	

Any and all help is appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
TML
Advocate III
Advocate III

I just found the answer here: Solution

 

Basically, in the ForAll you do not reference the collection name, only the column name of the collection.  Here is the relevant text from the above post. In the code,

{ID: ID} is the correct syntax where only the column is referenced instead of

{ID: Collection.ID} 

 

1) The correct syntax is without reference to the table, my oversight, apologies.  To answer you question, you just use the column name and it takes the current value in the ForAll loop.

So the correct syntax would be this:

Patch(Datasource, {ID: ID}, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})

 

 Thanks to everyone who tried to get me going.  Hopefully this post will help someone in the future.

 

This is the corrected code from my original post:

ForAll(cResponses, Patch(
	'[dbo].[Items]', 
	Defaults('[dbo].[Items]'), 
	{Survey_Key: Value(lblNewSurveyKey.Text)}, 
	{SurveyResponse_Key: Response_Key}, 
	{Survey_Status: rbStatus.Selected.Value}, 
	{Survey_Comments: txtTraining.Text}, 
	{Survey_CreatedBy: lblUserName_1.Text}, 
	{Survey_CreatedOn: Now()}, 
	{Survey_ModifiedBy: lblUserName_1.Text}, 
	{Survey_ModifiedOn: Now()}))

 

View solution in original post

11 REPLIES 11
HolmesLL
Frequent Visitor

Try Collect([dbo].[Items],cResponses)

I have already created the collection elsewhere in code... now I am trying to use the collection to fill in a field when creating a record in the database.  I want to create a record in the database for every record in the collection.

JRaasumaa
Memorable Member
Memorable Member


@TML wrote:

I have successfully created a collection.  When the user clicks a button, I have the below code in the OnSelect section.  It tells me that The function 'Patch' has some invalid arguments.  If I remove the ForAll piece and use a label.Text instead of collection!field (I also tried collection.field), then the Patch function doesn't complain and a record is created.  But if I use ForAll and try to create multiple records, I cannot get it to work.  What am I doing wong?

ForAll(cResponses, Patch(
	'[dbo].[Items]', 
	Defaults('[dbo].[Items]'), 
	{Survey_Key: Value(lblNewSurveyKey.Text)}, 
	{SurveyResponse_Key: cResponses.Response_Key}, 
	{Survey_Status: rbStatus.Selected.Value}, 
	{Survey_Comments: txtTraining.Text}, 
	{Survey_CreatedBy: lblUserName_1.Text}, 
	{Survey_CreatedOn: Now()}, 
	{Survey_ModifiedBy: lblUserName_1.Text}, 
	{Survey_ModifiedOn: Now()}))
	

Any and all help is appreciated.

 


 

Typically you don't need to close the curly brackets with each item to update.

Try:

 

edit: nevermind, this does the exact same thing in my testing...

 

ForAll(cResponses, Patch(
	'[dbo].[Items]', 
	Defaults('[dbo].[Items]'), 
	{Survey_Key: Value(lblNewSurveyKey.Text),
	SurveyResponse_Key: cResponses.Response_Key, 
	Survey_Status: rbStatus.Selected.Value, 
	Survey_Comments: txtTraining.Text, 
	Survey_CreatedBy: lblUserName_1.Text, 
	Survey_CreatedOn: Now(), 
	Survey_ModifiedBy: lblUserName_1.Text, 
	Survey_ModifiedOn: Now()}))

 

 

I get a red squiggly line on the collection (cResponses.Response_Key) and it says "The function Patch has some invalid areguments".  However, if I use the exact same ForAll(...Patch(... function and remove the reference to the collection and replace it with a text box or label, then it works just fine.

TML
Advocate III
Advocate III

Acutally I get more info if I hover over the specific column.  It says:

 

The type of this argument 'SurveyResponse_Key' does not match the expected type 'Number'. Found type 'Table'.

TML
Advocate III
Advocate III

I just found the answer here: Solution

 

Basically, in the ForAll you do not reference the collection name, only the column name of the collection.  Here is the relevant text from the above post. In the code,

{ID: ID} is the correct syntax where only the column is referenced instead of

{ID: Collection.ID} 

 

1) The correct syntax is without reference to the table, my oversight, apologies.  To answer you question, you just use the column name and it takes the current value in the ForAll loop.

So the correct syntax would be this:

Patch(Datasource, {ID: ID}, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})

 

 Thanks to everyone who tried to get me going.  Hopefully this post will help someone in the future.

 

This is the corrected code from my original post:

ForAll(cResponses, Patch(
	'[dbo].[Items]', 
	Defaults('[dbo].[Items]'), 
	{Survey_Key: Value(lblNewSurveyKey.Text)}, 
	{SurveyResponse_Key: Response_Key}, 
	{Survey_Status: rbStatus.Selected.Value}, 
	{Survey_Comments: txtTraining.Text}, 
	{Survey_CreatedBy: lblUserName_1.Text}, 
	{Survey_CreatedOn: Now()}, 
	{Survey_ModifiedBy: lblUserName_1.Text}, 
	{Survey_ModifiedOn: Now()}))

 

JRaasumaa
Memorable Member
Memorable Member


@TML wrote:

I just found the answer here: Solution

 

Basically, in the ForAll you do not reference the collection name, only the column name of the collection.  Here is the relevant text from the above post. In the code,

{ID: ID} is the correct syntax where only the column is referenced instead of

{ID: Collection.ID} 

 

1) The correct syntax is without reference to the table, my oversight, apologies.  To answer you question, you just use the column name and it takes the current value in the ForAll loop.

So the correct syntax would be this:

Patch(Datasource, {ID: ID}, {Column_A: Left(ReminderDates, 10), Column_B: Mid(ReminderDates, 12, 10), Column_C: Right(ReminderDates, 10)})

 

 Thanks to everyone who tried to get me going.  Hopefully this post will help someone in the future.

 

This is the corrected code from my original post:

ForAll(cResponses, Patch(
	'[dbo].[Items]', 
	Defaults('[dbo].[Items]'), 
	{Survey_Key: Value(lblNewSurveyKey.Text)}, 
	{SurveyResponse_Key: Response_Key}, 
	{Survey_Status: rbStatus.Selected.Value}, 
	{Survey_Comments: txtTraining.Text}, 
	{Survey_CreatedBy: lblUserName_1.Text}, 
	{Survey_CreatedOn: Now()}, 
	{Survey_ModifiedBy: lblUserName_1.Text}, 
	{Survey_ModifiedOn: Now()}))

 


Nice work figuring this out and thanks for sharing! I've been patching only filtered gallerys and it's very easy to accomplish. I may try patching just the collection now using this info.

Thank you for the post, helped me a ton. the {ID : ID} is really unsettling but seems to have done the trick!

PHarrison
Frequent Visitor

You could do {ID:ThisRecord.ID} which might be more readable...

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,854)