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

[PaulD1]

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

[v-yamao-msft · Accepted solution]

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

[PaulD1]

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?