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

[TML]

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.

[HolmesLL]

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

[TML]

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]

---

@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()}))

[TML]

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]

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 · Accepted solution]

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]

---

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

[Westly]

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

[PHarrison]

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