Client Side data cache

[PaulAmmoun]

Hi,
I need to make a small inventory count module on a mobile device.
Just collecting barcodes and qty.
It should be offline.
The user will have to enter a barcode and Qty.
The barcode should be validated (making sure it exist in the existing barcodes).
The problem is that the number of barcodes is huge (109 thousand).
I am thinking of doing this by downloading the barcodes to the the file system via : client side data cache editor
then populating a drobdown from it.

Is it the right way to go ? I am having weird result using client side data cache editor :(
It works, then without doing any major modifications it stops working ...

I am using AA build 5159 with Microsoft SQL 2016 express

Thank you

[jgrannis]

Re: Client Side data cache

I have almost the same exact feature.

I did notice that when I use the Action Javascript, Client-side Data Cache Actions (Refresh Data Item(s)) that the default timeout for the serverIsAvailable method is set at 300ms.
Although that should be enough time to get a positive response back to that HTTP HEAD request, I see that sometimes it is not as I watch the failures in the console. I'll run the refresh again manually via the console and usually get a successful response on the 2nd or 3rd attempt. I guess the connection between my device (using cellular data) and the server is just a little slow. Even the successful attempts barely make it back before the 300ms timeout.

Anyway, I converted the Action Javascript to text and changed the timeout to 1000ms and have yet to see a failed response to the HEAD call.

As for the barcode validation part, I am luckily enough to be able to filter down the barcode array to a smaller subset to search against as the user arrives on the panel where they start scanning. By time they get there, I know which category of products they will be working with.

Bon courage !

[PaulAmmoun]

Re: Client Side data cache

Thank you Jeff,
I think you are right, the timeout might be an issue.
I noticed another issue: Since the data to be downloaded in 17mb it cannot be tested in the browser. I am able to test it normally on the mobile with phone.

"Bon courage" ;) I will need it. filtering by category is a great idea, thank you for the tip.

[NBoje]

Re: Client Side data cache

You can test the download in working preview

Go to Properties - Local storage / file system storage
Set Working preview testing mode to filesystem and specify a local folder below.

It may slow your app down while testing, normally I run with Browser, the default

[Selwyn Rabins]

Re: Client Side data cache

Hi,
I need to make a small inventory count module on a mobile device.
Just collecting barcodes and qty.
It should be offline.
The user will have to enter a barcode and Qty.
The barcode should be validated (making sure it exist in the existing barcodes).
The problem is that the number of barcodes is huge (109 thousand).
I am thinking of doing this by downloading the barcodes to the the file system via : client side data cache editor
then populating a drobdown from it.

