Storing data on mobile phones

[Alaa-Alarori]

Hello Community,

I developed an online app and now I am adding the offline capability, everything is fine so far.

There is a little question:

I have an Excel table of governorates, districts, subdistricts and communities of a country that adds up to 5713 records, in the online app I stored this data on Sharepoint but with the offline feature I need to store this data locally on Android and iPhone phones. 

I thought of the following:

- write a little code to serialize the whole table into a collection (like taking each cell and place it in a ClearCollect function)

- use SaveData to store the collection locally

- put this collection in a separate powerapps app so the users run this app once to create this file on the phone and then they go back to my original app

What are the best ways to store this data so I can use them offline

Thanks,

Alaa

[wyotim]

I would recommend the SaveData method.

One issue you will run into however is the delegation limit, which by default is 500 items. This effectively means that if you use a Collect/ClearCollect to save the data and then do a SaveData on the collection, it will only have 500 items. To get around this, you can increase the limit up to 2000 in the File->App Settingns->Advanced Settings menu under Data row limit.

Obviously, this only sets the limit at 2000 and you need 5700+. To get the rest of the way, you will need to write a series of If statements to load the data fully. Basically, take the first 2000 items, filter those items out, take the next 2000, filter those out, and take the rest. If you like, I can post some example code. I do this with pretty large sets that need to be stored locally and it works out pretty well.

[Alaa-Alarori · Accepted solution]

Hi @wyotim

Thanks for your reply

this is what I tried yesterday and it seems to work fine:

1- I wrote a macro in Excel to add the required quotations and brackets so each row looks like this:

{Governorate: "XXX", District: "ZZZ", Subdistrict: "YYY", Community: "BBB"}

2- Set the items limit to 1 (as I always do)

3- copied all of the table into a ClearCollect function

4- I am using this collection as my database.

I tried putting the ClearCollection in an OnSelect function of a button and tried putting it in an OnVisibile of the first screen of the app and it works smoothly in both cases

Thanks for your help

Best

Alaa

[wyotim]

Sounds like a great solution!

If you have the time, would you mind sharing more about your solution? I am curious about setting the items limit to one. I assume this is to remove the delegation issues? Again, if you wouldn’t mind sharing, I would enjoy hearing about and learning from your approach.

[Alaa-Alarori]

yes exactly, items limit is set to 1 so I don't encounter any problems in the future when so much data is entered and then it will be harder to solve. if a formula is not delegable at 1 then it needs to be changed, this approach takes more time and work but once I publish an app I rest assured it doesn't have limitations.

for the solution, I pretty much explained all the steps I did. I realized though that if I save the collection in a local file from one app it won't be accessible from another app so I am not saving it locally, instead, I am just loading the collection on OnVisible trigger and using it as my data repository

if there are any specific questions you have I would be happy to discuss

 Alaa

[wyotim]

I do have some questions. For your third step, copying the table with a ClearCollect, how does that work with the item limit at 1? Do you have to write something to pull all the items in or does it pull the full table in because Excel is the data source?

I primarily use SQL and I was under the impression that Collect/ClearCollect will only pull in what the item limit is set at.

[Alaa-Alarori · Accepted solution]

I see,

Let me explain again with more details:

- I had an Excel sheet with 5713 rows of data with the data looking like this:

Governorate District Subdistrict Community

AAA              BBB      CCC          DDD

EEE              FFF      GGG          HHH

- I used Macros to add quotation marks and brackets to each cell so I don't have to write them myself one by one (which is almost impossible)

so each row looked like this:

{Governorate: "AAA", District: "BBB", Subdistrict: "CCC", Community: "DDD"},

- I copied the content of the Excel sheet to a ClearCollect function so it looked like this:

ClearCollect(Pcode,

{Governorate:"AAA", District:"BBB", Subdistrict:"CCC", Community:"DDD"},

Governorate:"EEE", District:"FFF", Subdistrict:"GGG", Community:"HHH"}, ... the rest of the data)

- this collection is a local data source and I am not storing any data online, therefore, the data limit doesn't apply here

on a different note:

Data limit only applies to certain functions and data sources in Powerapps which are called non-delegable 

you can learn more about those here, so the limit is related to your code actually not the volume of your data.

as an example:

I use Sharepoint as my data source. Filter is a delegable function But when I try to filter a Sharepoint list (equivalent to a table in sql) on a Choice type field I receive the delegation blue dot, so to avoid that I change the field type in the Sharepoint list to Text and replace the Textbox of that datacard in Powerapps with a dropdown control and add those choices in the app

this is what I meant when I said it takes more work and time, 

I hope I was able to explain myself better this time and please feel free to reach out if still not clear

Best,

Alaa

[wyotim]

Thank you for the reply. I was misunderstanding what you had stated before (and quite clearly in retrospect).

A number of apps I have built have offline data requirements which means large amounts of data stored on-device using Collect/ClearCollect and SaveData/LoadData functions. Storing data this way, the item limit does matter so I have to code in a way to collect enough data to meet requirements (as much as 15,000+ rows in some cases). My brain was stuck in that process while I was reading your steps I suppose.

I am intrigued by the single item limit practice you have described though. Looks like I will be doing some testing in the near future to see how I can adapt it to my own practices!

Thank you again for following up with me. I appreciate your time!

[Alaa-Alarori]

Glad I could help 🙂