Value List Table vs. Static List for Drop-Downs

[dkinley]

I wanted to take an informal poll about what others thought and implemented when it comes to drop-down boxes. Although (one of) the main purpose for the drop-down box is for data integrity, there is never much dialogue about the maintenance of drop-down boxes.

An example would be a drop-down boxes for categories, types, or other such information we might use to pigeon-hole data for segregating and counting purposes. Although the quantity of list might hardly ever change sometimes higher-ups want to add another to the list.

I personally use a tblValueList that holds any sort of data for my drop down boxes. It has 3 Fields (ID, ListType, ListDescription). For the drop down box, let's say it's a Category, I use "Category" in the ListType to filter the query and ListDescription is stored into that data field.

Hence, when I update, I just add the appropriate ListType and the new item in ListDescription. This way I can 'outsource' maintenance by allowing a priviledge user access to make these updates through a form.

Is my method normal? or do most use the static list in the properties of the combo box?

-dK

 

[Pat Hartman]

I use the same method you do although my process has been in use for over 30 years and in dozens of applications so I've refined it somewhat over the years. It now exists as Acces objects (it was originally COBOL and IMS DB/DC). I import the objects into each new application so that it is always available. My table has more columns
UniqueID (PK)
TableID (FK to UniqueID since this is a self-referencing relationship)
LongName (the name normally shown in combos)
ShortName (abbreviated name used in reports if space is tight)
SequenceNumber (allows the user to control the order in which items appear in combos if necessary)
ActiveFlg (indicates whether the value can be assigned to new records. This eliminates the need to delete old codes which are in use in old records)
PermissionLevel (1 - programmer only, 2 - authorized admin only, 3 - any user)
CreatedBy (id of person who created the record)
CreatedDT (date and time row was added)
ChangedBy (id of person who last changed the record)
ChangedDT (date and time row was last changed)

Each time I add a new "table" to the table, I create a query to pull out all the rows for that "table". The order by clause sorts by SequenceNumber (which may be null if it is not used), ActiveFlg (this sorts all the inactive items to the end of the list to get them out of the way), and finally LongName which ends up being the sort order unless the user specifically overrode it with a sequence number. The query is ALWAYS used to populate combos.

The last four fields exist in all my tables to help keep track of who is making changes. Occassionally, over the years, I have added columns to satisfy specific application needs but for the most part, the structure remains unchanged.

 

[Banana]

Pat, if I may, what do you usually do for certain lookup tables that may depend on other data (e.g. cascading lists for example); add another column to that table to support relationship or just define those on their own?

 

[dkinley]

Thanks for replying, Pat, it is very nice to hear from people with years of lessons learned. I have never given much thought to give users more information (LongName), hehehe ... I just make them suffer with an abbreviated ShortName.

For ordering I have always ascended on alphabetical only because I turn on hot-keying. In afterthought I am in error thinking that user's work like me. All tabbing and hot keying - not pointing and clicking.

I usually store the ShortName in the text field ... I am not sure why - maybe to flatten out the record as much as possible - I have always done that because when I have imported data in the past I never liked that one extra step and some drama sense of old codes. It seems you have solved that with the ActiveFlg.

Curious question of Banana's. How do you overcome that hurdle?

Hehehe ... thanks Pat for making me slap my forehead a few times while simultaneously yelling the word "Duh!".

-dK

 

[Pat Hartman]

Banana,
Not all tables are simplistic in nature and so those that don't conform, end up as entities of their own. I find that my table application takes care of the vast majority of my lookup table needs and ultimately saves me tons of work by simply removing the decision process. I don't have to even think about it and in those places where the DBA's exert Draconion control over "their" tables, it means I don't have to wait for someone else to add a new table to the database if I discover a new lookup table late in the game. The sequence number was the most recent addition to the table design. I also prefer to alphabetize my lists so that typing works correctly but some users want some things in a particular order so I added the functionality in a way that does not interfere with other things.

 

[Banana]

Make perfect sense.

I was mostly wondering about one-level-deep cascading lookup tables which it seem may fit your table, but I have a bad feeling that if it's just one level deep, it may morph into two level deep and therefore bite me in the posterior.

 

[The_Doc_Man]

There are very few times I would wish to "hard-code" a drop-down list, though I am enough of a pragmatist to know how to do it that way.

Hard-coding makes sense when you have VERY few options AND they won't change by the nature of your data.

For instance: Male/Female (OK, you COULD do a check box for Male and leave unchecked for Female, but that is sexually biased in a subtle way.)

For instance, Yes, No, Don't Know for action completion lists. (In my case, you might also need Don't Care.)

For instance, Dirt, Lower than Dirt, when describing your position in your organization...

Seriously, not many cases are appropriate to hard-code in a drop-down list, but there are speed and space reasons to do exactly that in some circumstances.

 

[georgedwilkinson]

My method is very similar to Pat's. I use a brand new table every time I have a lookup for a different purpose. That works great for simple lookup values.

However, sometimes users want to "associate" (like in a M:M relationship) a complex record with another complex record. For instance (in the most general of terms), they want to be able to say that client x has information pertinent to his case that was presented by client y.

In the M:M subform, I typically have a cbo/ddl allowing the user to select client y. Obviously, if the client table is quite large, this can cause some performance problems. So I've adapted (or adopted) Leigh Purvis' large ddl scheme for instances like this so the user gets to keep working even though the data set is large (i.e. don't assign a recordset to the ddl until after the user has typed in the first several characters). Using this technique, we (actually Leigh) found that there is a bug in the ADO implementation from MS, so Leigh has modified his sample code to compensate.