cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Meneghino
Level 10

Bug? Strange behaviour when Azure SQL DB table's primary key column is of date type

I created a table in an Azure SQL DB and set a date type column as they primary key.

There was no issue in connecting to the table and displaying the data.

However, patching new records did indeed add new records to the table, but returned an error in Studio (i.e. a warning that "The data returned by the service was invalid") however Errors (MyTable) was empty and after a refresh the data was shown correctly in the gallery.

Also, creating a delete button in the gallery with Remove(MyTable, ThisItem) did not do anything and did not give any errors.

It is very worrying that actions are not completing but no error is returned to the user.

 

Capture___.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Bug? Strange behaviour when Azure SQL DB table's primary key column is of date type

Hi Meneghino,

 

I have some updates for you.

 

It is a known limitation that only SQL Datetime works now. Date values do return blank.
It seems that PGs are working on it, but I don’t have a timeline on this issue.
As a temporary workaround, please use Datetime instead of Date.

 

I guess you have seen this thread, Staff @erga has posted a reply on this thread, please check it at here:
https://powerusers.microsoft.com/t5/PowerApps-Forum/Date-DateTime-SQL-AZURE/m-p/13842#M6053

 

And about your another issue about “Bug report: Patch of date column to Azure SQL DB works but does not return value to context variable”, I assume it should be the similar issue related to Date type column.
https://powerusers.microsoft.com/t5/PowerApps-Forum/Bug-report-Patch-of-date-column-to-Azure-SQL-DB-...

 

I will keep an eye on this issue, and back to you once I got any updates.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Bug? Strange behaviour when Azure SQL DB table's primary key column is of date type

Hi Meneghino,

 

May I suggest you configure the function looks like below?

Patch('[dbo].[a2]',Defaults('[dbo].[a2]'),{id:4,name:"zzz",birthday: DatePicker1.SelectedDate})

I test this code on my side, I works fine.

 

If this doesn’t meet your needs, please feel free correct me.

 

And about the Delete button, I guess you should configure the button as:

OnSelect= Remove('[dbo].[a2]',BrowseGallery1.Selected).

Then when you click the button, the selected item would be deleted.


Or on the gallery, with the Text box selected, configure the Text box control as:

OnSelect= Remove('[dbo].[a2]',ThisItem).

Then when you click the item, it would be deleted.

1.png

Hope this could be a reference for you.


Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Meneghino
Level 10

Re: Bug? Strange behaviour when Azure SQL DB table's primary key column is of date type

Hi @v-yamao-msft, thanks for following up.

I tried your first solution on patching dates and it does not work for me.

Here are the precise steps:

  1. Create a new table ([dbo].[MyDates]) in SQL Server with two columns:
    a) ID of int type, which is an identity (1,1) and primary index
    b) MyDateColumn of date type (NOT datetime type)
  2. In PowerApps create a gallery and set its items to [dbo].[MyDates]
  3. In the gallery's template have two text boxed with text ID and MyDateColumn
  4. Create a datepicker and two buttons, with the two buttons with OnSelect as:
    a) Patch('[dbo].[MyDates]',Defaults('[dbo].[MyDates]'),{MyDateColumn: DatePicker1.SelectedDate})
    b) Refresh('[dbo].[MyDates]')

Basically if you only press the first button it will add a new row to the gallery with the correct ID, but the date text box will be blank!

Only after pressing the refresh button will you see the date in the date text box.

Please see below a screen shot where I pressed save four times, then refresh then save three times again but no refresh.

 

PS If you now change the data type of MyDates to datetime type, then the correct date will be shown immediately after the save button is pressed.

 

 

__.JPG

 

Community Support Team
Community Support Team

Re: Bug? Strange behaviour when Azure SQL DB table's primary key column is of date type

Hi Meneghino,

 

Thanks for your detailed updates.

I can reproduce this issue on my side.


I have both Date and Datetime type column in my table. I use both SubmitForm and Patch to for a test.
But the result is that after I saved the data, only Datetime column has value, the Date column is blank.
Though I try to refresh the data source, the Date value is still blank.

1.png

 

I am going to report this issue from my side. Thanks for your feedback.
I will back once I got any response.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Bug? Strange behaviour when Azure SQL DB table's primary key column is of date type

Hi Meneghino,

 

I have some updates for you.

 

It is a known limitation that only SQL Datetime works now. Date values do return blank.
It seems that PGs are working on it, but I don’t have a timeline on this issue.
As a temporary workaround, please use Datetime instead of Date.

 

I guess you have seen this thread, Staff @erga has posted a reply on this thread, please check it at here:
https://powerusers.microsoft.com/t5/PowerApps-Forum/Date-DateTime-SQL-AZURE/m-p/13842#M6053

 

And about your another issue about “Bug report: Patch of date column to Azure SQL DB works but does not return value to context variable”, I assume it should be the similar issue related to Date type column.
https://powerusers.microsoft.com/t5/PowerApps-Forum/Bug-report-Patch-of-date-column-to-Azure-SQL-DB-...

 

I will keep an eye on this issue, and back to you once I got any updates.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (3,804)