[Solved] How to refer to a field on a form

Discuss the database features
Post Reply
SLOShane
Posts: 21
Joined: Wed Aug 07, 2013 12:06 am

[Solved] How to refer to a field on a form

Post by SLOShane »

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
Last edited by SLOShane on Mon Aug 12, 2013 7:20 pm, edited 1 time in total.
OpenOffice 4.00.9702 on Windows 8
SLOShane
Posts: 21
Joined: Wed Aug 07, 2013 12:06 am

Re: How to refer to a field on a form

Post by SLOShane »

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
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: How to refer to a field on a form

Post by DACM »

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.
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
SLOShane
Posts: 21
Joined: Wed Aug 07, 2013 12:06 am

Re: How to refer to a field on a form

Post by SLOShane »

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:
Attachments
Parts 001.odb
The File:
(21.63 KiB) Downloaded 1122 times
OpenOffice 4.00.9702 on Windows 8
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: How to refer to a field on a form

Post by Arineckaig »

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/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
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: How to refer to a field on a form

Post by DACM »

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:
 
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
SLOShane
Posts: 21
Joined: Wed Aug 07, 2013 12:06 am

Re: How to refer to a field on a form

Post by SLOShane »

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
Post Reply