cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ryosuke_I
New Member

How to delete the record from Azure SQL?

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

mydatabase.PNG

 

 

 

 

 

 

 

 

 

 

 

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?

 

 

 

5 REPLIES 5
Ryosuke_I
New Member

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?

 

 

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

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?

 

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
New Member

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"

 

Roach_K9_0-1652271450964.png

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,869)