Is it the right way to go ? I am having weird result using client side data cache editor :(
It works, then without doing any major modifications it stops working ...

I am using AA build 5159 with Microsoft SQL 2016 express

Thank you

if you have 109,000 records, then a client-side data cache is NOT the way i would do it.

I would use SQLite.

there is an action in action javascript to build a sqlite database on the server side and then download the sqlite database to the client.

there is also a new feature in the pre-release that lets you define a manifest of files to download to the device. this option allows you to zip the sqlite database -- which can result in a very significant reduction in the amount of data that has to be transferred to the device.

the reason that I prefer sqlite to the client-side data cache is that to read a client-side data cache you have to read the entire file into memory. if the file is large, reading it can be slow. especially on Android.

on the other hand a sql query against at 109,000 record sqlite database (especially if the sqlite database has the appropriate indexes) will be ****extremely**** fast (much faster than reading a client-side data cache)

[jgrannis]

Re: Client Side data cache

I was thinking of looking into the manifest way to go also but haven't had time yet to install the newer builds yet.

Hoping to eventually see pre-loaded list data getting stored there (as an option) instead of embedded in index.html for PhoneGap apps destined for a lot of offline use. Seems like the app would start faster.

[Selwyn Rabins]

Re: Client Side data cache

you can easily put pre-loaded data in a sqlite database that is part of the PhoneGap manifest. (The PhoneGap genie makes this very easy) The app will still start quickly and you will not need to download the SQLite database to the device after the app has started up. However, since the SQLite database was created at the time the PhoneGap app was built, the data in the SQLite database could be pretty stale. I therefore think it is a much better design to download an up to date SQLite database (when a refresh is needed, of course).

[jgrannis]

Re: Client Side data cache

Yes and admittedly I wasn't clear where my imagination was going.
I was thinking of cases like lists in a parent-child-child configuration. All bound to a company database benefiting from your synchronization design, all with detail views, all configured with auto-increment primary keys.
I definitely could (and have) attempted to do it all myself with SQLite. Huge pain.

Anyway, my imagination goes far... I'm thinking of AA/PhoneGap PWA type apps!

[PaulAmmoun]

Re: Client Side data cache

Thanks for the feedback.
I know how to create and download the SQLITE database.
But I don't know how to validate data against it :( any help is appreciated.

On another hand, is there a way to have a list linked to an sqlite table ?

Thanks again

[jgrannis]

Re: Client Side data cache

You could use the Action Javascript, SQLite Actions - Execute SQL statement(s) and run a SELECT on the table where you have the list of barcodes. If the select statement's filter is the unique barcode in the list and assuming the correct indexes exist, you will very quickly get a resultArray with the data for the specific record in the action's onSuccess code.

Now I can't remember off the top of my head but then, depending on the behavior that I can't clearly remember, you can either:
- get the length of the resultArray to test that the barcode record was returned
- test for the existence of the resultArray and/or resultArray[0].fieldname
- or (and I kind of remember that this may be a thing...) if there is no match, it may be handled as an error and the onSuccess will not run. You will have to be sure to handle the exceptions with the action's onError.

To answer your additional question: you can't really "link" a list control to a SQLite table in the sense of configuring it as the data source where you can then benefit from all the nice synch/detail view stuff.
I guess you could configure the data source to be a javascript function but that javascript function would need to run the SQLite execute stuff in order to return the required data needed to populate the list.
I have always just set up a list control with a static data source, and when needed, ran my query and used the resultArray and then the listObject.populate method.
To add or update to the SQLite table, you would need to execute again other INSERT or UPDATE queries.

[Selwyn Rabins]

Re: Client Side data cache

Jeff's answer is 100% on the money

By "validate data against" the SQLite database, I presume you mean something like this:
After the user enters data into a field, you might want to check to see if the value the user entered exists in one of the tables in the SQLite database. If so, then you would execute a SQL query against the SQLite database to see if you can find the data. There is an action in Action Javascript to execute a SQL query against a SQLIte database. If you query finds a record, then you have successfully validated. If not then the user entry is not valid.

In order to have a list "linked" to a on device SQLite database you would need to write some Javascript code yourself. You would need to execute a SQL query against the SQLite database. That will get you a "result" (which will be in the form if a JSON array of data) and you can then use this array to populate the List control (using the List control's .populate() method).

[PaulAmmoun]

Re: Client Side data cache

After using SQLITE, the barcode is validated instantly ..
I am not able to create the index.
I am using the action : create database on server then download
1_2018-08-11_1644.png

When I click indexes and try to add an index, I cannot select any field
2_2018-08-11_1647.png

[Selwyn Rabins]

Re: Client Side data cache

change the way you define the sqlite table from "custom sql statement" to "fields from a table"

[WebDesignShop]

Re: Client Side data cache

Additional idea: If you do have pre-defined categories you could create a CSDC for each of them, which of course would result in a smaller set of data. You could then have the user select the category and then pass the value in to javascript to read the correct CSDC item and fill in your list with that data. In one of our applications we needed a cascading set of data to be used for looking up location data (over 40k records) and it works very well. For us it was a simpler approach than SQLite and easier to update when needed. Otherwise, SQLite is a great suggestion. With that amount of records, no matter what, finding a way to filter your data will help application performance.