How to populate a table when combo box value is selected?
-
- Posts: 4
- Joined: Sat Nov 03, 2012 11:45 pm
How to populate a table when combo box value is selected?
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.
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
-
- 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
Welcome to the forum.
Where you say:
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.
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.I want to create a form ....... that will list all the equipments that the customer hired.
Where you say:
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.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.
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 679 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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
-
- Posts: 4
- Joined: Sat Nov 03, 2012 11:45 pm
Re: How to populate a table when combo box value is selected
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!
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 491 times
OpenOffice 3.4.0 on OS X 10.8
-
- 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
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.
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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
-
- Posts: 4
- Joined: Sat Nov 03, 2012 11:45 pm
Re: How to populate a table when combo box value is selected
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
-
- 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
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 564 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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
-
- Posts: 4
- Joined: Sat Nov 03, 2012 11:45 pm
Re: How to populate a table when combo box value is selected
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)?
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
Re: How to populate a table when combo box value is selected
Arineckaig has also wisely given you a relatively simple example to examine as you get up to speed on SubForm filtering.hubaghdadi wrote:I want to create a form that uses acombo boxlist box to pick a customer name...and...list all the equipments that the customer hired.
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 938 times
-
- Hire (macro-driven).odb
- List Box filter 2 SubForms using refresh 'macro'
- (28.87 KiB) Downloaded 947 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
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
-
- 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
While much may depend on how you are creating the relationships, I regret a constructive answer is above my pay grade!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)?
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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
-
- 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
My apologies but not thinking straight this morning. In my previous post where I said:
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.
the explanation is to be found in the error message:that issue should not apply to creating a link between the Customer and Membership tables.
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.Integrity constraint violation - no parent 3, table: Customer in statement
[ALTER TABLE "Customer" ADD FOREIGN KEY ("membership_id") REFERENCES "Membership"]
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
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB