PowerApps / Microsoft Flow – Using data in SQL Azure

Last week Mark Sipsma mentioned to me:

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.

Create SQL Database in Azure

Then I tested the connection in Flow

Create SQL Connection in Microsoft Flow

And when I ran a flow a default table ‘sys.database_firewall_rules’ is found.

Get Tables in Flow

Time to create a table for myself. The query editor makes this easy:

Create Table in Azure SQL using Query Editor

 

And my table is created within no time.

Persons table has been created

Within PowerApps I can connect to my SQL Server Database like I would do with any datasource. Still all is easy to do.

 

Connect to SQL Server in PowerApps

 

Even my newly created table is there immediately.

Pick a table in PowerApps

Again to connect my gallery to my persons table is just a matter of selecting the table.

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

I can run my app and all is working as expected.

Time to update my data. Again I use the preview query editor to add a record.

Editing Data in Azure SQL

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.

Adding refresh button in PowerApps

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.

 

 

 

 

Advertisements


Categories:Azure, Flow, Microsoft Flow, Office 365, PowerApps

Tags:

1 reply

  1. Thanks , very useful

    Like

Please leave a comment or feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: