Filtering a listbox based on an earlier listbox in the form

Creating and using forms

Filtering a listbox based on an earlier listbox in the form

Postby pjbrass » Thu Jun 18, 2015 5:02 am

I think my organization of this DB might be non-standard. (?) I have as Hierarchy.
Courses (CourseID, CourseName)
Lessons (LessonID, CourseID [fkey])
Students (StudentID)--->StudentLessons (StudentID [fkey], CourseID [fkey], LessonDate, LessonID)

When I enter lessons that the student has done, I first want to select the course with a list box, then the LessonID with another listbox filtered on the CourseID selected. I can't figure out how to refer to the CourseID in the sql for the LessonID to filter. i remember being able to cascade listboxes in Access, but with the data being entered in the form I'm spinning my wheels trying to work it out. I have the Student Table with the StudentLessons table as a subform, which is why I wanted to use a listbox to get the CourseID, because it doesn't relate to Students. I need the Student table in the form in case I need to update student info (address, etc.). That probably sounds very confusing. All I really want to be able to do is enter the Course, then the Lessons done without having to scroll through 100s of records for the LessonID. Up to this point I've been typing them all in without Listboxes. Trying to save some time.
Patricia Rasmussen / OpenOffice 4.01 / Windows 7
pjbrass
 
Posts: 29
Joined: Sun Mar 16, 2014 7:31 am

Re: Filtering a listbox based on an earlier listbox in the f

Postby Arineckaig » Thu Jun 18, 2015 11:17 am

A good description of the options can be found at:
https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=42845

The following demo file illustrates just three potential methods - in this example there is a many-to-many relationship between two of the tables which may or may not apply in your case:
Sample Multi Criteria Search Revised.odb
(65.51 KiB) Downloaded 277 times


Please come back if you have any questions or if these examples do not meet your requirement.
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: Filtering a listbox based on an earlier listbox in the f

Postby Buz » Fri Dec 23, 2016 11:03 pm

Thanks, Arineckaig. This works well. But I only want a selection in the 1st listbox to fire a SQL stmt to populate the 2nd, such as all CITIES in a COUNTY. I can now manage that, but is there an accepted/common way of doing it?

Check out Open_Form_Filtered.odb at "https://forum.openoffice.org/en/forum/viewtopic.php?f=39&t=66974" for clear examples on how to do all this murky stuff.

And is there any way to accomplish this without macros? Macros might intimidate my audience (see next).

I was excited when I stated with ooBase; it seemed simple. Now, not so much. :cry: I've been volunteered to teach ooBase to indigenous people in Guatemala, most of whom have 6th grade educations. I think some of the complex workarounds required in ooBase (like this filtering) may be too much for them. Any suggestions?

Thanks for all the support. I love ooBase and this forum!

Buz
Last edited by Buz on Sun Dec 25, 2016 2:06 pm, edited 1 time in total.
OpenOffice 4.1.3, HSQLDB 2.3.4 embedded, Win 10; LibreOffice 5.2.4
Buz
 
Posts: 42
Joined: Wed Sep 09, 2015 8:38 pm

Re: Filtering a listbox based on an earlier listbox in the f

Postby Arineckaig » Sat Dec 24, 2016 7:37 pm

Buz asked:
And is there any way to accomplish this without macros? Macros might intimidate my audience (see next).

For what my opinion might be worth, I would suggest that it may be simplest to rely initially on the filtering methods that are available within the Base GUI. The form document "1. Navigation Bar Filtering" in the demo "Sample Multi Criteria Search Revised.odb" file above, provides a simple example whereby data from more than one source table can be joined in a query that is the source for the grid/table in the form document. The icons in the form control navigation bar permit mutiple and simple filtering (or sorting) of the displayed data. The potential options are described in the Base help file, and in more tedious detail in some notes that can be downloaded from https://dl.dropboxusercontent.com/u/10552709/FormControlNavigationToolbar.pdf using examples from the same demo Base file. In particular the Auto filter and the Standard Filter and both simple and powerful if used in combination.
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: Filtering a listbox based on an earlier listbox in the f

