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

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 II
Advocate II

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 II
Advocate II

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 II
Advocate II

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

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,637)