Sql Server: how to create new row with SubmitForm from existing row

[jurix]

I have PowerApp based on Sql Server table. 

When I edit and submit a row, I always want to create new version (new row in database). How to do that since current functionality is using SubmitForm? I thought that I should set the ID to 0 but maybe I somehow need to create new context row with data copied from existing on opening row for edit?

[aadilganie · Accepted solution]

Hi @jurix

When using the submitForm command it will only create a new row if the form mode is new (NewForm()). If you would like to create a new row from a form that is in EditForm mode I would recommend using Patch with the defaults() command or the Collect command.

Hope it helps..

All the best!

[v-micsh-msft]

Hi @jurix,

just as shared by @aadilganie, the form control will only creating new record when the form mode is set to new, and when the mode is new, there is no item property available to hold the selected record.

You may check more details about how form works in PowerApps through the article below:

Understand form in PowerApps

DisplayForm and EditForm in PowerApps

So if here you would like to create new records when the Form control is under Edit mode, then it is recommended to take use of Patch () function, to replace the SubmitForm() function.

So basically the formula under the submit button should be:

Patch(SQL-Table, defaults(SQL-Table), {Field:Gallery.Selected.Field})

It is recommended to match the field seperately, if the ID is auto-generated, then the formula cuold be switched as :

Patch(SQL-Table, defaults(SQL-Table), Gallery.Selected.Field)

Regards,

Michael

[jurix]

Thanks, I have successfully used the Patch together with Errors function and validating if Patch was successfull. 
If successful then Navigate to initial screen.

I also needed to update the edit form validation:

1: to not submit the patch when form IsValid is false (for example when fields are empty)

2. to show red border if field.isValid is false.