[Solved] How to create a form linked to 3 tables?

[astertix]

I wish to do a database about products from a store.

This store has products from different manufacturers. A manufacturer has different products. One product can have many uses, or a single use let say pencil with different uses (coloring, carpenter, design). A single product can be produced by one or many manufacturers, we can have writing paper from 3 manufacturers.
I use oobase HSQL database engine
I have divided data into separate tables

table one named manufacturers
id_man bigint (autovalue/yes) format example "man"@
manufacturer text (varchar) entry requiered/yes

table two named products
id_prod bigint (autovalue/yes) format example "prd"@
product text (varchar) entry requiered/yes
engl_name text (varchar) entry requiered/yes
franc_name text (varchar) entry requiered/yes
id_man bigint (this should be the id_man from the first table, i don't know how to make this colect data from the first table field as in access)

table tree named uses

id_uses bigint (autovalue/yes) format example "uss"@
uses text (varchar) entry requiered/yes
id_prod bigint related with the id field from table 2

There will be more tables related to price, purchase date, EAN, and other store stock related databeses, but for the moment i need to understand what to do with these 3 tables.


What i want to do is:

• To create for the start a form that gives me the ability to write new records in each table
  To mentain the linking, for the moment even when i creat a form using form wizard for a single table it does not display all the field when i navigate. I have created one for manufacturer table and it display only the manufacturer name and not display the appropriate manufacturer id in the id field of the form, remaining at the first ID even thow i can navigate to the end of file in the manufacturer field of the form.
  
  When i write in a field of the form let say pencil that form to be able to display that record so I can move to the next field, and if there is none of such product, to record it in the appropriate table.
  I would try a referential table in which to put just the id fields from two tables but i don't know how to link them with the real tables.

I read a lot in the OObase forum. I read in the SQL forum. I am a beginner in the database field.

But for the moment I don't even know what i am searching to find the answer, that's why I am asking you to help me if you can.

[Villeroy]

Same answer as here:
Field updates from a multiple table view?
You can't (easily) have everything at once in the same form. First you have to add the preceeding data before you can edit the depending ones.

[voobase]

Hi Asterix,

I designed an example database for someone with a similar circumstance on the other forum. I never got any further responses from him and not wanting to waste a good example I have modified it a bit and it is attached to the bottom of this post.

Have a read of it to see what I have written as far as what it does. On the other forum is a more detailed explanation, here is a brief one.

The example database was designed for entering in stock information and to automatically generate part numbers in a specific format, which I achieved in probably a fairly unconventional way. The form heir achy was Category (mainform) SubCategory (subform) and PartNumber (subsubform). I designed the form in two sections to show two different ways you can work with a form. On the left the three sections are all related by linking the master fields and slave fields in the form properties. On the right I utilized using one listbox to narrow the list in a second listbox as a means to quickly finding the part number. It also used a static table to bounce the bound fields of these listbox's off so the part number form could link to something (not sure if this is the best way to do things but it works). Anyway, the link to the other forum question and the original example database is...

http://www.oooforum.org/forum/viewtopic.phtml?t=69534

I thought I would have a quick go at modifying my original example to more suit your needs so I changed some of the labels around and added an additional listbox for entering the generic "uses" you were talking about. I also included a separate form (and a new table) for the purpose of entering these additional "uses". (The table goes on to populate the listbox). This separate form also references a query so you can look at one of your "use" categories and see which products were selected in the primary form to facilatate that use, sort of like looking at things from the other way. The great thing about using the listbox like this is that your list of "uses" for each product will contain definite "use" categories with an individual primark key that is stored in a table that relates to each of your products. This makes it possible to use it in a query as (I mentioned above) as it uses consistant data.

To fully understand how it all goes together you may need to look at the macro file once you have loaded it up and go back to the forms to find the events which I am using to trigger them. You will also have to use the form navigator to look at the structure of the forms and to help inspect their properties.

Now I didn't have time to go and change all my table names to reflect that we are now talking about manufacturers, products and parts so you might find it useful to write this down on a piece of paper to help you when trying to figure out how things work...

Category = Manufacturer
Sub Category = Products
Part = Uses

There might also be a couple of unconventional things you find that keep you going, for instance I did not link the mainform and subform using primary key to foreign key but used primary key +1 to foreign key. This was to help with the automatic part number generation. which you will still see in action but probably not find much use as it is with your "uses" form. I have also left some foreign key values showing on the form (as this helps with fault finding).

Anyway hope it gives you a start. The latest example database is attached below. Don't forget to also load the macro file using ALT F11>Organiser>Import <scriptfile> (it is slightly different to the macro file on the other forum). Also don't forget to register the database by going to tools>options>openoffice.org base and selecting where you put the database.

Cheers

Voo

Parts_Macros.zip

(24.67 KiB) Downloaded 532 times

Parts_dB_Stock.odb

(26.51 KiB) Downloaded 563 times

[astertix]

I will work on this particular dataset from your example to get a grasp at what does a form mean, how to use it and how to personalize it to suite my needs
Being a beginner i did not understood the procedure to put two main form in the Category form from your database example.

Edit: Hagar: removed voobase's post quoted, doesn't add anything to the discussion.

[astertix]

Same answer as here:
Field updates from a multiple table view?
You can't (easily) have everything at once in the same form. First you have to add the preceeding data before you can edit the depending ones.

I don't want to go to the easy way. I don't want a fish, I want to learn fishing myself, that's why i need help from the persons who knows how to make a database from scractch to be useful for the user who does not even want to know how data is stored in his database. I am enthusiastic, that i will find the answers needed.

[voobase]

Being a beginner i did not understood the procedure to put two main form in the Category form from your database example.

It is possible to have several mainforms with their associated subforms in your "writer" doc with each of these doing different things. The best way to see this while working on your form in design mode is to use the "Form Navigator" button which gives you a tree like view of all the forms and subforms. To turn on the "Form Navigator", go to the "Form Design" toolbar and press the button three to the right of the "Design Mode on/off" button. To add another form from within the "Form Navigator" simply right click and select "form".

I am enthusiastic, that i will find the answers needed.

Yes it does happen. It takes a while, but as you learn things only become easier. The problem is that as you learn you only want to do more and you think of new things and then you go searching for more and more ways... Eventually you sort of find an equilibrium.

I don't want to go to the easy way. I don't want a fish, I want to learn fishing myself, that's why i need help from the persons who knows how to make a database from scractch to be useful for the user who does not even want to know how data is stored in his database.

I think what Villeroy was leading towards is that sometimes it is best to design your work around several separate data entry and display pages. It might be more practical and logical this way when there is often separate tasks to be performed in the overall functioning of the database. In the example database I probably got a bit busy with the main data entry page. I did need however, to construct a totally separate data entry page to populate the "uses" listbox and display a query that reorganizes the data so the "products" also show that relate to each of the "uses" that have already been entered. You also need to think about who will be using the database because you don't want data being changed accidentally, so you may wish to make some parts read only.

Voo

[Villeroy]

Same answer as here:
Field updates from a multiple table view?
You can't (easily) have everything at once in the same form. First you have to add the preceeding data before you can edit the depending ones.

I don't want to go to the easy way. I don't want a fish, I want to learn fishing myself, that's why i need help from the persons who knows how to make a database from scractch to be useful for the user who does not even want to know how data is stored in his database. I am enthusiastic, that i will find the answers needed.

Well, go ahead. Create your first relational database. Implement one-to-many relations and forms. Read some online resources about relations and normalization in relational databases. Then you may understand that all databases are extremely restrictive by design and by intention. A database user who does not care, is like an office worker who constantly establishes contacts in wrong order (first order, then negotiate the price before having permission from the upper floor). You may be able to create an elegant form to hide the dependencies. But first you need to understand what needs to be hiden and why. Go ahead and ask more concrete questions then.
btw: I don't recommend Base for database for database creation. It's too alpha (clumsy, buggy, not even feature complete). IMHO, the concept of embedded hsqldb failed. But it is still good enough to import all kinds of foreign databases into Calc and Writer.

[astertix]

Thanks for your reply Villeroy
I have to prepare this database for an exam. The final product should be something that can show data in a neat and clear order.
My speciality has nothing to do with informatics. I study something else, agriculture.
I have to know in a farm how to create my own database, for different situations.
I am aware that is not the best solution to use Base, but is the only free option.
There would be also MySQL workshop, but I have to learn it from the start, and for the moment there is no time to do that. I have started my database in Access, i have moved to Base, and none of them is finished.
The documentation about making database in Base is scarce, the documentation for SQL is too exhaustive, and I am lost in all that language, when i don=t know what the procedure are and which of them i need.
I have read about JOIN, about SELECT, about QUERRY, about forms and subforms, about relational database, about consistency about normalization and I am confused. I feel like I am reading the dictionary for a foreign language trying to speak fluently that language.

[astertix]

I will consider the advice of Villeroy to learn some serious database language if i would like to do something professional in this field.
Thanks to voobase, I have found the button of form navigator tomorrow I will tell the results at wich I have arrived

[Villeroy]

There are things to learn you can not learn in a do-it-yourself manner. Keep some of the theory in mind, even if you don't understand everything. You have MS Access? Get the "Northwind.mdb". It is self-documenting, huge (more or less complete) and fairly simple (very few macros).
Best OOo database I have seen so far: http://www.ninthavenue.com.au/products/ ... /downloads

I will consider the advice of Villeroy to learn some serious database language

SQL is the one common language of all relational databases. It is extremely simple and as close to human language as can be. For instance it is much more "human" than spreadsheet formulas. The issue with Base's built-in query designer is: It creates SELECT queries only (queries that return recombined rows and columns). It does not support the full set of features (JOIN for instance). The queries that it is able to create are so braindead simple that you can type the SQL as fast than you click in the designer. In the long run you get used to SQL anyway.

[voobase]

A good simple tutorial on SQL can be found at the W3schools.com website...

http://www.w3schools.com/default.asp

V

[astertix]

There are things to learn you can not learn in a do-it-yourself manner. Keep some of the theory in mind, even if you don't understand everything. You have MS Access? Get the "Northwind.mdb". It is self-documenting, huge (more or less complete) and fairly simple (very few macros).
Best OOo database I have seen so far: http://www.ninthavenue.com.au/products/ ... /downloads

I will consider the advice of Villeroy to learn some serious database language

SQL is the one common language of all relational databases. It is extremely simple and as close to human language as can be. For instance it is much more "human" than spreadsheet formulas. The issue with Base's built-in query designer is: It creates SELECT queries only (queries that return recombined rows and columns). It does not support the full set of features (JOIN for instance). The queries that it is able to create are so braindead simple that you can type the SQL as fast than you click in the designer. In the long run you get used to SQL anyway.

Thank you for your help. I have found some of the answer needed

[astertix]

A good simple tutorial on SQL can be found at the W3schools.com website...

http://www.w3schools.com/default.asp

V

Thank you for your help. I consider solved my question for the moment