[Solved] Restricting items in a listbox with another

Discuss the database features
Post Reply
User avatar
Aquarina
Posts: 58
Joined: Sat Oct 24, 2009 5:15 am

[Solved] Restricting items in a listbox with another

Post by Aquarina »

I have a table with lots of entryes
I want to enter items from that table in a form but looking through all of them is a pain, so I'd like to restrict the ones that get shown by the listbox using another listbox that lists the categories of items.
I've tryed with a sample database and copied basic code for 2 days... but can't get it to work!
Can you please help me?

In this database example I have names of people with which I'd like to make apointments... try i out.
Last edited by Aquarina on Wed Sep 15, 2010 9:47 am, edited 1 time in total.
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Restricting the number of items in a listbox with anothe

Post by Arineckaig »

I want to enter items from that table in a form but looking through all of them is a pain, so I'd like to restrict the ones that get shown by the listbox using another listbox that lists the categories of items
I am not sure if the database you posted is a suitable example but I have added a form document to that database which may be what you are seeking. I have attached the amended version.

For an fuller explanation of this method of filtering I would refer you to some notes I prepared at:
http://dl.dropbox.com/u/10552709/FilterExamples.zip
Hopefully somewhere in that lengthy note you will find some pointers.
Last edited by Arineckaig on Sun Oct 10, 2010 5:14 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
User avatar
Aquarina
Posts: 58
Joined: Sat Oct 24, 2009 5:15 am

Re: Restricting the number of items in a listbox with anothe

Post by Aquarina »

Thank you Arineckaig...
This is what you get when you do things late at night too tired to think!
I am realy sorry I completely messed up. Here's why:
The file I uploaded was not the one intended to upload. To redeam myself I tryed to build a sample one a little bit more clean. Unfortunately too large to upload here :-/

http://drop.io/hidden/0ggrkn3h0fdymo/asset/bmFtZXMtb2Ri

I still didn't have time to read all the documentation you kindly sent me, but I've been reading all through this forum on various posts but all seem to use table control, as you do. I'd realy like to use a dropdown box or a listbox.
Last edited by Aquarina on Sun Sep 12, 2010 11:16 am, edited 1 time in total.
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Restricting the number of items in a listbox with anothe

Post by Arineckaig »

I'd realy like to use a dropdown box or a listbox.
It is certainly possible to use a drop down box, and you will have seen that method described (alas at some length) in the download explanation. As, however, a selection from a List or Combo box does not move the record pointer in the record set it does not cause an automatic refresh of a linked sub-form. Instead it requires the data form (whether a sub-form or a separate form) that is to be filtered to be refreshed manually: for example, by clicking a suitable button. Of course that refresh can be done by a macro triggered by an event from the selection. In my limited experience there is then significant potential for unreliability, unless great care is taken to select the correct event to trigger the refresh. In short, a button is reliable and no great extra effort for the user.
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
User avatar
Aquarina
Posts: 58
Joined: Sat Oct 24, 2009 5:15 am

Re: Restricting the number of items in a listbox with anothe

Post by Aquarina »

Well, I still didn't figure it out, but you documentation defenetly seems to point me in the right direction. In conjunction with the rest of the documentation in this formum, I'm sure I'll figure it out.
Thank you once again.
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Restricting the number of items in a listbox with anothe

Post by Arineckaig »

I have now had a chance to look at your database, but have several questions.

I am having some difficulty in understanding the design of your database. Clearly each of the "groups" has many "names", but it would appear that no "names" belongs to more than one group. Thus it appears to be a one to many relationship and the "names-groups" table is redundant. The "meetings" table has only two records. so it is not easy to appreciate its relationships. I presume one "names" will have many "meetings" dates, but is to be expected that one "meetings" date will have more than one "names"? If so you will need a "names-dates" linking table to handle the many to many relationship?

Am I right to assume that the "V.1" form document is your ultimate aim?
Am I right to suspect your "groups" have more significance in their own right than just representing the initial letter of the "names"?
My apologies for coming back with so many questions but any suggestions I could make would be pointless without a better understanding of the design of your large database.
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
User avatar
Aquarina
Posts: 58
Joined: Sat Oct 24, 2009 5:15 am

