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.

SQL Azure in Microsoft Flow

I started by creating a SQL DB in Azure.

SQL Database configuration

Then I tested the connection in Flow

connect to sql in Azure

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

Get tables from Azure

Create a Table in SQL Azure

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

Create a table in SQL

 

And my table is created within no time.

My table has been created

SQL Azure in PowerApps

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 in PowerApps

 

Even my newly created table is there immediately.

connecttotable

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

Connect a Gallery to a SQL 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

Display the data from my table in PowerApps using a Gallery

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

My App shows the record from SQL

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

Adding a 2nd record to my table

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.

Refresh my data in PowerApps

Using the refresh function on the persons datasource, loads the data as I would expect.

Displaying 2 records in my app

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.

My newly created table is missing

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 appears when I create a 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

One thought on “PowerApps / Microsoft Flow – Using data in SQL Azure

Leave a Reply to Kate “Khojasteh” Esteghlal Cancel reply

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.