Implementing Offline Capabilities for Relational Data

As you may already know, PowerApps provides offline capabilities for your applications to enable users to be productive when there is limited internet access or no connectivity. Here is a blog which talks about basic implementation details for offline capabilities in your app. The current tutorial provides deeper context on how to build these offline capabilities along with relational data (master and child) in your PowerApps application.

This tutorial assumes that you already know the offline concepts of PowerApps like SaveData, LoadData functions and Connection signal object. We'll will use all these concepts in this tutorial .

Scenario

For this tutorial , let’s assume we have to build a simple Store Inspection App.

• Primarily, app should have 3 screens

1. List of Stores	2. Add Store Screen	3. Add inspection Screen

• Screen 1 should show the list of all stores including the stores that are created when offline
• Screen 2 captures new stores along with inspections (Screen 3) even when the internet connection is not available and save them to the database when the internet connection is available
• For existing stores, new inspections(Screen 3) should be prepopulated with latest inspection data (sorted by created data) in both offline and online mode.
• Offline changes should be synched to online by user intervention (on explicit button click)

Note: You can find the finished sample msapp file(for code reference) over here. For this app, I used CDS (Common Data Service) as the data source. However same concepts will apply to any other data source like SharePoint, SQL etc.

So to achieve above mentioned requirements, I have created 2 custom entities called Store and Inspection with 1 : n relationship ( I.e., one store can have multiple inspections) in CDS to capture Stores and related inspections data.

Part 1 : Offline Caching of master and child tables

First screen of this app is a List of Stores. So, on Start of the app, we’ll check for the internet connection using PowerApps built in operator “Connection”. If internet connection is available, Populate both parent and child entities data “Stores”, “Inspections” into local collections “StoreCollection” and StoreInspectionCollection. Also, save it to the local storage (local caches) as “storesInLocalStorage”, “storesInspectionsInLocalStorage” so that we can use it as local cache when the internet connection is not available.

High level approach for accomplishing this is below:

On start of the app, check if the internet connection is available or not If internet connection is available, fetch the data from the database and put that in a collection and save this collection data to local storage of your device (local cache) so that we can use this cached data next time when the internet connection is not available. If internet connection is not available, fetch data from local storage of your device and use that to populate the collection.

Part 2: Capture new stores and inspections whether the device is online or offline

Create new store

For adding new stores, add a new screen and insert an EditForm control with data source to Stores in that screen. Navigate to this screen on the press of plus sign from the first page.

Our high-level approach to save new Stores will be as follow:

1. If the internet connection is available,

• Submit the form so that new issue is directly saved to the database.
• Refresh the “Stores” data source and repopulate the parent collection with the latest data so that your local collection has the last added record
• Save this local collection in the local storage– this ensures that the newly added record is available in local cache to be used next time when the app is opened in offline mode (without internet connection)
• Set the SelectedStoreid variable to last submitted record id, so that it can be used in the child screen.
• Navigate to inspection screen to create the inspection for newly created store

2. If the internet connection is not available,

• Here, we need a unique id or GUID to carry on the parent child relationship. So, create a temp unique id for new parent record so that you can attach this id to child records for relationship tracking.
• PowerApps does not have better way to generate GUIDs so I have used date time now in milliseconds as unique id, because it is almost not possible for the same user to create two records in a millisecond. You can use your own approaches to create this temp unique id’s.

3. Set the SelectedStoreid variable to this unique id to use in child screen

4. Add a variable called isDirty= true to identify offline records while synch back to online. Use Collect function to collect all this details into master collection “StoreCollection” so that this offline record reflects on the Stores Screen.

• Save this local collection in the local storage– this ensures that the newly added offline record is available in local cache to be used next time when the app is opened in offline mode (without internet connection)

Create new inspection

Now, let’s focus on how to save the inspections(Child) for stores, high-level approach for this is almost like Stores with fewer changes.

As we mentioned above in this tutorial , Inspection form would be prepopulated with latest/last inspection record (sorted by created date), So again we need to cover 2 scenarios here.

For New store, there will be no inspection records and form will be new and for existing store, we will find the latest record using Last and Sort By functions and pass this record as parameter(LastSurveyOfStore) to inspection screen using Navigate function.

Our high-level approach to save new Inspections will be as follow:

1. If the internet connection is available

• Submit the form so that new Inspection is directly saved to the database.
• Refresh the “Inspections” data source and repopulate the child collection with the latest data so that your local collection has the last added record
• Save this local collection in the local storage
• Navigate back to Stores screen (1^st Screen)

2. If the internet connection is not available

• Add a variable called ParentID and assign the SelectedStoreid (Which contains latest uniqueid in offline mode and last submitted record id in online mode).
• Use Collect function to collect all this details into child collection “StoreInspectionCollection” so that this offline record reflects on the Stores Screen.
• Save this local collection in the local storage

Part 3: Sync up offline records when internet connection is available

On the “StoreScreen”, Add a label at the bottom to show how many offline records are to be save to database (or, if all of them are saved, indicate so). This is done by filtering the count or rows in the collection using isDirty flag.

Add a button for user intervention to save the offline records to database (We can also have timer that checks for the internet connection availability using the same Connection object which we used earlier for every given time interval). If the Connection is connected and button gets enabled and gives the opportunity to save the pending offline records.

Our high- level approach would be first to patch the parent record and update the corresponding child records parentID with patched primary id and then patch the child records.

In detail as follows

Filter the StoreCollection and StoreInspectionCollection for items that are added offline using isDirty flag and ClearCollect these items into new collections (“StoresTobeAdded”,” StoresInspectionsTobeAdded. Use ForAll function to iterate each record of new Parent collection and patch each store(parent) record. Collect this patched item into temp collection to access the primary id since PowerApps does not allow to declare a variable in ForAll to hold the patched item. Identify the children of patched parent record using ParentID variable in new child collection and Update the corresponding child records ParentID value with newly patched primary id. Remove the patched item from the collection and continue to evaluate other records in the loop Refresh the data source (Stores), collect to local collection and save this local collection to cache (local storage) Now, it’s time to save the child records, Use ForAll function and Patch each child record. Refresh the data source (Inspections), collect to local collection and save this local collection to cache (local storage)

Conclusion

Hopefully, this tutorial gives you an idea of how to build offline capabilities with relational data in your application.