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.
jtm,
Iām working on thisā¦ and I will update this post as I progress!
Goal: Import the xlsx spreadsheets to LO Base table to use in forms and queries.
Updated black friday versionā¦ see if this helps!
UNCP.odb (339.9 KB)
Nope. 2,147,483,647 (231 - 1) is the maximum string length for a JAVA string as used by the underlying HSQLDB. For unlimited strings, there is the Memo field type.
Data types should be as large as necessary, as small as possible.
P.S. I see, you are ignoring the bottom part of the table design window where you can define the length of text fields and make them mandatory (disallow empty values).