Looking For Some Advice

[Jim Heavey]

I have two data tables which have a parent child relationship. All of
the attibutes in the parent table are in the child table. The child
table has a few more attributes.

The child table, for the purpose of discussion, let's say this table had
the following attributes.

Competition Location
Competition Event Name
Competition bracket
Competition Level
Competition Gender

Name
Placement

The first 5 attributes are shared by both tables, but the child table
identifies the persons name and how they placed in the competition.

When the user selects a Location, I only want to show the distinct list
of Event Names which have been defined for that location. Likewise,
only the brackets, levels and gender which are applicable to the
selected location.

Assuming that the first 5 fields are Combo Boxes. If I created a view
of the parent table in which the "RowFilter" was adjusted to the
"Selected Competition Location", this would of course limit the values
which could be selected in the other combo boxes (assuming that they are
bound to the same view), but it would not be a distinct list of values.
How can I make the list which appears in the remaining drop down boxes
distinct? Am I able to use some sort of "Distinct" command in those
subsequent views?

When the user makes a "Competition Event Name" selection, then this
should further narrow the selections which can be made in the subsequent
dropdown boxes.

Any suggestions as to the "best/appropriate" way to handle this?

Should I even be using combo boxes for all of this?

You assistance is greatly appreciated!!!!!!!

 

[Nick Malik]

Your message is a bit confusing. It appears that you are not using a
relational database design at all.

The child table should not have any of the same fields as the parent.
Instead, I'd expect that the parent table would have a unique id of some
kind, and that the child table would have a column where the parent table's
id was stored as a foreign key. Then, you would ask for what people placed
in a competiton by first getting the id of the competition and then querying
for those child rows that had that id in the foreign key column.

What you are asking for is fairly difficult to do, due to the limited nature
of our user interfaces. This is a problem already faced by folks doing data
mining, and the interfaces that they came up with for "drilling down" can be
fairly interesting (and sometimes difficult to learn).

Probably the best thing is to offer all possibilities for all combo boxes.
Then, wait for the user to select all filters and click a "go" button, and
show the results.
Another option would be a treeview, where each level of the tree has each
possible path in it. The user could drill down all they want. That said,
you could have a fairly large tree, which may take some time to paint, or
some tricky code to fill it as you go.

Good luck,
--- Nick