How to populate a table when combo box value is selected?

Discuss the database features
Post Reply
hubaghdadi
Posts: 4
Joined: Sat Nov 03, 2012 11:45 pm

How to populate a table when combo box value is selected?

Post by hubaghdadi »

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.
OpenOffice 3.4.0 on OS X 10.8
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to populate a table when combo box value is selected

Post by Arineckaig »

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/vi ... 00&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.
Attachments
ClientsTrips.odb
Simple many-to-many example
(26.38 KiB) Downloaded 674 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
hubaghdadi
Posts: 4
Joined: Sat Nov 03, 2012 11:45 pm

Re: How to populate a table when combo box value is selected

Post by hubaghdadi »

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!
Attachments
Hire.odb
(12.28 KiB) Downloaded 485 times
OpenOffice 3.4.0 on OS X 10.8
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to populate a table when combo box value is selected

Post by Arineckaig »

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.
Last edited by Arineckaig on Sun Nov 04, 2012 9:25 pm, edited 1 time in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
hubaghdadi
Posts: 4
Joined: Sat Nov 03, 2012 11:45 pm

Re: How to populate a table when combo box value is selected

Post by hubaghdadi »

The many to many between Customer and Equipment is done in Hire_Equipment table, not Hire.
OpenOffice 3.4.0 on OS X 10.8
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to populate a table when combo box value is selected

Post by Arineckaig »

You may find some pointers in the form document in the attached file.
Attachments
Hire-DEMOform.odb
Added crude DEMO form document
(20.54 KiB) Downloaded 556 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
hubaghdadi
Posts: 4
Joined: Sat Nov 03, 2012 11:45 pm

Re: How to populate a table when combo box value is selected

Post by hubaghdadi »

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)?
OpenOffice 3.4.0 on OS X 10.8
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: How to populate a table when combo box value is selected

Post by DACM »

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
...
Attachments
Hire (button-driven).odb
List Box filter 2 SubForms using refresh 'button'
(27.77 KiB) Downloaded 930 times
Hire (macro-driven).odb
List Box filter 2 SubForms using refresh 'macro'
(28.87 KiB) Downloaded 941 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to populate a table when combo box value is selected

Post by Arineckaig »

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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to populate a table when combo box value is selected

Post by Arineckaig »

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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Post Reply