Apache OpenOffice Community Forum

I have a form with a series of list boxes. I want to filter the drop-down content for the list boxes. I know how to filter the drop-down content by setting the Criterion for a certain field. The Problem is that I want to filter the drop-down content according to a value that is not static. The Criterion I want to filter by is a word selected from a separate list box. That way, whenever I change the selection in that separate list box, the filter will change for the list boxes that follow.

Specifically: I have a primary list box that is linked to a table which displays a list of part types. Currently screw, nut, and washer. Then, after the primary list box, there are a series of other list boxes that are linked to a table which displays a variety of descriptions. The descriptions are not specific to one part type. There are descriptions for screws, nuts, and washers all in the same table. The table has a column that refers to part type, and that is what I want to filter by. So, for example, if I filtered by "nut", only descriptions for the nuts appear under the drop-down. The problem is I don't want to filter by a static criteria such as "nut". I want to filter by a criteria that is specified by the primary list box that I mentioned earlier. So what I want to know is how I can describe the field that contains the primary list box so I can enter that as the Criterion for the filter.

Thank you

So far this example by DACM is very helpful. Just as an update for anyone else interested. http://forum.openoffice.org/en/forum/viewtopic.php?p=197512#p197512

Okay, so you've decided to use a series of cascading List Boxes.

NOTE: I'll outline a macro-free approach below, but note that example macros (macro example 1 / macro example 2) can be just as easy and cleaner to implement in this role, since the macro-free approach is equally complex as we push the built-in features to the limit for the combined List Box filtering and record-field cloning.

This will require a FILTER table as I mentioned in the other thread. Since you can only save the last List Box selection to another table (in this macro-free approach), we'll need to transfer the fields from the FILTER table in the process of creating 'junction table' records.

Assuming the following tables:

Table: FILTER (temporary storage if using cascading List Boxes)

ID (INTEGER PRIMARY KEY)
PART_TYPE_ID (INTEGER FOREIGN KEY) (screw ID) [bind the PART_TYPE List Box here]
ATTRIBUTE_TYPE_ID (INTEGER FOREIGN KEY) (Drive ID) [bind the filtered ATTRIBUTE_TYPE List Box here]

Table: PT_AT_A (junction table: PART_TYPE_ID 1:n ATTRIBUTE_TYPE_ID 1:n ATTRIBUTE_ID)

ID (INTEGER PRIMARY KEY AutoValue)
PART_TYPE_ID (INTEGER FOREIGN KEY) (screw ID) [SubForm link will auto-fill this field]
ATTRIBUTE_TYPE_ID (INTEGER FOREIGN KEY) (Drive ID) [SubForm link will auto-fill this field]
ATTRIBUTE_ID (INTEGER FOREIGN KEY) (Phillips ID) [bind the filtered ATTRIBUTE List Box here]

Here's a few hints based on the above tables. SubForm links can be used to transfer the values from the FILTER table to the PT_AT_A junction table, but only when creating a new record. That's okay because we will always be creating new records in the PT_AT_A table. So we can set the associated SubForm attribute to 'Add data only'. You'll need a push-button for cascaded List Boxes to force SubForm updates (which re-runs the List Box Content SQL).

In theory, you don't even need to clone the FILTER table fields to the PT_AT_A junction table, because the cascading 1:n relationships infer that we can derive those fields from the relationship(s). Perhaps someone with some insights here can suggest a more efficient table structure and associated query (GROUP_CONCAT) to build the CSV label output. I haven't really thought that far into this solution, but the above table structure should suffice.

If you run into problems, just post what you've got and someone here can help.

Okay, so I think I'm on the right track. I revised my tables and forms significantly since my first attempt. Everything is much simpler now. However, I still cant quite get it. I only need to do one cascade, but I want it to apply to 7 list boxes. I only have three tables:

Table: PartDescription (All descriptions for all part types (Screw, nut, ect.) and description types (Size, Drive, ect.))
DescriptionID (Primary Key Autovalue)
PartType (Screw, Nut, Washer, ect)
Description# (designates which column each description type will be under (up to 7 columns for some certain parts))
Description (all the descriptions for all parts and all description types)
Abbr (the abbreviations for the description types)
PartTypeID (the ID the corresponds with the type of part in each description.

Table: Labeling (essentially) this tables is all the fields I wanted to appear as boxes on the form. Only a few fields contain data)
PartType (Contains Part Types (Screw, Nut, ect.) and is used for the drop-down on the list box that I want to create the cascade)
Label (for the field that will show the label of combined descriptions)
LabelAbbr (abbreviated Label)
Description 1-Description 7 (7 fields. This is so I can separate the different types of descriptions from the first table into different columns)
PartTypeID (The ID the corresponds with the part types in the PartType field)
PartTypeAbbr (abbreviations for each part type. Only longer part names have abbreviations)

Table: Filter (I made this table because apparently I need it to do cascading list boxes. I'm not sure how to apply it yet)
FilterID (Primary Key Autovalue)
PartTypeID (Same Idea as on the first two tables)

Here is what I have. I don't need the data to be stored after each entry. I just need to create the label. I will consider doing a Macro, but I want to figure this out first. Thank you. The file:

I revised my tables and forms significantly since my first attempt. Everything is much simpler now. However, I still cant quite get it. I only need to do one cascade, but I want it to apply to 7 list boxes. I only have three tables:

With respect I dare to suggest that it may well be your tables (and forms) will require repeated revision until some serious thought has been given to the design of the database: in particular the tables should reflect to a reasonable degree data dependency and the normalization requirements for an effective RDMS. Base forms essentially use SQL (even when hidden by the GUI) to communicate with the database, and subsequent problems are likely if the tables significantly ignore Relational Database principles.

Thus, it is usually more productive to give serious thought to the design of the database before embarking on construction of Base form documents. An excellent introductory tutorial to database design and OOo Base can be downloaded from:
https://wiki.documentfoundation.org/images/0/02/Base_tutorial.pdf

I'll assume you've investigated some database design and normalization principles as Arineckaig suggested above. I've certainly been pushing you in that direction because it promotes database efficiency and eases form design based on the available SQL integration of Base forms. That process would drive you to give-up on your current form design, while adopting a table-control and junction-table to support a variable number of Description field inputs based on the Part Type.

However, I noted a stroke of genius in your posted example with a high potential for success with your current form design. Particularly, your use of a 'Description#' field within the 'Descriptions' table is workable with cascaded (filtered) List Boxes. So working from your desired form design, here's three different examples to consider as you work towards a final solution. Note that even the last example is simply macro-enhanced as part of a building-block approach. It's certainly possible to go directly to a macro-driven List Box filtering solution as mentioned previously. I should mention that the SQL used to generate the labels is more difficult to derive using HSQLDB 1.8 than it would be with HSQLDB 2.x which supports both CONCAT_WS and GROUP_CONCAT as necessary.

Edit: Added a 'split HSQL 2.3 database' version demonstrating CONCAT_WS for label creation: Parts 006 (filter list boxes, split HSQLDB 2.3.0).zip

Thanks a ton DACM!!! You don't understand how much I appreciate it. I was really having a hard time figuring it out. It is so helpful that you gave me a good example of each method. Now I will know how to do it in the future. Thanks again.