Re: Restricting the number of items in a listbox with anothe

Post by Aquarina »

Arineckaig wrote:I am having some difficulty in understanding the design of your database.
No surprise :-/ I'm just now trying to make some sense of databases.
Arineckaig wrote:Clearly each of the "groups" has many "names", but it would appear that no "names" belongs to more than one group. Thus it appears to be a one to many relationship and the "names-groups" table is redundant.
That's exactly that. One name has one group. One group has many names. The "names-groups" table states what names belongs in what group. I don't understand why it is redundant. I can imagine I could have had a "group" field in the "names" table, but... can't it be done this way?
Arineckaig wrote:The "meetings" table has only two records. so it is not easy to appreciate its relationships. I presume one "names" will have many "meetings" dates, but is to be expected that one "meetings" date will have more than one "names"? If so you will need a "names-dates" linking table to handle the many to many relationship?
Well... the "meetings" table is that "names-dates" table you mention. Imagine you're a doctor and those names (the clients) consult you. You register that on that "meetings" table so you don't forget what you did.
I believe you coult take a look at the relationships tool where, by the way, I forgot to establish the relationship 1 "names.id" to many "meetings.name". That could explain a little bit more.
Arineckaig wrote:Am I right to assume that the "V.1" form document is your ultimate aim?
Yes, "V.1" is the design I would like to have. It should let me: 1 - enter a date in the meetings table, 2 - filter the names apearing on the next listbox. 3 - enter a name in the meetings table in the same record as the date I just entered.
Arineckaig wrote:Am I right to suspect your "groups" have more significance in their own right than just representing the initial letter of the "names"?
My apologies for coming back with so many questions but any suggestions I could make would be pointless without a better understanding of the design of your large database.
This is just a sample database, but in my real one, yes. It's meant to sort topics out of subjects just like, in here, we sot names out of their initial letters.

...Hope my english is ok, also! :-)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Restricting the number of items in a listbox with anothe

Post by Villeroy »

