I’m working on a PowerApps project that uses Microsoft SQL as the back end. Recently we have noticed that amendments to the SQL DB such as new fields and field amendments can take around 5 hours to show in a PowerApp as a selectable option in the formula bar and also in Flow as a selectable SQL field.
It feels like there is some sort of PowerApps to SQL cache that is taking time to update. I’m struggling to find any information on the architecture between PowerApps and SQL Azure DBs.
Today I had a look into this problem.
I started by creating a SQL DB in Azure.
SQL Database
Then I tested the connection in Flow
And when I ran a flow a default table ‘sys.database_firewall_rules’ is found.
Manually trigger a flow
Time to create a table for myself. The query editor makes this easy:
Query Editor
And my table is created within no time.
Tables
Within PowerApps I can connect to my SQL Server Database like I would do with any datasource. Still all is easy to do.
SQL Server
Even my newly created table is there immediately.
Choose a table
Again to connect my gallery to my persons table is just a matter of selecting the table.
Gallery1
But when I look in Flow my Persons table is still not found.
Within PowerApps however I can display my data form the table using a few label elements
Label elements
I can run my app and all is working as expected.
Running app
Time to update my data. Again I use the preview query editor to add a record.
Adding a record
Even though I saved the data, my app will only show one record. I decided to add a refresh button. Of course you don’t need to have a button in your apps, you can simply run the code on screen load.
Add a refresh button
Using the refresh function on the persons datasource, loads the data as I would expect.
Updated data after refresh
Time for the ultimate test. I’m creating an additional table in my database. I’m calling this table Persons2. PowerApps however doesn’t see this table.
New table is not available in PowerApps.
Ok, that isn’t nice. I decided refresh my connection, but still no luck.
When I created a new connection however the new table did appear.
New table is available with new connection
After some investigation with Mark, we found that there is a delay due to cache lifetime which is 30 min any database scheme changes should be visible by design after 30 min.
About the Author:
Since 2007 I’ve worked with SharePoint as a Consultant, Developer and Architect. I’m currently working at TRIAD in Milton Keynes, United Kingdom as a SharePoint Consultant. In 2017 Microsoft has awarded me with an MVP Award in the category Office Apps and Services.
Reference:
Veensta, P. (2019). PowerApps / Microsoft Flow – Using data in SQL Azure. Available at: https://veenstra.me.uk/2019/01/21/powerapps-microsoft-flow-using-data-in-sql-azure/ [Accessed: 21 February 2019]
