It's possbile to filter a listbox with another listbox

[darkshark]

Hi to everyone,
i'm trying to create a form with 2 listbox that write something in 2 fields (let's call them "fieldB" and "fieldC") in the table "tableA".

Those 2 listbox (listboxB and listboxC) are linked with 2 different tables ("tableB" and "tableC").

The listboxB list the values of the field "idB" of "tableB" and write the selcted value in the field "fieldB" of "tableA".

The listboxC list the values of the field "idC" of "tableC" and write the selcted value in the field "fieldC" of "tableA".

"tableB" and "tableC" have a relation one to many with the unique field "idB" in "tableB" that relate to many record "idB1" in "tableC".

What i'm trying to do, is to have the list shown in the listboxC filtered by the above relation, so when the user select a value of "idB" in the listboxB, the list shown in the listboxC contains only the value where "idB1" = "idB".

I thank in advance for any help!

[DACM]

You should be able to apply the principles and techniques described in this example.
...

[darkshark]

Hi,
i've readed some times your guide, and i got the theory of build subforms to filter the results, but i find very difficult to actually apply the concepts to my problem.

Reading your guide, it seems to me that the example that better fit my problem is the example2, and since the guide is very "theorical" i've downloaded the students2.odb example database to try to understand how to apply the concepts to my problem but now i'm a bit puzzled.

Correct me if i'm wrong, but as far as i can understand, your example is about the use of 2 listbox to launch something like a variable query on a single table to apply two WHERE clauses based on what the user have selected, and the main purpose of the listboxes is to select the data to visualize in the grid, not to write something in a record.

Since all the works are done on a single table i understand how you have nested the actual "main" grid under the two other form that you have used to filter the resul of the grid.

I don't understand how to apply those techniques to my three tables problem where the 2 listbox are used to select data from two different tables and write them on a third table that i think i can not nest under the listbox forms.

I'll try to make an example to "visualize" my problem:
- in the "tableA" of my example you have the characteristics of a thing: name, color, weight, "book in wich is described, "number of the chapter of the book in wich is described", etc etc
- in the "tableB" you have the name of the books that you have in your library
- in the "tableC" you have all the reference points of your books: a name of a book (in realtion one to many with tableB), the chapter numbers, and a brief description of the chapter

My form should visualize one by one all the "things" in tableA, and i need to have a first listbox where i can choose one book from "tableB" and write its name in tableA, than, the second listbox, should let me chose only the references in tableC that comes from the book i've chose in the first listbox, to select the reference point in the book in wich the thing of tableA is described and wirte it in tableA.

Thanks for any help

[DACM]

I understand. The examples don't apply to your case because you want to save the results of more than one List Box to a selected record in TableA. The macro-free examples only allow you to save the last List Box selection to a selected record in a table; preliminary cascading List Box selections must be saved to a dedicated filter-table/record in a macro-free approach.

While this is understandable, it is redundant. You could just as easily accomplish this task with a single List Box which displays the book name followed by the chapter reference (the List Box display can be derived from any number of tables using SQL concatenation). A subform (linked by book ID and based on TableC) could then display the brief description of the chapter in a multi-line text box. This macro-free approach would require a push-button to refresh the subform for chapter-summary display purposes. Or you could employ a simple, boilerplate macro to refresh the subform automatically upon each List Box selection.

If you really want to pursue two (somewhat redundant but admittedly cleaner) fields and associated List Boxes, then you'll need to utilize a macro. We've got plenty of examples of macro-driven List Box filtering in these forums and we're here to help if you get stuck.
...

[darkshark]

Hi,
thank's for your reply.

I've taken into account the option of concatenating the book and the chapter, but, for what i'm able to set up, i had to abandon this option, because in the real DB the book and the reference point have 2 unique ID that i have to write in 2 field of tableA to correctly identify the reference. Correct me if i'm wrong, but if i concatenate 2 field in a listbox, in the end i can write only one field. In addition, in the real DB, i don't have alwayls a reference, sometimes there's only the "book" without the "chapter".

It's not that i really want to pursue 2 redundant field in my form, i'm just come up with this kind of table setup to register the eventual reference point of an object, and now i'm trying to build a from to write the tables. If there is a more suitable option i can try to change the things.

Anyway even if i never set up or used a macro, it seems that by now is my option of choice... i'll take a look to the examples you linked to try to come up with something.

[darkshark]

Hi,
i solved my problem.
Thanks to this example of yours, i think i've got how to filter the second listobox based on the first with a macro.

Since the example cover a more complex task that maybe could be a little hard to understand, i post the "minimum" macro to achieve the goal.

-------------------------------------------------------------------------------------------------------------------
Sub ListBox2_ItemStatusChanged (oEvent As Object)
oEvent.Source.Model.commit()
oForm = oEvent.Source.Model.Parent
oForm.updateRow()
sValue = oEvent.Source.Model.ValueItemList(oEvent.Selected)
sSQL="INSERT HERE THE SQL CODE TO GENERATE THE SECOND LISTBOX WHERE INSERT HERE THE RELATION BETWEEN THE TWO LISTBOX='" & sValue & "'"
oListBox=oForm.GetByName("INSERT HERE THE OBJECT NAME OF THE SECOND LISTBOX")
oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
oListBox.ListSource=Array(sSQL)
oListBox.Refresh
oListBox.Enabled = 1 'enable Listbox
End Sub
-------------------------------------------------------------------------------------------------------------
In the SQL code in the macro, you MUST use this character ` instead that the usual " to declare the database fields and tables, so for example, instead than write "myfiled" you must write `myfiled`

After writing the macro, the macro must be binded to the first listbox in the tab Events of the Properties of the Listbox object in the form editor in relation the the Modified State event.

Thanks for the help.

[DACM]

Congratulations!

Macros are indeed be the best approach in this case, given the optional chapter-reference, and given sufficient examples and/or macro-coding experience.

I will add that List Boxes combined with SQL are quite powerful, without macros. I'm not suggesting that SQL is easier than adapting an example macro as you've done. But I will mention that virtually anything is possible with SQL concatenation and string functions. In other words, you could use a 'left outer join' within the SQL used to derive the List Box display field, in order to also include book names without chapter references. In addition that same SQL command could concatenate the ID's from both source tables (TableB and TableC in your example) including nulls such that the List Box saves a delimited string to the main table (TableA in your case). SQL string functions could then be used to derive/extract the "Book ID" and "Chapter ID" as necessary for SubForm display purposes. So yes, with some ridiculous effort, it is possible to save multiple ID's including null handling to a single field using a List Box, without macros.

But I think you've found the best solution through adapting macros.

[DACM]

In the SQL code in the macro, you MUST use this character ` instead that the usual " to declare the database fields and tables, so for example, instead than write "myfiled" you must write `myfiled`

Actually you should generally utilize double-double quotes (""myfield"") to delineate database objects within a macro. The back-tic works (`myfield`), and I've used it interchangeably throughout my coding experience with *Office because I prefer back-tics for readability -- but as I recall, it has some limitations relative to double-double quotes. Perhaps Sliderule or others could remind us of those limitations...?

[papijo]

Hello,
Since I was confronted to a similar problem, this discussion helped a lot. I needed to filter 2 combo-boxes in "cascade". I decided to do this with a macro and I am attaching a document which illustrates the way I did it in the hope that it might help other AOO/LO users with a similar need for "cascading" combo-boxes.