Postby Buz » Mon Dec 26, 2016 3:27 pm

Happy holidays to all!

I've looked into four ways to populate a subform grid based on a mainform selection (are there more ways?). In the course of this investigation, I've read dozens of ooBase posts, read a number of related documents and watched some videos. Still, there appears to be no way to do what I'd like: select one line in a mainform listbox and have the corresponding data display in a subform listbox. As far as I can tell, you MUST use a grid (table control) in the subform for filtering to work ... unless you use macros.

Macros work well and in the way I expect, but are the most difficult to implement. I've got roped into volunteering to teach ooBase to indigenous people in Guatemala, so macros are out, at least for now. I don't think they's be up for it.

The easiest solution is the Navigation Bar Filtering proposed by Arineckaig's in his post above, but I find it awkward. Almost 20 buttons in the nav bar, small icons and I can't find a way to modify the bar.

There's a madman who uses the handle "TheFrugalComputerGuy". He's got about 500 videos on LibreOffice (!!), which I've read is almost identical to OO. See: http://www.thefrugalcomputerguy.com/tutorials.php. There are 94 videos on LO Base, including several on Subform FIltering (search at http://www.thefrugalcomputerguy.com/libreoffice-base/index.php). He provides a clear and effective description on how to do filtering without macros, based on a listbox selection in the mainform. BUT using the listbox requires a button to refresh the subform because listboxes don't share PKs with the subform. I like his solution, but I don't like the button.

Perhaps the best is given in: https://forum.openoffice.org/en/forum/viewtopic.php?p=252113#p252113
Relatively straight-forward to implement, BUT it requires a grid in mainform as well as a grid in subform. It's easy to disable the ability to add, change or delete in both grids. See the HIRE demo databases references in the post. BUT I don't like that I must use grids in both mainform and subform.

I will go with the last unless some kind soul among you has a better solution. Or a simple guide on how to write macros that ordinary folks can understand.

Thanks,
Buz
OpenOffice 4.1.3, HSQLDB 2.3.4 embedded, Win 10; LibreOffice 5.2.4
Buz
 
Posts: 42
Joined: Wed Sep 09, 2015 8:38 pm

Re: Filtering a listbox based on an earlier listbox in the f

Postby Villeroy » Mon Dec 26, 2016 3:41 pm

You can do exactly the same with list boxes: https://forum.openoffice.org/en/forum/d ... p?id=27954
It's not grid vs list box. The important thing is the additional filter table to store criteria for any kind of record sets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filtering a listbox based on an earlier listbox in the f

Postby Buz » Mon Dec 26, 2016 7:02 pm

Yes, just "not exactly". I still need the REFRESH button. Is there a way to have two linked listboxes which refresh without that button? Apparently it cannot be done.

The FILTER table is an elegant solution, but I think the problem it solves lies in the incomplete way that listboxes work. Of course, I defer to the knowledge of those more experienced than I. :!:
OpenOffice 4.1.3, HSQLDB 2.3.4 embedded, Win 10; LibreOffice 5.2.4
Buz
 
Posts: 42
Joined: Wed Sep 09, 2015 8:38 pm

Re: Filtering a listbox based on an earlier listbox in the f

Postby Villeroy » Mon Dec 26, 2016 7:27 pm

This is where macros are useful. You have something up and running and then you want to save one or two clicks.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filtering a listbox based on an earlier listbox in the f

Postby Arineckaig » Tue Dec 27, 2016 12:20 pm

Buz:
Almost 20 buttons in the nav bar, small icons and I can't find a way to modify the bar.

The size of icons in the Navigation Bar form control can be changed and it does not need to display all its buttons. Of its four main groups any one or more [default is all four] can be selected for display, also, from the control's General Properties. Furthermore it is quite possible to display and include separately more than one these mini Navigation Bar form controls in the same Data Form. If required the Base GUI also permits these mini bars to be supplemented with standard non-macro command buttons to achieve comprehensive, user-friendly and effective form documents. The Base GUI most certainly has its limitations, but surprisingly often an understanding exploitation of its full potential provides more immediate results than resort to bespoke macros.
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: Filtering a listbox based on an earlier listbox in the f

