[Solved] File structures and forms multilevel

[Ricky]

it has been a long time I did not work with database so need a little help to get one going!

i would like some files with keys
and need at least 3 or 4 levels of 1 to N

I need files as follow
1 - let says some Panel specs
2 - CB XY
3 - Cables length in multiple sections
with Boxes or connections

How can I do this 3 levels definitions for these data files
I can add one key in each file
then you do the relationships ?



now i see that Forms seems to be limited to 2 levels of file
so i guess that only way to have more level is to use a Query
can someone confirm this ?



Thanks for any feedback
have a nice day

[UnklDonald418]

You are limited to two levels on Base forms when using the the Form Design Wizard, but multi-level forms are easy to create in the Form Design/Edit mode.
Open your form in the Edit/Design mode and open the Form Navigator on the Form Design Toolbar usually found along the bottom of the form window.
In the Form Navigator pane right click on the form you want to add another level to, and select New->Form.
Right click on the newly created form and select Properties to open the Form Properties dialog.
On the General tab you can change the name of the form to something more meaningful.
On the Data tab you can select the Content type.
If you chose Table then on the Content line select a Table name from the drop down list.
If you chose Query then on the Content line select a Query from the list of predefined queries.
If you chose SQL command then either enter the text for a query or click on the ellipsis to open the Query Design dialog.
Then choose the appropriate fields for Link Master field, and Link Slave field.
Next add a control to your form.
If you didn't close the Form Properties dialog it will change to the Control Properties dialog otherwise right click on the new control and select Control to open the dialog.
On the Data tab select a Data field from the list of available fields.
On the General tab you can rename the control to something more meaningful.
Using this process you can enter as many levels as you need.

[Ricky]

i will try your method
and see if I can find a short video to demonstrate this

1 ) i already defined many fields in another database
is there a way to copy and paste fields definiton from one database to another one ?

any video tut showing this ?

2 ) when you define a file with a primary key
you can use a default integer field
but this is not field i need to sort my records
so is there a way to add another field like Name of a panel as a key and use that key for doing
a query and sort records ?

i will try to upload sample database when i get it to start to work


thanks
have a nice day

[UnklDonald418]

is there a way to copy and paste fields definiton from one database to another one ?

One way is to use Copy and Paste. Select the table in the source database and press <Ctrl>C then in the destination database select Tables and press <Ctrl>V. That will start a Copy table dialog that gives a number of options.

is there a way to add another field like Name of a panel as a key and use that key for doing a query and sort records ?

Add an ORDER BY statement to your query. Something like

Code: Select all

SELECT * FROM "Table"  ORDER BY "Name" ASC;

will sort by the field "Name" in alphabetic order or you can reverse the order by replacing ASC with DESC
You can also sort by multiple fields for instance

Code: Select all

SELECT * FROM "Table" ORDER BY "LastName" ASC, "FirstName" ASC;

[Ricky]

here is beginning of tables definitions

hope this make sense

image did not upload!
is there a minimum post before uploading files in forum ?
I can add it to pastall site instead with link

http://pasteall.org/pic/index.php?id=120397

Edit: 2023-12-23: Broken link disabled -- MrProgrammer, forum moderator

thanks

[Villeroy]

Try to connect a new Base document to that mysterious other database. What kind of database is it? Possibly you can simply work with that one.

What most users do not understand: Base is NOT a database. It is a minimalistic tool set to work with external databases and make them accessible from ODF documents. The whole Base component is a tiny extra to this office suite. It is shipped with an old version of HSQL so you can build simple relational databases from scratch, good enough for demos and educational purposes. The embedded HSQL 1.8 ist not the type of database that should be used for productive database applications.

[UnklDonald418]

Your tables appear to make sense, but in my experience using a Text field for the Primary Key often leads to problems at a later date. What in the beginning looks like a unique text field often turns out not to be as unique as originally thought, and then you are stuck with having to rebuild your table and possibly queries, forms and reports just to enter a new record. It is usually better to use an automatically generated Integer for the Primary Key.

Look at this Tutorial for the rules for uploading to this Forum.
viewtopic.php?f=74&t=8289

[Ricky]

i know pri key
it has to be unique and the field I selected will be even if it does not look like it and it has to be that cause it is what I want and need LOL

i did read a little the intro manuel for Base
but some parts are not that clear or difficult
mind you there are so many things to learn
and i never use that one before so a bit difficult to start

the database i want to do is very simple as a structure / relationships of data
just that it needs 3 levels of relationship

after that just a matter of doing some relatively simple query for a nice report
and it should do the list i need hopefully
or i will have to get MS suite with DATABASE which i have not touch in over 10 years!
this option is not really good cause i don't have a lot of need for database

I just only need this long list of cables i have to do with a nice report
i would do it in Calc but it does not really have a nice way to make a clean report as in the Database soft

i will study this this night and try to be back tomorrow
for other tips if possible

and thanks for your feedback

[Villeroy]

As already stated, Base is NOT a database. It is a bridge between databases and office documents. You can store things in some relational database, connect a Base document to it, add some meaningful queries and drag them into Writer or Calc. You can create simple Base reports which are just Writer tables embedded in the database document. Serial letters (aka mail merge, aka form letters)are Writer documents with database snippets in the text flow. If you want something good looking with a not necessarily tabular layout, you can install the report builder extension. I use Calc for all my reports since I can handle Calc, spreadsheets offer a lot of extra functionality and the layout capabilities are sufficient.

[robleyd]

it has to be unique and the field I selected will be even if it does not look like it and it has to be that cause it is what I want and need LOL

May I suggest a compromise that should cover both your and the database engine's needs? The database will work best with a primary key that is an integer field auto incrementing - that way you don't even have to enter values and remember where you were up to.

Then, you want a user defined value assigned to each record; add another field defined as Text and unique - in that you can enter your specific user defined value.

[Ricky]

sorry for the delay

I was able to build the tables with proper relationships - at least hope

and I build a custom form with the tables and defined the links

but still have to start testing this newcustom Form

I did follow a good video on custom form with sub form and sub sub form
which was very interesting and nicely explained.

but wondering why you need to add fields
and also table + scrolling bar

are the field needed to query the tables only?
and tables only for deleting or adding new records ?


the next thing will be 2 reports

first report will use only the last table so should be much easier
but still need to find way to make group and sum inside group
but will be back later on with that part

second report will have to use all the tables
so this might require some SQL query on 3 tables with the proper links
have to find other video tut on this subject

thanks for your patience
have a nice day

[UnklDonald418]

Table controls and individual field controls can do the same operations including display, edit and delete existing data or add new records. The differences are that individual controls display one record at a time while table controls can display multiple records, and a table control often has its own navigation bar while individual controls usually use the main navigation bar.

The Oracle/Sun Report Builder extension for Base provides many more options than the built in report builder. It can be downloaded from
https://extensions.openoffice.org/en/pr ... rt-builder
There is some documentation at
https://wiki.openoffice.org/wiki/SUN_Report_Builder

A search for “SQL joins tutorial” will get many choices for learning about joining multiple tables with a query.