For two days now, I have been trying to set up a relational database. I have two tables and two forms created, but I cannot change or delete the index on the ID field. I am at a complete loss and need some help with this. In addition, I am not able to create two drop-down lists, each with a choice of Yes or No. In the control properties, the field of “List Entries” is greyed out. I could send the 2 Tables and Forms so that you could figure out how I messed this up.
First: Try to create a first database like this: Chapter 1 Introducing Base. It’s an example with relations between 3 tables and helps for getting a form and a report working for this example.
Before struggling with forms, you have to build tables and indices according to the rules of database normalization. The forms, subforms and their controls should follow the underlying structure or normalized tables and indices.
A list box is a GUI element that writes another table’s primary key into a foreign key field by selecting a human-readable name. For Yes/No values (booleans) the check box control is adequate.
It is very difficult to modify keys of tables that are populated with data.
https://forum.openoffice.org/en/forum/viewforum.php?f=83
https://forum.openoffice.org/en/forum/viewtopic.php?t=56006
https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=40444
rherhood,
I have been there my friend, messed things up a lot. The best thing to do is don’t give up easily and try recreating the tables and forms using methods you may have learned from examples or guides available to you since the mess up. If we just do it all for you, you learn nothing and will remain dependant on this forum to “get anything done”. Include some basic info about your system such as:
- Type of relational database to which LO Base connects (embedded or split HSQLDB, MySql, Firebird, etc…) This is displayed in the status bar of the main LO window when odb is open.
- Software and Device basic info (LO version, OS).
- A stripped down sample of the odb or even a screenshot of all the tables in the relationship tool view.
- A simple description of the workflow intended for the database objects in question, along with previous methods attempted to rectify the problem.
Here are some of my own basic tennants when creating tables and forms. - For convenience and time efficiency, a table should be created with the first two fields being
1 “refText” as type text(varchar) - Use your own field naming preference. (“item”,“name”, etc…) Field names are case sensitive.
2 “ID” as type Integer, Primary Key. - ID is typical and efficient as a primary key name. However, use your own preference. (“ItemID”, “Item_ID”) Try using caps in primary key names to maintain “uniqueness”. (In a related child table, the foreign key integer field could be “itemID” or “item_id”, lower case) Table field names are case sensitive and will be important when creating queries or writing sql queries later on.
3 Remaining table field order is mostly irrelevant, except the default column order of table controls. - Do not store boolean data as field type Yes/No(boolean) in any table. This data is much more efficiently stored in tables as field type Integer. Checkboxes and option buttons use integers to determine their “state”, and the table will achieve much higher performance over using the Yes/No(boolean) type. The only visual difference is how the field (example “isRetired”) data appears in the table: an integer (1 = true, null or 0 = false) - not as a checkbox.
You will see how these basic considerations impact your time efficiency and convenience, especially when creating listboxes and desiring higher standards of database performance. - Gain a firm understanding of the differences between a listbox and a combobox, and which situations require the use of one over the other. Referential databases rely heavily on the frequent use of these two objects.
And before building the tables, write down all the data that need to be stored in that database, like:
Invoices: Date, Cient-ID, Primary Key
Clients: Name, Contact data, …, Primary Key)
Vendors: Name, Contact data, …, Primary Key)
Articles: Name, No, Description, Vendor-ID, …, Primary Key
Prices: Article-ID, Date, Price, Primary Key=(Article-ID, Date)
Sales: Article-ID, Invoice-ID, Quantity, Primary Key=(Article-ID, Invoice-ID)
and many more tables as demonstrated in [Example] Invoice Forms (without macros).
Learning the simple SQL syntax for CREATE TABLE and ALTER TABLE gives you the maximum of flexibility, because the Base GUI for table design fails in many cases where simple SQL statements simply either work or give a meaningful error message in case of failure.
In a second step, I build simple 2-column queries for listboxes: SELECT "Name", "PrimaryKey" FROM "Articles" ORDER BY "Name" ASC
When you start a new form using the form wizard, the result is a single main form with one subform, which is not sufficient in most practical cases, but it is a start. The form wizard does not create any listboxes, so you have to right-click>replace the numeric/formatted fields representing the foreign keys with listboxes and then point the listboxes to their respective listbox queries.
The most important tool when editing a form in design mode is the form navigator (menu:Form>Form Navigator). Among other things, it lets you create hierarchies of forms and (sub-)subforms.
Libreoffice provides a basic walkthrough to help create your own database, tables, forms, queries, and reports.
- Open Libreoffice and click “Base Database” in the left pane.
- Create a new database - HSQLDB Embedded - click Next >
- No, do not register - Create tables using the table wizard - click Finish
- Select desired save directory, name and save the odb file.
- Follow the prompts to create the first table from example field lists. Remember the first two fields should always be first a text field, and second the ID integer field.
- When saving, give it a name (“Items”) and Save.
- Create another table (“SubItems”) by Use Wizard to Create Table in the database Tables. Follow the prompts.
- After the tables are created, under Tools in main LO window - Relationships - Add tables to UI - create relationship between parent table primary key (“ItemID”) and the child table foreign key (“item_id”). Save and close the relationship UI window, then also Save the odb! (ctrl+s)
- For creating a Form based upon the table, switch to the database Forms , then Use Wizard to Create Form.
1 Select the parent table from dropdown (“Items”), include all available fields (>>), click Next
2 Add Subform - Subform based on existing relation - select related child table (“SubItems”), include all available fields (>>), Next >, relate “ItemID” with “item_id” if prompted, click Finish! (or Next > for more design options. Read and follow prompts.)
3 Name the form (“ItemList”) and Save and close the form, then also Save the odb!
If you properly followed the process ther should now be a working Form named “ItemList” in the database Forms list.
- Create a new database document with an embedded HSQL database.
- Open Create_Database_Tables1.ott (39.7 KB), leave everything as is and copy the SQL code from page 3 into a plain text editor (or to Writer as unformatted text) and then the plain text from the editor. Close the template.
- Base menu:Tools>SQL… paste and execute.
- menu:View>Refresh Tables. Now you have 4 most simple lists “TBL_A”, … “TBL_D” and a 5th table “TBL_BC” linking B items to C items together with a one-to-many relation between A and B, a many-to-many relation between B and C and a one-to-one relation between C and D.
- Create 4 listbox queries for the A,B,C,D items. The one for A is
SELECT "Name", "ID" FROM "TBL_A" ORDER BY "Name" ASC. For the other three, make copies and change the letter in the table name. Field names are the same. - Right-click TBL_B>“Form Wizard…” and create a form with a subform based on the relation to “TBL_BC”. The subform needs to be a table grid!
- Right-click>Replace the AID numeric field with a listbox and bind the listbox to the A query.
- RIght-click>Replace the numeric column in the table grid with a listbox and bind it to the C query.
- Add a subform to the TBL_BC subform, linked to TBL_C showing the selected C item linked through the common ID (TBL_BC.CID → TBL_C.ID).
- Add a subform to the TBL_C subform, linked to TBL_D showing the selected D item (if any) linked through the common ID (TBL_C.ID → TBL_D.CID).
- Link the navigation bar of the subforms to “Parent”, so the bar always refers to the root form.
The result looks like this:
New Database.odb (39.3 KB) and took me less time than writing this description.