My organization handles batches of stuff that need to be checked by Quality Control. What I need is something for the people who work in Quality Control to be able to register and input data inside the laboratory, they will be walking so we thought that tablets with an excel would work pretty well but we ran into the problem of file merging and losing data.
We need some type of app:
- That lets you input data by multiple users at the same time.
- It should be able to be used with or without connection, and if there is no connection it should synchronize everything that was updated.
- We are trying to avoid having to develop an application with a database since we like the liberty something like Excel gives for users to manipulate the data. Since most of employees are familiar with Excel.
- We would prefer it if the app were inside the Microsoft ecosystem, we considered Microsoft Lists for a while but not being able to use it offline is a big problem.
We stopped using Excel because merging problems were too common.
Is such a system feasible, or are these requirements too much in conflict with each other?
11
Have been there, and have experienced the very same problems like you. In short:
Accept that you cannot have a cake and eat it!.
If you want multiple users enter data into a system simultanously, with the option of doing this offline, and a merge process afterwards, you need
-
a rigid frontend, which makes sure all data entered has at least a certain technical quality
-
a central database or datastore with a rigid import & merge which is specifically made for your business and your data structures.
Can one use MS Excel (or another offline spreadsheet software) for the frontend, on tablets? Sure, under the following constraints:
-
The process of “registering and entering data on a tablet” does not need online access. So you might provide some key data out of the central database at the beginning of each day which is fixed, and then no further online data over the rest of the day.
-
The data structures are not overly complex and do not require a full-blown GUI for ensuring the users will enter the data correctly
-
the users can fill out predefined, rigidly preformatted spreadsheet table templates, where changing the structure is blocked for them.
That will leave users the freedom of a spreadsheet, at least partially, but only to the degree which makes sense.
Then, an import & merge process of such data is feasible – however, you won’t get something like this for your case out-of-the box. You said merging problems were too common – because, I am pretty sure, you tried to merge this data directly, manually, without a specific merge program which works as a quality gate and knows precisely how merges and merge collisions of your specific data structures have to be handled. Hire an experienced programmer for implementing this part, I don’t think there is a sensible way around it.
Moreover, you need to make a decision about the backend (database or datastore):
-
Maybe some small web application is sufficient, with some lightweight database like SQLite or MS Access, where the spreadsheet table can be uploaded when the users reach online access again.
-
Maybe a combined spreadsheet is all you need to merge the data of different users collected over a day or a week.
-
Maybe you need a full-blown transactional database like MySql, PostgreSQL, MS SQL Server or Oracle.
That depends on things like what you are actually want to do with the collected data, how much data in total will pile up over the expected usage period, in what frequency the merges will have to happen, if those merges will happen concurrently or can be queued, how complex the data is, as well as how complex the after-processing will be.