New Bug Encountered:Large Database

AppSheet AppSheet Q&A
1

I rolled out a new app to new users using an existing GSheet database. My users access that data off-line. Users are reporting many problems that lead me believe it may be due to their phone’s databases not being updated completely. The database has 1400 records with average 3 photos per record and the photos average ~0.3 MB size (set to medium upload size in AppSheet). So the database size is ~1.3 GB. My phone shows 0.9 GB used.

How and when does the a phone update the ~1 GB database so that it can be run off-line? I don’t think that happens on sync because - isn’t that associated with synchronizing the few new reports on the phone with the database? I have watched my phone’s file size increase gradually over days when I delete and reinstall the data. When does this happen? Can I force it to happen asap for my users?

What if the phone’s database is not completely updated and a user working off-line tries to access a record that isn’t completely updated?

Is is possible to bring only a subset of the data into the phone, say only 2019 data, but sync it back to the entire database having 2017-2019 data?

Example of user problems yesterday and today:

  • Duplicate records saved on sync
  • A photo in a record was expanded by tapping, but then the photo disappeared from the record. It later reappeared.
  • A user enters his name and his email and phone are automatically retrieved into the record. This has worked perfectly for 2 years. Today email and phone from previous record came into the next record entered by another person.
  • Couldn’t get photos to come into the app either from AppSheet camera or uploading from the phones file

Thanks
Bob

2

It sounds as you said, that you have duplicate records and the result can be weird. Check your records that you don’t have any duplicates.

3

I’ve added some comments inline, with clarifications

Fof_Webmaster:

I rolled out a new app to new users using an existing GSheet database. My users access that data off-line. Users are reporting many problems that lead me believe it may be due to their phone’s databases not being updated completely. The database has 1400 records with average 3 photos per record and the photos average ~0.3 MB size (set to medium upload size in AppSheet). So the database size is ~1.3 GB. My phone shows 0.9 GB used.

Your data has two components — structured/tabular data and content (images, files, etc). By default, the structured data is cached locally on the device, but the content is not. If you enable Offline Content Caching, then the content is also cached locally. Note that the image content is explicitly downsized for devices. So your calculations of total size may not be quite accurate. For the rest of the response, I’ll presume you have enabled Offline Content Caching.

Fof_Webmaster:

How and when does the a phone update the ~1 GB database so that it can be run off-line? I don’t think that happens on sync because - isn’t that associated with synchronizing the few new reports on the phone with the database? I have watched my phone’s file size increase gradually over days when I delete and reinstall the data. When does this happen? Can I force it to happen asap for my users?

Sync is a two-way sync. Changes from the device get pushed to the backend. And then the data gets pulled back from the backend to the device (because it might have changed). When does this happen? — this is controlled by the options in the Behavior → Offline/Sync pane. On the very first sync, the offline content cache has to be populated (which is why the first sync needs to be run when there is connectivity). This may take a while and the image content is copied over in the background. On subsequent syncs, the system checks if each content item is already in the cache — if so, it is not re-fetched.

Fof_Webmaster:

What if the phone’s database is not completely updated and a user working off-line tries to access a record that isn’t completely updated?

The structured data is always updated all-or-nothing. There is no question of getting some records but not others. However, the content may not be fully cached if the user did not wait for the initial content sync.

Fof_Webmaster:

Is is possible to bring only a subset of the data into the phone, say only 2019 data, but sync it back to the entire database having 2017-2019 data?

Yes, you use a security filter for this. We always recommend that you do this to minimize the amount of data being moved to the devices. We have extensive documentation on scaling using security filters.

…

Apart from all of this, the user problems you describe sound like problems with the choice of keys and duplicate key values, as Aleksi indicates.

4

My key is a time stamp by the second. Yesterday was this first time I had a duplicated record - and this was from the same person. He was syncing repeatedly with poor connectivity because pictures he needed from previous records were not available, ie stored on his phone. AppSheet should roll back incomplete syncs, correct?

5

So I’m a bit confused then by what you meant by “duplicate records saved on sync”. You had a set of broad questions and I tried to provide adequate background.

Maybe we can focus on the specific user problems you are seeing. If there is a reproducible problem (eg: “email and phone from previous record come into the next record”), then we should be able to easily investigate.

6

I can now respond with specifics, since my IOS 12.2 user and I sat together. Our app, FoFReport-206341, is set to start when off-line and store off-line content. But photos are not available to him when he is offline. It does not appear that the phone is storing any photos - there is no AppSheet photo album on his phone. The rest of the app functions as expected and photos are available on-line though there is a delay to load them. When installing,our app did not prompt for permission to access camera or storage. See photo: Under IOS 12.2 settings, AppSheet, there are no options for allowing AppSheet access to storage. There is an AppSheet Option entry but it is a text field with no explanation.

Thanks
Bob

2X_f_f4cc61a6037b2518c8c0897564ed8cfdf7f00f68.jpeg
2X_f_f4cc61a6037b2518c8c0897564ed8cfdf7f00f68.jpeg1125×2436 195 KB

7

So Bob, the photos are not available offline. Did the user sync once when online and give it some time for the photos to be cached?

You would not see an AppSheet photo album explicitly, I think.

Does the app let the user take a photo via the camera or from the existing camera roll? That is what would need the permission to access the camera. It might prompt at the first access.

Added @Gil, our iOS developer for clarification and followup

8

@Fof_Webmaster,
As I mentioned in the email thread: I’m currently working on a fix for the cache issues. It will be out in the next iOS update (version 12.3), and would hopefully be released by the end of next week.

As Praveen mentioned, the cached content is internal to the app and won’t appear in an AppSheet photo album. Storage permission is not required for caching.
Also, while the app is downloading the cache, you can see the progress in the app’s menu. If no progress bar appears in the menu, it means it’s not caching at the moment.

If you’re seeing any issues with the 12.3 version please let me know.

9

Hi Gil,

When do you expect this fix will happen, so my iPhone users will be able to view photos offline? They can’t do their jobs without this. It seems your fix is dependent on iOS 12.3 release, and I’m concerned about a long delay in it’s release.

https://www.gottabemobile.com/ios-12-3-release-date/

Thanks

Bob

1 Like
10

Hi @Fof_Webmaster,
I was talking about AppSheet version 12.3, not iOS version 12.3. This fix has nothing to do with iOS update.
Should be in the AppStore by the end of the week.
I’ll let you know here as soon as it’s out.

11

What a coincidence that your version number and the timing of update are the same as Apple’s iOS update. I glad to here you’re in control and not Apple!

Thanks
Bob

12

@Fof_Webmaster,
Version 12.3 is in the AppStore now. If you go to AppSheet’s page in the AppStore you should be able to update your app. Users with automatic update turned on should gradually get this update over the next 7 days.
Please let me know if you’re still seeing issues with image cacheing once you have the update.

13

Gil, excellent. I’ll have some folks who had issues do the update and test out our app off line, then let you know the results . Thanks for jumping on this!

14

Gil, we’ve tested 12.3 and still have the same problem, no off-line photos exclusive to iPhones. We are volunteers who don’t go to work or have a central office. I don’t have an iPhone for testing, so rely on other volunteers who can’t debug at all. So I sent you my app details. Can you test it?

15

Hi @Fof_Webmaster,
Did the users first opened the app and wait for the app to download the images? You can see the download progress in the app’s menu.

16

Gil,
What is the progress on your fix for no off-line view of photos on an iPhone?
Thanks
Bob