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
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.

View solution in original post

2 REPLIES 2
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.

View solution in original post

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 🙂

 

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
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 437 members 5,988 guests
Please welcome our newest community members: