Auto-fill Fields on a Form

[SLOShane]

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

[DACM]

...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.

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:

• List Type > SQL ... List Content such as:

  Code: Select all

  SELECT "DISPLAY_FIELD", "ID" FROM "PARTS"

With the List Box bound field set to '1' the user selection will save the selected record "ID" to the FILTER table. A SubForm linked by "ID" is used to display various fields of the record in Text Boxes.

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.

[SLOShane]

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.

[SLOShane]

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:

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"

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.

[DACM]

Edit: Sorry, I think we cross-posted. This reply does not reflect your last post above.

I'm sorry but you've lost me. The best I can ascertain from above is this:

• (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

If that resembles your workflow, then we would also need to know:

• (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"?

Perhaps post what you've got (.odb file), or your intended table design and workflow.

[DACM]

(a) How are your "aspects" organized? By type, one type per table? All one type selected from a single table of "aspects"?

Each of the six categories ["aspects"...?] of screw description is its own table including the long text, the abbreviation, and an ID.

Got it! Thanks...

But I don't understand this:

The 7th List Box is the one I plan to be the final label.

Why a List Box? ... Am I correct in assuming:

(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

[SLOShane]

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.

[DACM]

The problem is that each part type (e.g. screw, washer, resistor, etc.) has a different number of description aspects.

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.

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.)

• Table: ATTRIBUTE_TYPE
  • ID (INTEGER PRIMARY KEY AutoValue)
    TYPE (VARCHAR (50)) (SizexLength, SEMS, Head, Drive, Material, and Finish)

• 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.)

• 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)

• 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)

Your form will therefore be something like:

• (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

Some questions remain before proceeding with the table-design phase:

• 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?

[SLOShane]

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