Sending subsets of records to a value list?

[Zipididodad]

I'm a newbie so be kind!

I recently found a solution to a problem which works though it''s awkward. I was interested if anyone had a more elegant solution.

Table A contains a huge list of widgets. One of the fields in this table is the "date" when the widget was made.

Table B has a pull-down field where the user has to select one of the widgets from Table A. At first, I defined a simple value list to show the primary key of each entry in Table A, and assigned this to the pull-down field. This was a royal pain for the user when Table A had 100+ records, i.e., he/she had to visually pick the desired widget from a list 100+ long.

My desired solution was to limit the value list to only show widgets from Table A which were made after a certain date, thus, I could limit the size of the pull-down box.

How I did this (and again, it's clumsy) was to open Table A, and perform a find on all widgets made after a certain date. I then exported all fields of the found recordset to another table I called "Subset of Table A". Then, rather than assign the PK of Table A to the pull-down box in Table B, I simply assinged the PK of the "Subset of Table A" table to the box. Thus, I can effectively limit the choices presented to the user, which makes them happy.

This works since the user typically works with more recent widgets, the earlier ones get stored, but aren't really necessary for data entry anymore. They have to be stored however to be mined later on.

I hope this explanation is clear. In my line of work, this "type of fan-out" is typical. At the beginning of a fab line, an operator makes a wafer. At the next step this wafer is diced into 25 pieces. At the next step, each die is further divided into 10 pieces,..yada yada yada. At the end, you have 1000 records for each 1 record in the first table.

I'm thinking there must be a way to directly assign widgets to my value list based upon a criteria like "only get widgets that are 30 days or less old". Right now, I have to manually go in, and update my "subset of Table A" every 30 days or so to make my system work.

Anybody out there have to solve this type of "manufacturing" problem before? or,..could you recommend a good book?

Thanks all!

[LiveOak]

The way to limit your value list to a subset of all the widgets in the file is to base it upon a relationship. The simplest case is to define a field in Table B (File ::

iOne (calculation, number, indexed) = 1

In File A, define a field:

Marked (number)

Define a relationiship from File B to File A (call it SelectWidget) and have iOne in File B match Marked in File A. Now, if you base a value list upon this relationship, you will only see the Widgets with a "1" in the Marked field in the drop down menu.

As an alternative, the definition of Marked could be:

Marked (calculation, number) = Date > 4/5/2001

This will test as true (1) for Widgets with a date newer than April 5 of this year (or whatever you wish). It's a automatic way of limiting the drop down menu display of widgets based upon date.

-bd

[Zipididodad]

Bd (Live Oak),

That was quick. Just got home from work and boy was I pleasantly surprised. I'll try it out late tonight and post back if successful.

Thanks again Bd.

[Zipididodad]

I went went your second suggestion. It works nicely. Thanks again for the help!

Dave A