Cross Referenced Value List Relationship??

[kirkrr]

Perplexing problem that I can't seem to figure out the solution:

Inventory comes in (mostly from large auctions). Someone looks at each item to enter it into inventory. Sometimes they know the manufacturer, sometimes not. Sometimes they know what it is (at least categorically, if not specifically, and sometimes not.

 

What I would like to do is select either MFG or CATEGORY, and pick from a list, that item, with the other list being restricted to just the related records.

 

The sticky part is that I need to build the lists, not up front, but as we go. The categories of items that a given manufacturer makes is not always known up front, nor are all the mfg of a given category known. However, in most cases, we will figure it out, and know both mfg and category, even if the subject matter expert (who does NOT do inventory/receiving entry) needs to do this later from the pictures in the inventory record.

 

I'd prefer not to do this in a maintenance mode, but in the actual real time entry of inventory information. FWIIW, these products are medical equipment, with hundreds of manufacturers, and literally, million+ # of line items possible.

 

Just trying to get my head around the problem from a DB structure.......

[Inky Phil]

OK

 

the value lists that are restricted by other value lists are called conditional value lists. Do a search for these as this has been covered a billion times and you should find a sample file fairly easily.

 

As regards the upkeep of your lists just set the value lists to use a value from a specific field. This will then create your list for you offering only one instance of every different entry and will of course then include any new values as the records are created

 

hth

Phil

[kirkrr]

Sorry, I understand the conditional value list part. I provided this as background information, but neglected to indicate what works.

 

The part I am having trouble thinking through is still how (if?) to add to an existing value list AND make the conditional aspects update AND make it so that, if the current conditional list does cover the situation, expand the conditional value list to accommodate everything AND it the item is NOT in this "everything" list, add it. Whew! AND then be able to do the same thing, the other way - from the category, not the manufacturer.

 

It'd be great if I just knew all the manufacturers (thousands) and all the products they make (hundreds) - say, 10 million +, and could just have a relational table with all this in it, but I only want to add what we have and what we get, as we get it, which may be very diverse, and many times, a "once time only" product (that 1950's mahogany patient exam table has never / will never be seen again, but we have one now)

 

As I don't seem to be able to comprehend how to setup the data structure to support this, I was going to prototype some thoughts and find out what works. Suggestions gladly accepted!

[Inky Phil]

As I said initially if you set your value lists to use a value from a field then they will automatically update your value lists including the conditional part. As regards adding data that is not available from the list (ie does not exist on file) just make your field a pop up list and if the selection is not available from the value list then double clicking into the field will allow you to add a new entry, which once committed will become part of your value list

 

hth

Phil