I am new using Libreoffice Base, and databases in general, I found is easier to use LO Calc to input data because I can not copy a cell to another cells, I need to introduce individually each cell in LibreOffice Base and this very slow.
Which is the workflow to speed up, introduce or manage databases in LO Base?
Mark a cell content, move the mouse and copy the content to every other cell.
But: Cell is defined by the field properties. So you could copy content from one field to another if properties of this fields are different (Integer couldn’t be copied to Decimal or to Varchar and so on). This will work inside a tablecontrol of a form.
If you have created a primary key with autovalue:
Mark a row, move to new row and the row will be inserted as new row.
Mark many rows and move to new row and all rows will be inserted.
This will work in a table directly.
This is still vert hard to manage, I need to manage like LO Calc, I mean copy a cell in a range / row / column or add formulas, etc.
Is there any possibility to copy the table to Calc, then edit the table, delete the actual Base table content, then Copy the edited table as new content?
Cells, which should be calculated, won’t be saved. Calculation will be made by queries or views from original data. So changing the data will always show the changed results in the calculation.
Download Base Guide 7.3 and try the example in the first chapter (Introducing to Base → A simple database - a detailed example). This would help to understand the concept of base.
Fields in Base will defined first. It could only save content, which is defined through this definition. In Calc you could input content as you want: numbers, dates, text, boolean and Calc won’t control if its is usable content for you. So many people want to calculate and see: Oh, I have input text content, but it looks like a date…
You could copy content from Calc to Base. The wizard will ask if you want to create a new table with this content. But have a look at the columns first. Characters could only be save in a column for text content, dates should be saved in a column for dates, decimal values should be saved in a column for decimal values. And also have a look: How many characters do you need? How many decimal places do you need? Might be some rows won’t be imported because you haven’t set enough length for the text content or enough decimal places for a value you try to import.
Besides the question, if this is a useful approach: You can do this quite easily, if there are no relations preventing this. But if you have for example a table with names and a second related with their addresses, you can not remove all names as this would violate the relation.
.
You may then work either without formal relations or you need to update the names instead of simply deleting and replacing tables.
.
Note: A database is not only for quick retrieval, but also to check types and referential integrity of the data. One reason, why Calc is slower is not having rules what to expect in a cell.
.
How to do:
Open a table in base, mark some lines and select copy from context menu of the row-headers. When you paste in Calc you will find a header row also copied automagically. To copy the whole table you don’t need to open it, but can copy from context menu in the tables-list.
.
To delete rows, mark them and delete via context menu. I often prefer Extras->SQL from the menu and enter DELETE FROM tablename when importing external tables manually.
.
To copy from Calc to Base, check first with Ctrl-End, you don’t have garbage/empty lines at the end of your data, then mark the complete table and select copy from context menu of the row-headers. In Base select Paste from the context menu of the destination table in the tables list. An assistant will be shown, where you can even reorder the columns. But if you only copy a table back, you will not need that part.
I should add: It is possible to update tables with relations intact, if you use SQL statement UPDATE, but I don’t think, this is the way you’d like to go.
Calc and Base are very different. Formulas in Base are not executed from a “cell” fomula, but from a macro script which is not stored in the table. For example: to sum column1 (cell1) plus column2 (cell2) and show the result in column3 (cell3) in Calc: the formula “=cell1+cell2” is written in cell3 where the result will be shown. In Base: a similar formula must written to an SQL query or a sub routine (macro) that executes when the user does something (event) with the mouse (click), keyboard (enter), or to a form control (listbox) based on column3 of the underlying table. It may be easier for now to stay in Calc and explore the built in Data / Validity method. Data can be easily recalled and duplicated using this tool! If you would like a small sample of this, reply and I will post a sample ods as a suggested solution. Cheers!
Sorry, that’s bullshit. The formula language of a relational database is SQL. It can do simple arithmetic out of the box, faster and more reliable than any spreadsheet.
Yes SQL also. (Edited) The point was that base tables do not execute calc formulas written in a column. The user has to “write” a macro (or SQL) to make fill a column with calculated results. I guess I missed the part in the poster’s question about being concerned with calc’s performance and reliability. If one wants to copy and paste or drag and drop or make calculations right in a column… LO Base is NOT the platform to do those things… Calc is!
You should never save calculated values in a table. You save the original data and calculate all by a query. So if you change values of the field you use for calculation the integrity won’t be lost:
Example:
“a” * “b”
In SQL:
SELECT “a”, “b”, “a”*“b” FROM “Table”
a=2; b=3; query will show 6
Now do this in Calc and copy the content from Calc (or created by a macro) into a Base file:
Result in the table would be 6. Looks well, but if you change
a=4; b=3; query will show 12 but the old value you inserted by Calc or macro will always be 6.
It is easy to get some result from a spreadsheet. However, it is incredibly hard to get correct results, particularly when the same spreadsheet is changed thousands of times over the years, collecting data, expanding references, copying around formulas, loading years old megabytes of data, formulas and formatting into memory before you can enter a new data point.
There is not a single certified accounting software that is based on spreadsheets. No IT professional would store real world data in spreadsheets. A spreadsheet is a handy tool for quickly drafted demos, presentations, what-if-scenarios, predictions based on real-world database data, database mock-ups. Your Amazon bill is not calculated with Excel nor is your investment report.
The first spreadsheet, Visicalc, has been invented 45 years ago as a pocket calculator on steroids where one can easily build up calculation models and see how the output changes as parameters are changing. The core functionality of Excel and Calc is still the same as in Visicalc: relative/absolute references, skalar functions working with numbers, scale lookups rather than database lookups, 2 data types number and text (“labels” in Visicalc). There is still no consistent way to store data in spreadsheets.
One of the problems most frequently discussed on spreadsheet forums is csv import, when people try to import csv data, originating from some database, into their arithmetic calculator and then try to continue working with database data as if their calculator was a database.
Same with Excel/Calc. In a perfect world, you keep data and formulas separated from each other, although the spreadsheet does not enforce any such separation.
You may need to convert calculated values into constant ones when you do some restructuring work on your calculation model or when you encounter performance issues due to many thousands of “database records” with endless cascades of VLOOKUP formulas.
In the real world, there are reasons to deviate from sound principles and store calculated values in a database, depending on how expensive the calculation is. In this case, a database provides the means to protect calculation parameters from being changed.
Invoicing sample with calculated amounts quantity * price and sum(amounts):
Invoices_Articles_Prices.odb (165.1 KB)
A customer table.
Invoices storing the customer ID and invoice date.
A table of products.
A table of prices with a product ID and start date of a changed price.
A sales table storing which article has been sold on which invoice with a quantity figure.
The price of each sale is looked up by the article ID and the invoice date from the table of prices, multiplied by the quantity. The overall sum for each invoice is calculated either by means of a subform query or by the report builder which has its own calculation tools.
jtm,
This is coming from someone who has been there!
Use LO Base forms with SQL to add data to tables, not the tables themselves!
Lots of youtube videos in easy to understand formats to help you!
Thanks everyone for the answers.
I agree, now that I learned a bit more LO Base I understand that results should be not stored in the tables. But I still need Copy / Paste From Calc to Base because I need to migrate from others platforms that closed source like “S10 Presupuesto” and others ERP for construction works budget.
Here is a sample: (real case)
In my work, I received PDF files with the costs of construction:
Individual items:
01. BLOQUE 01 - LISTADO DE INSUMOS.pdf (161.8 KB)
unit costs:
04. INSTALACIONES ELECTRICAS.pdf (336.0 KB)
budget:
02. BLOQUE 01 - PRESUPUESTO.pdf (85.4 KB)
In Excel, I converted all pdf to table format using Excel and notepad++, then exported metering data from BricsCAD / AutoCAD, then used =filter formula to link all tables with prices, then used Pivot tables to do similar to when LO Base do Query, and the result is this:
PROPUESTA PRESUPUESTO - 01_10_23 - rev01.xlsx (238.0 KB)
Normally this is done with closed source ERP software, but I found is already possible a migration from these software programs to Excel / LO Calc using relationship tables, I mean LibreOffice Calc can alreadt replace completely ERP softwares and Excel, at least for me.
The next step is the performance, In both cases, in Excel or LO Calc, when try to calculate formulas, after I try to update data / table / row / columns, the result is very slow only with 10000 rows, and this is only a small project.
Now I found LO Base as an alternative to this task expecting updating millions of rows / columns and thousands of tables in a future, the only problem I found is that If I rewrite a row / column, or move columns / rows is very slow change each cell individually, because it is not possible to manage tables like LO Calc.
Delete all rows / columns, and copy / paste to same tables from LO Calc to update rows / columns solve this for me, but I think this is not necessary, If LibreOffice already have those features in LO Calc, Why not implement in LO Base to speed up workflow? At least optionally. But I am new in SQL Data bases, maybe is there another method to manage this task?
Because Base is not "the Database of LibreOffice. It is a connecting Module to Databases, wich unifies the access. The database can be a quite simple datasource like a bunch of csv-file, dbase-files or even a Calc-Table. But it can also be a full database server like Postgres/MySQL/MariaDB wich may not even be on your computer/network, but “somewhere on the internet”. ODBC and JDBC-drivers can connect to databases never known to developers of Base/LibreOffice. So Base can only interact with these databases and often not directly modify data.
.
Also consider: This are multi-user databases. So you are usually not expected to delete tables, wich other users may access at the same time…
.
At the moment I guess you will use the default “HSQLDB embedded”-database. This is a database written in java, where all the data can be put in your .odb-file, when you close the database and will be extracted for access, when you open the file again. This type is very useful to send around a “complete database” by email/ftp etc., but not recommended for productive databases.
Usually a database is/should be a carefully designed structure which is seldom changed. And you usually try to have few tables: So not “table of outlets in unit6” etc. but a single “table of outlets” for all units with a UnitId as column and selection is done in a form/query by WHERE UnitId='unit6'
.
On the other hand you may find a lot of tables, when you find out database-designers try not to duplicate data and may spread a “simple” address of a customer over several tables using a table for all Streets and a table of cities…
.
Actually most databases have also techniques for mass-updates and imports from csv etc., but these methods vary a bit. So you need to find out, what suites your need. Generally: Updating rows is usually quite efficient, columns can be more tedious.But there are special databases like DuckDB, wich are more efficient for this tasks (google for columnar database, if you like). Some of the “bigger” projects have different engines for storage, wich may be selected during design of thr database.
Is a database, obviously. https://www.youtube.com/watch?v=af9wagkAH6s
Find out the type of database and where it is stored on your computer. If there is some ODBC or JDBC driver for this database, you can connect a Base document to it.