how to patch MSSQL with ID

[botonakis]

Good morning all,

we have a powerapp button that has the below

Patch(event_log, {
ID: GUID(),
Serial_No : SerialNumberTextInput.Text,
Status_Event: "new"}

the event_log table is set to have ID column as uniqueidentifier and always set newid()

when running the powerapp there is an error that the new id could not be found

Isn't patch there to create rows as well, or are we doing something wrong?

thank you in advance

[timl · Accepted solution]

@botonakis 

If you want to create a new record in event_log by calling Patch, the syntax would look like this:

Patch(event_log, 
  Defaults(event_log),
  {
    ID: GUID(),
    Serial_No : SerialNumberTextInput.Text,
    Status_Event: "new"
  }
)

[botonakis]

Morning @timl 

thank you for this

Now i am getting "bad gateway" error

Most of the searches suggest i remove the ID from the script, but if i do so and try again

I get another error "ID required"?

any ideas?

Thank you

[timl]

Hi @botonakis 

I'm not exactly sure what the problem is at this point. If I have some time later, I'll see if I can recreate this.

One thing I would try is to close and reopen your app in the designer, as it's possible that the 'bad gateway' error is an unconnected issue, that might help clear the problem.

[botonakis]

Good morning @timl

I just tried again today. no luck

do you think it has to do with SQL to automatically add ID ?

Maybe patch is not the correct function?

Thanks

[timl]

Hi @botonakis 

I've just created a test table with a GUID id column with a default value of newid().

When I call the formula beneath from Power Apps, it successfully adds a new record into the table. I'm pretty certain this syntax should work, so the challenge is to find out why it doesn't work in your specific case. Just to confirm, the error that you get when you supply id:GUID() is "bad gateway"?

[botonakis]

Good morning @timl ,

this is strange.

Should I mention that when running the test, there is no error

Of course the SQL will not update

and when i close the test of the app, i will notice a formula error on the button

when i hover over the error it will come up with 

if i remove the id completely, then it will fail and the error will be replaced with another one, "ID required"

Is your SQL updating? no errors if you exit the test?

again, the script i write is

Patch(event_log, Defaults(event_log), {ID : GUID(), Serial_No : SerialNumberTextInput.Text, Status_Event : "new", TimeStamp : Now()});

I have tried to put the patch script at the beginning , middle or end of the updateif

Any other similar function maybe?

Thank you again

[timl]

Hi @botonakis 

If I edit the text, the call to Patch still works for me.

Just to clarify your other comment, where do you call UpdateIf in your formula?

>>I have tried to put the patch script at the beginning , middle or end of the updateif

[botonakis]

Good morning @timl 

I have been trying to put the Patch at the beginning, middle and end of the two updateif scripts as well

It still errors. If i remove the patch , the other two updateif work fine

I think PowerApps do not patch the ID unless it is int and not uniqueidentifier

Thank you anyway