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
Solved! Go to 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
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
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
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
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.
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
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!
Glad I could help 🙂
User | Count |
---|---|
186 | |
123 | |
91 | |
47 | |
42 |
User | Count |
---|---|
271 | |
159 | |
132 | |
84 | |
78 |