How can I fill the fields of a table in LibreOffice Base automatically?

Question

I have a database which contains a table of cellphones. Let's say that every cellphone has 10 fields. In order to fill or modify the table I will have several forms available for the user. However, I don't want the user to modify all 10 fields every time. I want him to just give information about 4 of the fields and the rest of them will be automatically filled or modified by a program. Does someone know how to do that? :)

Answer 1

While possible with triggers, macros, or other coding, it's generally bad database practice to have calculated fields or duplicate data stored in tables. Related data should be stored through relationships between tables and displayed in a query, not directly in the table.

So if, say, each store only sells a single color of phone, you would have the user enter only the store. You would have another table that showed the relationship between store name and phone color. Then when you wanted a list of users and their phone colors, you would write a query that looks at the table list of users and where they bought their phones and joins it to the list of stores and what colors they sell.

My advice has three tiers:

• Almost certainly best - redesign your database to be more normalized, meaning use relationships between tables to prevent the need for duplicate data.
• If you decide you need macros, a good resource for working with OpenOffice macros is Andrew Pitonyak's book OpenOffice Macros Explained (a free download from his website).
• SQL Triggers are often a cleaner way of doing this (compared to macros) but are not supported by the old database engine that is the Base default. (Base itself only handles queries, forms, and reports. The tables are handled by separate software, which by default is an old version 1.8 of HyperSQL Database or HSQLDB that is "embedded" inside Base.) You would need to upgrade to a newer database software. Instructions on upgrading to HSQLDB 2.3 are in this thread: [Tutorial] Splitting an "embedded HSQL database"