city_country.odb shows a simple subform model where record sets are filtered by means of the selected entry in the parent form. One form lets you edit the relation (assign a new citie's country), the other form shows the cities of a selected country. For 150 countries and thousands of cities this is quite usable.

shopping.odb implements a many-to-many relation (each shop has many items) with 2 sectors clothing and fruit. The list boxes in a shop's product lists are filtered by one record set in the top-level form.

Using the built-in form filter you can use a list box, combo box, text control as a filtering list box. Text controls have a property "Filter Proposal". WIth this option set, you push the filter button, select an entry from a list box that used to be the text box and apply the filter.
I fixed your Assets.odb so it works with a one-to-many relation (each model belongs to one brand) and the form based filter works with the text box (asset) and the list box (brand) likewise.
Attachments
assets_2.odb
(13.27 KiB) Downloaded 737 times
shopping.odb
many-to-many with filtered list boxes
(26.63 KiB) Downloaded 866 times
city_country.odb
filter row set of cities by selected coutry record
(19.3 KiB) Downloaded 1248 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Aquarina
Posts: 58
Joined: Sat Oct 24, 2009 5:15 am

Re: Restricting the number of items in a listbox with anothe

Post by Aquarina »

I, now, have too much material to deal with! Crucial timing in work also. let's see if I can handle all of that.
As you can imagine, I have been reading, mainly, posts from you, kabing and the neoofice "To Base and Back Again" docs.
I'm really thankfull to you all.
The irony of all of this is I have too much work to do right now and this is meant to be used in work... :-P
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Restricting the number of items in a listbox with anothe

Post by Arineckaig »

I, now, have too much material to deal with! Crucial timing in work also.
I know the problem. FWIW I have made a number of revisions to your database file to demonstrate what can be done. I offer this not as a solution but as an practical example to assist the tedious task of absorbing theoretical descriptions. It can be downloaded from:
http://dl.dropbox.com/u/10552709/NamesRevised.odb
The key addition is the form document "V.0-Amended". The result is far from ideal, but with more than 27,000 records WHERE clauses in frequent repeated SQL selects can cause excessive delays. The form document indicates the order of actions required by the user: clicking a button after selecting from the each of first two list boxes is necessary I regret. Due to the delay in filling the names list box after a group has been selected is likely to be even more frustrating if the user were not to have initiated the action. Any delay of more than a second or two is too easily seen as a program failure.

You will see I have added a "groupID" field to the "names" table to reflect the one to many relationship between groups and names. One effect has been to reduce the size of the .odb by more than 50%. In the "V.0-Amended" form document I have chosen to show the final "meetings" data form in a table/grid control: it could easily be changed to show one record at a time if so preferred.

The embedded HSQL database engine is not best suited for a database with this number of potential records: it is too easy for the file to become corrupted if there should any glitch when the data is compacted after having been expanded into memory. If serious use is to be made of the data, I strongly advise a separate back end such as MySQL.
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
User avatar
Aquarina
Posts: 58
Joined: Sat Oct 24, 2009 5:15 am

Re: Restricting the number of items in a listbox with anothe

Post by Aquarina »

Ok.
I've spent some time now studying the material you provided, Arineckaig, and I think I now understand one or two concepts that I just didn't have. Now I think it's just a matter of playing with it a little bit more and make use of what I just learned.
I'll mark this subject as "solved" as I think the explanations ar (more than) sufficient, even if it'll take some time for me to absorb it all.
Your explanations where realy helpfull and I am realy greatfull for the time you spent explaining key concepts to someone tha "just doesn't have a clue" about how databases work. Realy great work!
You too Villeroy. Keep on this great work of help.
mattia83
Posts: 36
Joined: Thu Nov 18, 2010 11:29 am

Re: Restricting the number of items in a listbox with anothe

Post by mattia83 »

Villeroy wrote:city_country.odb shows a simple subform model where record sets are filtered by means of the selected entry in the parent form. One form lets you edit the relation (assign a new citie's country), the other form shows the cities of a selected country. For 150 countries and thousands of cities this is quite usable.

shopping.odb implements a many-to-many relation (each shop has many items) with 2 sectors clothing and fruit. The list boxes in a shop's product lists are filtered by one record set in the top-level form.

Using the built-in form filter you can use a list box, combo box, text control as a filtering list box. Text controls have a property "Filter Proposal". WIth this option set, you push the filter button, select an entry from a list box that used to be the text box and apply the filter.
I fixed your Assets.odb so it works with a one-to-many relation (each model belongs to one brand) and the form based filter works with the text box (asset) and the list box (brand) likewise.
Sorry to revive old posts, but I would ask just a question. There is a macro to do exactly what the refresh button does in the shopping db? I did the same with my db, but I would like to do it automatically without buttons. Thanks.
OpenOffice 3.3 with MySQL Server 5.5 on WinXP Pro SP2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Restricting items in a listbox with another

Post by Villeroy »

Of course there are plenty of macros for everything you want to do. All it takes is somebody to write it.
http://user.services.openoffice.org/en/ ... hp?id=8630
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mattia83
Posts: 36
Joined: Thu Nov 18, 2010 11:29 am

Re: [Solved] Restricting items in a listbox with another

Post by mattia83 »

Villeroy wrote:Of course there are plenty of macros for everything you want to do. All it takes is somebody to write it.
http://user.services.openoffice.org/en/ ... hp?id=8630
You're right, I apologize for my bad knowledge of BASIC, I promise to study more. ;) Thanks
OpenOffice 3.3 with MySQL Server 5.5 on WinXP Pro SP2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Restricting items in a listbox with another

Post by Villeroy »

mattia83 wrote:
Villeroy wrote:Of course there are plenty of macros for everything you want to do. All it takes is somebody to write it.
http://user.services.openoffice.org/en/ ... hp?id=8630
You're right, I apologize for my bad knowledge of BASIC, I promise to study more. ;) Thanks
Basic? In OOo you don't have to use that underdog language. Use Java instead. However, any language is difficult if you don't know what to say.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply