How to delete the record from Azure SQL?

[Ryosuke_I]

Hello.

I can add the record in Azure SQL database via Power App. But I can't delete the record via Power App.

I'm using default sample app to use SQL Server.

My data base is...

Adding record code is...

SubmitForm(EditForm1)

After this code execution, I can find the new record on MS SQL Server Management Studio(SSMS).

delete record code is...

Remove('[dbo].[mydatabase]', BrowseGallery1.Selected)

If I execute this remove code, apps respond...

"Can't find specified record.

Server Respond: ID '1481814000000' does not exist."

And I can find this record on SSMS...

How can I remove the record in SQL database via app?

[Ryosuke_I]

Update:

2016-12-15T15:00:00.000 on SQL datetime data should be "3690889200000"

Because, datetime is started from 1900-01-01T00:00:00.000 and tick time is 1msec.

However, in my analyzing, on PowerApp, this start datetime maybe 1970-01-01T00:00:00.000

Then, 2016-12-15T15:00:00.000 on Power App datetime data is "1481814000000"

So, if PK is datetime on SQL database, Power App can't find record...

Can someone give advice to delete record from SQL database via Power App?

[Meneghino]

Hi @Ryosuke_I

You can remove a record by using its ID (or any other field which is your primary ID) like this:

Remove('[dbo].[mydatabase]', {ID: BrowseGallery1.Selected.ID})

This way you do not worry about the other fields

If you only have your UpdateDateTime field, then you can transform it like this:

Remove('[dbo].[mydatabase]', {UpdateDateTime: BrowseGallery1.Selected.UpdateDateTime + X})

where X is the difference in datetime calculation

[Ryosuke_I]

Hi @Meneghino

Thank you very much for your advice.

However, I can't solve my issue...

I tried some pattern for test.

1. PK is datetime "UpdateTimeDate" on database

2. PK is nvarchar(50) "RoomName" on database

Record Del Code for case 1-a.

RemoveIf('[dbo].[mydatabase]', UpdateDateTime=datetimevalue("2016/12/31 00:00:00.000")); If (IsEmpty(Errors('[dbo].[mydatabase]', BrowseGallery1.Selected)), Back())

Record Del Code for case 1-b.

RemoveIf('[dbo].[mydatabase]', RoomName="test01"); If (IsEmpty(Errors('[dbo].[mydatabase]', BrowseGallery1.Selected)), Back())

Record Del Code for case 2-a.

RemoveIf('[dbo].[mydatabase]', UpdateDateTime=datetimevalue("2016/12/31 00:00:00.000")); If (IsEmpty(Errors('[dbo].[mydatabase]', BrowseGallery1.Selected)), Back())

Record Del Code for case 2-b.

RemoveIf('[dbo].[mydatabase]', RoomName="test01"); If (IsEmpty(Errors('[dbo].[mydatabase]', BrowseGallery1.Selected)), Back())

Result of case 1-a and -b.

Both NG...

Result of case 2-a and -b.

Both OK!

So, in my understanding, PowerApps has bug for "datetime" handling if Azure SQL database PK data type is "datetime".

Can you or MS PowerApps team check this issue?

[Meneghino]

hi @Ryosuke_I

Yes, I have had many problems with date and datetime fields in PowerApps and the product team seems to be aware of it.  They are making regular improvements but nothing major so far.

I usually avoid them by using an integer field for dates like this yyyymmdd and then parsing to a date if and when I need it.

I have not needed the time portion but of course you can add that too.

It's a workaround but it works.

[Roach_K9]

You can delete the data by passing "Blank()"

Above solution will only work after you enable "Formula-level error management" setting under: setting > Upcoming features > Experimental

For Data Type "datetime"