cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Limitations to SaveData, LoadData, working offline with SQL Azure DB data source

Short question: 

What is best practice when working offline with SQL Azure DB tables where inserts are required?

 

Long question plus detail:

I am developing an app for taking menu orders. The tables are OrderHeader, OrderDetail, MenuCategory and MenuItem. The database is an Azure SQL DB.

 

MenuCategory and MenuItem will not be updated by the user of the menu app, so I have already set these to cache locally when the app is started. This has improved performance significantly (e.g. when selecting from the menu category gallery, the associated menu items populate almost immediately in their gallery rather than a 3 or 4 second delay).

 

I would now like to have OrderHeader and OrderDetail work offline, but the user must be able to add new records to these tables and these must later be saved to Azure SQL DB. The purpose of making these offline is to improve performance and to allow the app to be used in the event of a connectivity failure. I would have the app write any cached data back to the Azure SQL DB either on a timer or on the event of an order being paid.

 

Both tables currently have primary keys that are identity fields (incrementing by 1 for each new record). OrderDetail includes a foreign key to OrderHeaderID in the OrderHeader table. Typically a user will create a new OrderHeader record then a number of associated OrderDetail records.

 

What is best practice when working offline with tables where inserts are required, especially where one table must reference data inserted in another table? If I change Identity to GUID, will PowerApps be able to generate the GUIDs while working in offline mode? I would prefer not to use a composite ID both for clarity and because I understand PowerApps will not check for uniqueness in a composite key when working offline.

 

Thanks

Paul

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Limitations to SaveData, LoadData, working offline with SQL Azure DB data source

Hi PaulD1,

 

Please check if the following thread could help you understand more:
https://powerusers.microsoft.com/t5/PowerApps-Forum/PowerApps-with-SQL-server-connection-Offline/m-p...

 

It seems that you may need to create 4 collections to work with it. Please check the thread for more details.

 

Best regards,
Mabel Mao

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Limitations to SaveData, LoadData, working offline with SQL Azure DB data source

Hi PaulD1,

 

Please check if the following thread could help you understand more:
https://powerusers.microsoft.com/t5/PowerApps-Forum/PowerApps-with-SQL-server-connection-Offline/m-p...

 

It seems that you may need to create 4 collections to work with it. Please check the thread for more details.

 

Best regards,
Mabel Mao

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Limitations to SaveData, LoadData, working offline with SQL Azure DB data source

Thanks for the references @v-yamao-msft

 

It seems that, currently the procedure for true offline working is far more complex than it will be worth for me to develop, especially as I am finding development in PowerApps slow due to all manner of bugs and anomalies which I need to investigate and work-around.

 

Making my 'static data' (a menu that is not updated by the app and only infrequently updated in the database) available offline was quite straightforward and a big win in terms of performance gains, so I think it best to quit while I'm ahead Smiley Happy

 

One related question. As per the example in PowerApps documentation, I initially placed the code to cache the static menu tables in the OnVisible event of the initial application screen, but that makes it fire every time the user returns to that screen. So I tried moving the code to the OnStart event but that does not seem to work. For now, I have had to resort to adding a button which users will have to be told to press each day when opening the app so that any changes in the menu are downloaded.

 

I guess I can make a 'splash' screen that caches the data. This can have a button that takes you to the first real screen and no button to take you back again. Any neater suggestions?

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 55 members 4,279 guests
Please welcome our newest community members: