You’re offline. This is a read only version of the page.
Announcements
40
Hello,
The last few days we edited an app running and updated many times in the past weeks, meaning we are very familiar with PowerApps and this is not a new development. It uses the SQL Premium connector and part of its feature, it adds XML records to a SQL table together with a UID key and an internal index number. This is not done with a gallery but directly with Patch() command in a button. Previous versions of the app, unedited, can read/write perfectly to those 2 tables, it is then only when we edit the app that our problem happens.
The issue: The records are not written anymore in SQL Server, and no error message.
Here is what we did to test the add of a simple record to a SQL Server table:
- Table "licenseXML" is refreshed from the "Data" blade, it finishes with a success, we know there is no connection issue.
That table is empty, no record.
- Another table from the same database with the application name is read and we can display its first record content, no issue there. So we are 100% sure the connection from PowerApps to our Azure SQL Database is working perfectly, it is not a credentials issue.
- We created a "New Key ID" button that when clicked generates a random 50chars Index2. That's for the record key in the Azure SQL table. That ID appears on the left of the button in the screenshot below. This is to prove we don't have a duplicate key value issue for records.
- A "Generate record" button adds the content of fields to a new record (the index for "Index2" is new). We added the Refresh() before and after as an attempt to sync the PowerApp connector with the SQL table, in vain.
Refresh('[dbo].[licenseXML]');Patch('[dbo].[licenseXML]',Defaults('[dbo].[licenseXML]'),{Index2:RecordID2.Text, Id:License_UID.Text, licenseText:LicenseTextXML.Text});Refresh('[dbo].[licenseXML]')
- You can see above that we added an horizontal gallery linked to that SQL table [dbo].[licenseXML] and it shows the 2 record we created using the buttons.
- But when we browse the Azure SQL table we still see no new record, even after a refresh:
- On the PowerApp the Gallery still dhows 2 records.
- Then we go to View/Datasource/[dbo].[licenseXML]/Refresh and immediately the Gallery is empty. Interestingly, it shows that function Refresh('[dbo].[licenseXML]') is not the same as going to View/Datasource/[dbo].[licenseXML]/Refresh
- There is never any error message anywhere
Conclusion:
With the latest Powerapps release, we found a case where Powerapps uses a cache for SQL Server records but the cache never gets flushed to SQL Server. With previous releases of PowerApps we never faced that issue with the SQL Premium connector usage, same App, same usage of Patch().
Question:
Did anyone face that issue, and how to flush the data to SQL Server when creating a record from a button using Patch()?
Update:
In the same database I created another table, twin of table that's not updated when using Patch().
I then linked the app to that table and changed the formula to write to that new table.
Result:
The records are written in the table... until it will fail again one day as there is visibly a defect in the SQL connector.
Under some conditions the connector cache is not flushed to the SQL Server.
Share
Report
All responses (
Answers (
