Evaluating LibreOffice Base for data collection process

[alanreed]

Hi there,

I am currently evaluating LibreOffice Base for use in our company’s research and data collection process. Our current process involves researchers collecting information from the web, entering it into Google Sheets, and then importing the sheet into MySql.

While we appreciate the ease of editing and collaborative nature of Google Sheets, we are running into some issues with its performance as we approach the maximum allowed size. Additionally, data types are not strictly enforced, which can lead to errors.

We are interested in exploring the possibility of using LibreOffice Base as an alternative. We need a spreadsheet-style interface with the ability to copy and paste multiple rows at a time and apply conditional formatting rules. It looks like there are a lot of features in Base that I am unfamiliar with. We are open to hiring a LibreOffice expert on a consulting basis to help with the evaluation process.

Would anyone be able to provide some insight or guidance on whether it is possible to achieve these goals with LibreOffice Base, and if so, how we might go about implementing them? Any input would be greatly appreciated.

[Villeroy]

Connect a new Base document to your MySQL database, add some meaningful queries and input forms.
See Apache OpenOffice Community Forum - [Tutorial] Forms in OpenOffice.org Base - (View topic) and the Base guide from English documentation | LibreOffice Documentation - LibreOffice User Guides

[Wanderer]

@alanreed

We need a spreadsheet-style interface with the ability to copy and paste multiple rows at a time and apply conditional formatting rules.

It is possible to have a grid as spreadsheet-style input on a form, but a database has usually an active row/current record/position of database cursor where the record may be changed. The free and equal editing in all rows is “alien” to a database.
.
When you copy records around you usually get problems with the unique-ids, so I guess you will need macros to support this.
.
You can just drag a table or a query of your database to a spreadsheet (Calc), even with the option to refresh data. From there you have all options to format and explore data, if you are more familiar with spreadsheets.
.
If you wish to copy and paste rows inside the database, I guess your database is not normalized?
.
As @Villeroy already told: Connect Base to your MySQL-database and play around a bit, following examples from the guide to see what is possible.

[Villeroy]

With Base you can delete multiple rows but you can not edit nor insert more than one record at the same time.
IF (and only IF) your spreadsheet data are consistent in respect to the rules of your database structure, you may be ablt to copy multiple records from a spreadsheet and insert (append) them to one database table.

You may try Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet at your own risk.