Postby Buz » Tue Dec 27, 2016 12:33 pm

"I see", said the blind man! Thanks.
OpenOffice 4.1.3, HSQLDB 2.3.4 embedded, Win 10; LibreOffice 5.2.4
Buz
 
Posts: 42
Joined: Wed Sep 09, 2015 8:38 pm

Re: Filtering a listbox based on an earlier listbox in the f

Postby Villeroy » Tue Dec 27, 2016 1:36 pm

You can customize the built-in navigation bar on a per-document basis. This one is for a form where a limited record set is editable and filterable but not sortable.
CustomNavibar.png
Customized navi bar saved in embedded form


And this is the customized bar:
CustomFormNaviBar.png
CustomFormNaviBar.png (4.69 KiB) Viewed 5537 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Filtering a listbox based on an earlier listbox in the f

Postby Buz » Tue Dec 27, 2016 1:54 pm

Good grief! There is so much to learn. I started with ooBase because I thought it was simple enough to teach indigenous in Guatemala. But it's as sophisticated as the several 4GL's I've used. Villeroy: will you come to Guatemala with me next Spring? :lol:
OpenOffice 4.1.3, HSQLDB 2.3.4 embedded, Win 10; LibreOffice 5.2.4
Buz
 
Posts: 42
Joined: Wed Sep 09, 2015 8:38 pm

Re: Filtering a listbox based on an earlier listbox in the f

Postby Buz » Wed Jan 11, 2017 3:53 pm

I've gotten lots of help from several Volunteers, so I thought I'd share key points of what I've learned with the community.

I got roped into volunteering in Guatemala; I'll teach descendants of the Maya to write software to manage operations in the mission center in the Alto Plano section of Guatemala. There are 16 projects underway in the mission, all different and all needing software support. The challenge for me was to find FREE software that I can teach to people with the hope that when I leave, the teams will survive and thrive. So it will have to be competent and simple. I've discovered that ooBase will work for my purposes, with some caveats.

This morning, as a graduation exercise, I built a prototype app of six standalone forms, managed from a switchboard form, in two hours, and it all works! :D I discount the approx. 200 hours I spent flailing around in the wilderness before reaching this point. :(

Here's what I've learned, consistent with the constraints of teaching people who have never programmed computer software - these are only the most important points:

I do NOT use FILTER tables to manage multiple subforms. The filter table is an elegant solution but one I thought might baffle my students as it initially did me.

I use GRIDS (Table Control) instead in the mainform, with entry fields in the subform. I've adopted this structure as the basis for most of the forms we will build in multiple applications.

Grids are somehow able to share key info with the subform through the Link master fields so they work well and effortlessly. With subforms, the best part of ooBase in my opinion.

Make sure you have the Wizards On/Off icon in the left toolbar selected, otherwise you will have difficulty assigning columns to the grid.
I think that subforms are the most important aspect of ooBase. Without subforms, ooBase would either be fairly trivial or very complex.

When you add a subform, make sure to make it subordinate to the mainform. When its subordinate, it will display indented under the mainform.

If it's not subordinate, you will NOT see the Link master fields in subform Properties Data tab. This is the key to subform happiness; the Link fields tie the data in the subform to the mainform data. So the mainform grid can display manufacturer data (eg) while the subform can display parts. Easy peasy ... and quick!

Or you can add a 2nd subform, where subform 1 displays a list of products for the mfr and the 2nd displays detail info for a selected part.
I've learned in this thread that I can use the Form Navigator bar to do all that's required. Customize it as Villeroy showed earlier. And read Arineckaig's contribution. The customized toolbar saves development time (and teaching time!) by using built-in functions, specially functions like SEARCH (Find Record),SORT and FORM FILTERS. These are comprehensive and easy to teach/learn.

btw, I can't see how to bulletize in this forum so this may not be as clear as I'd like.

Thanks for all the help!
Buz
OpenOffice 4.1.3, HSQLDB 2.3.4 embedded, Win 10; LibreOffice 5.2.4
Buz
 
Posts: 42
Joined: Wed Sep 09, 2015 8:38 pm


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests