Apache OpenOffice Community Forum

Hi,

I have this tables structures:

Table Equipment
equip_id
name

Table Customer
customer_id

Table Hire
customer_id
equip_id

I want to create a form that uses a combo box to pick a customer name from, and a control (table maybe?) that will list all the equipments that the customer hired.

How to achieve that? Is it possible to?
I know that a code should be written when a customer's name is picked then execute a query and then populate the table. But I don't know how.

Thanks you all for help and time.

Welcome to the forum.

I want to create a form ....... that will list all the equipments that the customer hired.

I suggest avoidance of a combobox for selection of a particular record. Where you are seeking to list all the equipment that a customer has hired, I suggest you make use of the relationship between a main data form in Base and its linked sub-form: selection of any particular Customer record in the main form will display in the sub-form all Equipmet records linked to that Customer.
Where you say:

I know that a code should be written when a customer's name is picked then execute a query and then populate the table. But I don't know how.

In effect the Base form-subform relationship does this automatically. It is somewhat confusingly described in the Base Help file, but once comprehended greatly simplifies the display of filtered records.

You may well find it productive to look at Villeroy's
http://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=40444
items #5 and #6 in particular give a good introduction to the effective use of combo and list boxes in Base.

I have also attached a simple demo file, where there is a similar many-to-many relationship between Clients and Trips and the same principles apply.

Please come back if further clarification would help.

Hi,
Thank you so much for the great help!

I tried to figure out how you create many-to-many form/subform but I'm not able to reproduce it for my case.

Would you please instruct me how to create it?
I'm attaching my Base file.

What I want to do is to list all previous hires. This can be done with Customer/Hire_Equipment/Equipment
Previous hires = Hire_Equipment.expected_return_date < CURDATE

Thank you again!

First, the fields in three of the tables in your uploaded database 'Hire.odb' - "Equipment", "Customer", and "Hire" - differ significantly from the tables that you described in your first post. Thus, you do not appear now to have set-up a many-to-many relationship between Customers and Equipment, and my previous reply is no longer relevant.

Secondly, you latest post refers now to three tables - Customer, Hire_Equipment and Equipment - where the relationships between them are ill defined.

Thirdly, I dare to suggest you need initially to give some careful thought to the design of your database: in particular, to the principles of normalisation and data dependency that must underlie any Relational Database. I would strongly advise you you have a look at an introductory tutorial that can be downloaded from:
http://www.scribd.com/doc/48635562/Base-Tutorial-OOo

Finally, only then will it be productive to consider how best to display data sourced from those tables.

Sorry not to be more helpful but until the foundations for an RDMS have been established adding superstructure will soon or or later lead to trouble.

The many to many between Customer and Equipment is done in Hire_Equipment table, not Hire.

You may find some pointers in the form document in the attached file.

Thanks!!

When trying to create a relationship link between Equipment and Supplier_Equipment, Customer and Membership ; Why OpenOffice is complaining about integrity constraints (and no relationship link is created)?

hubaghdadi wrote:I want to create a form that uses a combo box list box to pick a customer name...and...list all the equipments that the customer hired.

Arineckaig has also wisely given you a relatively simple example to examine as you get up to speed on SubForm filtering.

Since you've asked if your idea of customer-selection and record-retrieval can be done with a List Box, I thought I'd demo that for you as well. I'd rather not step you through the process of creating such a Form step-by-step, but I will refer you to the following posts which outline the design concepts in some detail:
Filter table concept
[Example #1] Filter/Search with Forms (leveraging SubForms)
[Example] Invoice (without macros)
[Example] Text Box, Table Control and List Box filtering SubForms
[Example] List Box filtering SubForms
...

When trying to create a relationship link between Equipment and Supplier_Equipment, Customer and Membership ; Why OpenOffice is complaining about integrity constraints (and no relationship link is created)?

While much may depend on how you are creating the relationships, I regret a constructive answer is above my pay grade!

Hopefully one of the forum experts on Base and SQL will see your post and give you some pointers.

FWIW, I try to avoid the complexity of multiple PK fields in any one table, but that issue should not apply to creating a link between the Customer and Membership tables.

My apologies but not thinking straight this morning. In my previous post where I said:

that issue should not apply to creating a link between the Customer and Membership tables.

the explanation is to be found in the error message:

Integrity constraint violation - no parent 3, table: Customer in statement
[ALTER TABLE "Customer" ADD FOREIGN KEY ("membership_id") REFERENCES "Membership"]

If you check the 'parent' table for the relationship you are trying to create, the Membership table has no record with the PK field holding the value of 3, but there is such a value in the FK field of one of the records in the Customer table. Thus, Base recognises a potential integrity problem and aborts the intended link.

Similarly with the aborted link between the Equipment and Supplier_Equipment tables, you are getting the error message: "no parent 24". This means the Equipment table lacks a record with a PK field holding the value 24, whereas the Supplier_Equipment table has three records where the intended FK field holds such a value.