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
[Solved] How to refer to a field on a form
[Solved] How to refer to a field on a form
Last edited by SLOShane on Mon Aug 12, 2013 7:20 pm, edited 1 time in total.
OpenOffice 4.00.9702 on Windows 8
Re: How to refer to a field on a form
So far this example by DACM is very helpful. Just as an update for anyone else interested. http://forum.openoffice.org/en/forum/vi ... 12#p197512
OpenOffice 4.00.9702 on Windows 8
Re: How to refer to a field on a form
Okay, so you've decided to use a series of cascading List Boxes.
Assuming the following tables:
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.
- 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.
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]
- ID (INTEGER PRIMARY KEY)
- 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]
- ID (INTEGER PRIMARY KEY AutoValue)
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.
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
Re: How to refer to a field on a form
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:
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:
- Attachments
-
- Parts 001.odb
- The File:
- (21.63 KiB) Downloaded 1122 times
OpenOffice 4.00.9702 on Windows 8
-
- Volunteer
- Posts: 828
- Joined: Fri Nov 30, 2007 10:58 am
- Location: Scotland
Re: How to refer to a field on a form
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.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:
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/ima ... torial.pdf
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
Re: How to refer to a field on a form
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.
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: |
- Attachments
-
- Parts 002 (macro-free FILTER table).odb
- macro-free form using FILTER table to filter cascading List Boxes and create labels
- (35.65 KiB) Downloaded 204 times
-
- Parts 003 (macro-free TIMESTAMP labels).odb
- macro-free form using auto-incremented FILTER table with label TIMESTAMP feature
- (36.76 KiB) Downloaded 439 times
-
- Parts 004 (macro-enhanced).odb
- macro-enhanced to clear the FILTER table and eliminate push buttons
- (36.05 KiB) Downloaded 219 times
Last edited by DACM on Tue Aug 13, 2013 12:48 pm, edited 1 time in total.
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
Re: How to refer to a field on a form
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.
OpenOffice 4.00.9702 on Windows 8