How to have a field auto-fill on a form?
Hello,
I am very new to Base, but I am starting to get the hang of it. My Boss is trying to have me make a form that will output a series of labels connected by commas. The idea being an easy way to insure all his parts are labeled accurately. So theoretically an employee could go to the form, fill out the various fields and have the appropriate part label to appear in a box below for copying/pasting. My boss wants the form to be composed so that the employee can select the full length description of a part from a field (e.g. Socket Head) and have an abbreviation of that description appear in the corresponding part of the label (e.g. SktHd). I have managed to set up the drop down boxes for the full length description fields. What I would like is for the Abbreviation fields for each description (from the same table) to auto-fill after the full length description is selected.
Thank You
Auto-fill Fields on a Form
Auto-fill Fields on a Form
OpenOffice 4.00.9702 on Windows 8
Re: Auto-fill Fields on a Form
So you're basically using a List Box to search among records and display the results. This type of Form can be implemented using a FILTER table as the basis of a MainForm. You'll place the List Box on the MainForm and populate it with:SLOShane wrote:...What I would like is for the Abbreviation fields for each description (from the same table) to auto-fill after the full length description is selected.
- List Type > SQL ... List Content such as:
Code: Select all
SELECT "DISPLAY_FIELD", "ID" FROM "PARTS"
Take a look at this example and perhaps reverse-engineer various aspects of the design for your purposes:
[Example] Business Cards
Additional information may be found here:
[Example #1] Filter/Search with Forms (leveraging SubForms)
Feel free to ask further questions.
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: Auto-fill Fields on a Form
Okay, so what I have on my form right now is 12 list boxes. The top six are formatted as you listed above, so when I open the drop down I can select the full length description. The bottom 6 List boxes are for the same 6 descriptions from above, but my goal is to have them list the abbreviations instead of the full length terms. The abbreviations and full length description for each aspect of the part (Sxl, SEMS, Head, Drive, Material, and Finish) are in the same table, but each aspect of the part is set up as a different table. Do I need to create a SubForm to link the abbreviations to the full length descriptions (by ID) or can I do it right in the list boxes I already have with an SQL command? I'm also not quite sure what you mean by a filter table. More help would be appreciated. I apologize, I just started learning Base a few days ago. Thank you for your help.
OpenOffice 4.00.9702 on Windows 8
Re: Auto-fill Fields on a Form
Update on the issue. So while I've been waiting I've been fiddling around, trying to get it to work the way I want and I believe I am significantly closer than before. Currently I Have 7 List Boxes. 6 of them are for the 6 descriptions of the part, in this case a screw. The six aspects of the screw are SizexLength, SEMS, Head, Drive, Material, and Finish. These List Boxes are dropdown-enabled where a user can select the specific type of Head, Drive, Finish, ect. I also managed to make it so the abbreviation is listed next to the full length text with a dash within the same List Box (e.g. in the drop-down for "Drive" there is an option "Socket - Skt"). Each of the six categories of screw description is its own table including the long text, the abbreviation, and an ID. The 7th List Box is the one I plan to be the final label. I have it coded like this:
Some of my Table titles are confusing, but it seems to be partially working. It creates the combined label like I want with the abbreviations separated by commas, but I don't think it is properly linked to the 6 fields mentioned above because instead of having one specific label tied to the choices in the first 6 List Boxes it has arrows on the left and contains every single combination of all the abbreviations. I Think I'm close. More help would be appreciated. Thank you.
Code: Select all
SELECT "SC SxL"."SC_SxL_Abbreviation" || ', ' || "SC SEMS"."SC_SEMS_Abbreviation" || ', ' || "SC Head"."SC_Head_Abbreviation" || ', ' || "SC Drive"."SC_Drive_Abbreviation" || ', ' || "SC Material"."SC_Material_Abbreviation" || ', ' || "SC Finish"."SC_Finish_Abbreviation" FROM "SC Drive","SC Finish","SC Head","SC Material","SC SEMS","SC SxL"
OpenOffice 4.00.9702 on Windows 8
Re: Auto-fill Fields on a Form
Edit: Sorry, I think we cross-posted. This reply does not reflect your last post above. |
- (1) Each "part" has 6 different "aspects"
(2) You want a form that allows you to select a "part"
(3) Then you'd like to select each of 6 different "aspects" for that "part" using full-length descriptions in the List Content (pull-downs)
(4) The final output is a '"label" in the form of a Text Box reflecting the "part" and the selected "aspects" in abbreviated form separated by commas, suitable for copy-&-paste to another application
- (a) How are your "aspects" organized? By type, one type per table? All one type selected from a single table of "aspects"?
(b) Do you intend to save the selected "aspects" associated with each "part"? Do the "aspects" change each time you create a "label" even for the same "part"?
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: Auto-fill Fields on a Form
Got it! Thanks...DACM wrote:(a) How are your "aspects" organized? By type, one type per table? All one type selected from a single table of "aspects"?SLOShane wrote:Each of the six categories ["aspects"...?] of screw description is its own table including the long text, the abbreviation, and an ID.
But I don't understand this:
Why a List Box? ... Am I correct in assuming:SLOShane wrote:The 7th List Box is the one I plan to be the final label.
DACM wrote:(4) The final output is a '"label" in the form of a Text Box reflecting the "part" and the selected "aspects" in abbreviated form separated by commas, suitable for copy-&-paste to another application
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: Auto-fill Fields on a Form
Well the 7th box is a list box because... I don't really know what else to make it... I know how to change it, I just don't know which type of box will best fit my goal. Here is what I currently have. I figured I would just try to figure it out for screws first before I do the other parts, although it would be nice to have a final product where I could select the part type and then the different aspects. The problem is that each part type (e.g. screw, washer, resistor, ect.) has a different number of description aspects. Anyway here, Ignore the 6 middle fields, I'm going to delete the when I'm sure I don't need them anymore. Tell me how it is. I probably did it the most complicated way possible. If I have to remake the tables, I will. There isn't too much data yet.
You are correct with your idea of how I intend it to work. You reminded me, the label does currently contain the part, just the descriptions. I will fix that later though as I will need to create a new table.
You are correct with your idea of how I intend it to work. You reminded me, the label does currently contain the part, just the descriptions. I will fix that later though as I will need to create a new table.
- Attachments
-
- Parts.odb
- (13.39 KiB) Downloaded 859 times
OpenOffice 4.00.9702 on Windows 8
Re: Auto-fill Fields on a Form
We do need to understand the full scope of the project so we can nail-down all possible requirements before designing the tables...or the form.SLOShane wrote:The problem is that each part type (e.g. screw, washer, resistor, etc.) has a different number of description aspects.
That said, these collective requirements might be best implemented through some sort of junction table. And the "label" might be best produced through a GROUP_CONCAT function as available in the upgraded HSQLDB 2.x engine.
Your tables will be something like:
- Table: PART_TYPE
- ID (INTEGER PRIMARY KEY AutoValue)
TYPE (VARCHAR (50)) (screw, washer, resistor, ect.)
- ID (INTEGER PRIMARY KEY AutoValue)
- Table: ATTRIBUTE_TYPE
- ID (INTEGER PRIMARY KEY AutoValue)
TYPE (VARCHAR (50)) (SizexLength, SEMS, Head, Drive, Material, and Finish)
- ID (INTEGER PRIMARY KEY AutoValue)
- Table: ATTRIBUTES
- ID (INTEGER PRIMARY KEY AutoValue)
PART_TYPE_ID (INTEGER FOREIGN KEY) (screw ID)
ATTRIBUTE_TYPE_ID (INTEGER FOREIGN KEY) (Drive ID)
FULLNAME (VARCHAR (50)) (Phillips, Slotted, Socket,Hex, etc.)
ABBREVIATION (VARCHAR (50)) (Phil, Slot, Skt, Hex, etc.)
- ID (INTEGER PRIMARY KEY AutoValue)
- Table: FILTER (temporary storage if using cascading List Boxes)
- ID (INTEGER PRIMARY KEY)
PART_TYPE_ID (INTEGER FOREIGN KEY) (screw ID)
ATTRIBUTE_TYPE_ID (INTEGER FOREIGN KEY) (Drive ID)
- ID (INTEGER PRIMARY KEY)
- Table: PT_AT_A (junction table: PART_ID 1:n ATTRIBUTE_TYPE_ID 1:n ATTRIBUTE_ID)
- ID (INTEGER PRIMARY KEY AutoValue)
PART_TYPE_ID (INTEGER FOREIGN KEY) (screw ID)
ATTRIBUTE_TYPE_ID (INTEGER FOREIGN KEY) (Drive ID)
ATTRIBUTE_ID (INTEGER FOREIGN KEY) (Phillips ID)
- ID (INTEGER PRIMARY KEY AutoValue)
- (1) a Table Control or List Box* to select the type of part (screw, washer, resistor, ect.)
(2) a filtered Table Control or List Box* to select the type of attribute (filtered by 'screw ID': SizexLength, SEMS, Head, Drive, Material, and Finish)
(3) a filtered Table Control or List Box* to select each attribute (filtered by 'Drive ID': Phillips, Slotted, Socket,Hex, etc.)
(4) a Table Control to display and manage (delete) an unlimited list of selected attributes for a given part-type
(5) a Text Box based on a SubForm query using GROUP_CONCAT to display the abbreviated attributes separated by commas as a "label" suitable for copy-&-paste
*List Boxes require dedicated push buttons (or a macro) to refresh/filter subsequent/cascaded List Boxes
- Is this form used only as a label generator on-the-fly? Or do you need to generate records for discrete parts (by name and/or part number), complete with their associated part-type and selected attributes, for later recall? In other words, is part-type sufficient or will there be actual parts stored as records in the database?
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: Auto-fill Fields on a Form
Thank you DACM, you have helped a lot. I ended up recreating all my tables similarly to how you have mentioned above to simplify things. I'm not completely there yet but I am very close
OpenOffice 4.00.9702 on Windows 8