Azure SQL Database is a very powerful tool, but currently there are limitations to its use in conjunction with PowerApps. I would appreciate any feedback on this list so that it can be kept up to date. Please note that all work-arounds are temporary, since sooner or later these limitations should be resolved by Microsoft.
- Date type columns can only be filtered with the = operator, not with < or >
- Date type columns will update correctly but will show blank after an update
Datetime2 type columns will show blank if combined with a sort(Resolved 2017-01-18)
- Fields cannot be updated with null/blank values when app is played in the browser
- The IsBlank() function is not delegated
- The Sum() function is only delegated for integer (int) type columns and not for money, float and other numeric types
- The in operator is not delegated as a test for membership
- Tables must have a primary key
Temporal tables are not supported
(Resolved 2018-02-23)- Tables with triggers are not supported
Views are not supported
(Resolved 2018-02-01)- Varbinary(max) type columns are not supported
- Defaults() function does not return default value (credit to Dave Jorgensen for this one, and according to Jan-Erik Karlsson this is not an issue for on-prem SQL Server)
1. Date type columns can only be filtered with the = operator, not with < or >
The suggest work-around for this is to create a calculated column of integer type of value YYYYMMDD. Say you have a table called MyTable, with a date column called MyDate, then this is the T-SQL statement that will create the necessary column in Azure SQL Database:
ALTER TABLE MyTable ADD MyDateInt AS ((datepart(year,[MyDate])*(10000)+datepart(month,[MyDate])*(100))+datepart(day,[MyDate])) PERSISTED
In PowerApps you can then filter this column with any operator applicable to integers, including < and >
Another possible work-around is to use datetime type columns, but due to issues with time zones I would not recommend this approach.
2. Date type columns will update correctly but will show blank after an update
There are two possible work-arounds:
- Refresh the data source after every update operation, so for example:
Patch('dbo.MyTable', ThisItem, {MyDate: Today()}); Refresh('dbo.MyTable') - Display a calculated column like the one in 1. above but formatted as a date. Strangely the date type column is shown as blank but the calculated column depending on this date column shows the correct integer immediately.
3. Datetime2 type columns will show blank if combined with a sort
Please see this post on the community.
4. Fields cannot be updated with null/blank values when app is played in the browser
Finally, as announced in the PowerApps blog at the end of March 2018, we are able to Patch() null/blank values to nullable columns which currently contain a value and they will become null.
However, I was only able to get this to work in the web studio when playing the app. When publishing and simply opening the app from its link, fields were not set to blank. This is a bug.
There are two possible work-arounds:
- Create a stored procedure in Azure SQL Database to set the field value to null and then call the stored procedure from within PowerApps via Flow. This is not ideal, and not my favourite solution.
- Define a value (but not one that would ever be a real value) to stand in as a null (for example -999 in the case of people attendance), and use that value wherever a null is required.
5. The IsBlank() function is not delegated
Instead of
Filter('dbo.MyTable', IsBlank(MyColumn))
which is not delegated, you can use the following equivalent statement which is delegated
Filter('dbo.MyTable', MyColumn=Blank())
6. The Sum() function is only delegated for int type columns
The only work-around possible is for example to store money values in an int type column by multiplying the underlying values by 100 or 1,000 or 10,000 depending on the precision needed. Then the values can be divided again before being shown.
7. The in operator is not delegated
Where possible, the only work-around is to cache the target table in a collection within PowerApps, then the in operator can be used to filter the cached table to check for membership.
8. Tables must have a primary key
The work-around is to add an identity column to your Azure SQL Database table as primary key. You can do this with the following T-SQL statement
ALTER TABLE dbo.MyTable ADD ID INT IDENTITY PRIMARY KEY CLUSTERED
9. Temporal tables are not supported
For this one we will have to wait until they are supported by PowerApps. Any suggestions for a good work-around are welcome.
10. Tables with triggers are not supported
The suggested work-around is to move the trigger actions to stored procedures and then call them from within PowerApps via Flow after any insert, update, delete operations. This is not ideal, I agree.
11. Views are not supported
The good news is that views should be supported by the end of 2017, in the meanwhile either:
- Use shaping functions (Filter, GroupBy etc.) within PowerApps to obtain delegable expressions
- Use stored procedures with timer jobs within Azure SQL DB to update tables that store the results of any queries
- Use Flow to fetch query and use result in PowerApps
12. Varbinary(max) type columns are not supported
To store images in Azure SQL Database, use the image data type. This will work in PowerApps. However, even though it should be practically the same, the varbinary(max) data type will store the data but the image will not be displayed in PowerApps’s image control.
13. Defaults() function does not return default value
The default value set for a column in Azure SQL Database will not be returned by the Defaults() function. There is no possible workaround to this.
Hi,
Are dates in SQL server REALLY supported in powerApps? I’m hearing lots of issues in the forums,
https://powerusers.microsoft.com/t5/Expressions-and-Formulas/date-in-datetime-column/td-p/73382
Which date type would you recommend to be used in powerapps SQL server backend? An article on this would be great.
Hi Tinker, I generally use the datetime or datetime2 data types and have had mixed results. I keep hoping that integration of SQL Server with PowerApps will improve, as the last few updates have shown.
Hi,
PowerApps is returning an Azure SQL Time(5) field into a TextInput box as a string formatted under the ISO 8601 Time Duration, For example, “P3Y6M4DT12H30M5S” represents a duration of “three years, six months, four days, twelve hours, thirty minutes, and five seconds”.
I am storing either: 09:00, 09:30, 07:45, 17:15, 18:00 etc. directly into the SQL table, however, the output when read by PowerApps is PT16H30M for 16:30.
Any advice for a workaround would be very much appreciated.
Thank you
Hi AvMaxDan, unfortunately PowerApps and Azure SQL date/time fields don’t really communicate too well. I have no obviously good solution, but can suggest one of three things:
1) leaving the column type as is and parse the string in PowerApps with string functions, or
2) changing to an int type column and store your time as hhmm, or
3) leaving the column type as is and add a calculated column to expose an int of hhmm type to PowerApps
Each option has advantages and disadvantages.
I can’t seem to place an Edit Form on a screen and get it to display Text Input controls. It looks and behaves exactly the same as the Display Form. Is anyone else having this issue using an Azure SQL table?
JRV, thank you for your comment. I avoid the use of forms as I create my own from the single controls. I find it much more robust.
I am experiencing the exact same thing – with great frustration. This was actually what brought me to this post.
Sorry I could not help, Flemming. I just refuse to use the OOB forms, they are too buggy, and building my own resolves 95% of the issues.
Hi,
Thank you so much for creating this blog. I am loving the examples you provide.
How many of these issues are valid for on-Prem SQL too?
I know the Default() works well with our on-prem MS SQL, no issue there.
Jan-Erik, thank you for your note. I only work with Azure SQL Database, but my impression from the comments of others on the main PowerApps blog is that most of these issues apply to on-prem SQL Server as well. However, I will update the post to reflect your information.
Hi Meneghino.
Great info, thanks.
A bug(?) I ran into with PowerApp=>Azure DB is getting a column default value.
I added a constraint on a column that does not allow nulls. I assume from the PowerApps documentation that opening a new form should automatically populate the field with the default value. It works fine determining that the field is required. I tried calling the function like so: Defaults(). and putting that value into a text box as well, since I would have expected to extract the default that way, but no luck. Have you ran into this issue?
Thanks again.
Hi Dave, indeed you are correct. This is confirmed and I have added to the list. Many thanks.
Steps I took. In Azure SQL DB, create a table Order.OrderHeader (OrderHeaderID Int (Identity 1,1) Primary Key, OrderHeadName varchar(50), OrderHeaderTime DateTime2 Default Now()).
Create PowerApp. Link to data source. Create a gallery form to display OrderHeaderName and OrderHeaderTime from Order.OrderHeader.
Create a form for adding a record, name it frmNewOrderHeader.
Set DataSource as ‘[Order].[OrderHeader]’
Set DefaultMode to FormMode.Edit
Item to GalOrderHeaderSelected. Add fields for OrderHeaderName and OrderHeaderTime. Set default for OrderHeaderTime to Now()
Add a ‘New’ button to the screen with the Gallery.
NewForm(frmNewOrderHeader);Navigate(scnNewOrderHeader, ScreenTransition.Fade).
Create a new record via the ‘New’ button. In the designer this works until you close and then re-open the app, after which you get the ‘Entry is required…’ error.
Publish the app and you cannot add a record at all, you always get the ‘Entry is required…’ error.
I then added a timestamp field to OrderHeader, refreshed the data and from that point, the application functioned as expected.
I had the same issue with a similar table Order.OrderDetail. Again, adding the Timestamp field resolved the issue.
I was not able to reproduce your issue. New records were successfully created by a Collect(‘[dbo].[OrderHeader]’, {OrderHeaderName:”Hello world”}) action, even after saving the app in Studio and reopening in Chrome.
The T-SQL statements I used in Azure to create the table is the following:
CREATE TABLE dbo.OrderHeader
(OrderHeaderID INT IDENTITY (1,1) PRIMARY KEY,
OrderHeaderName VARCHAR(50),
OrderHeaderTime DATETIME2 DEFAULT GETUTCDATE())
Please note that I have corrected the OrderHeaderName column name from OrderHeadName
One more for the list. To ensure that new records can be added, you should include a column of data-type timestamp to the table in Azure SQL DB. You may need to add this column as a Datacard on your form (for adding new records) in PowerApps (it can be set to Visible = false).
Without a timestamp field, I was unable to reliably add new records.
I have not come across this issue, could you please outline how to reproduce it?
Thanks very much for presenting this information in one place – very useful.
Please update these as they are addressed, perhaps adding a date for when each issue is resolved as this would give a good idea of how quickly PowerApps is being fixed/improved.
Seems very odd to me that the Image data type (long since marked as deprecated) is supported but that varbinary(Max) is not!
Yes, it is indeed strange that varbinary(max) is not supported. Let’s hope it gets resolved soon.
I will update as you suggest, please let me know if you think anything else should make the list.