Create Value List based on 3 fields

[Doozer]

I don't know if this is possible but i have a databse file that contains 3 fields ie PART_NO DESCRIPTION ACTIVE_PART this is a stock database. I can create a value list by selecting use values from field and selecting PART_NO as the first field and then DESCRIPTION as the second field showing only values from the second field. This allows me to show the values of DESCRIPTION in the list but place the PART_NO in the correct field of another tables field?.

What i would like to do though is get this value list only to show values for records that have the ACTIVE_PART field set to true as i do not want to delete records when a part becomes obsolete but would like to set its ACTIVE_PART to false so that it doesn't show up in the list.

Is this possible and if so how would i go about it.

[bcooney]

Create a calc field, PartID_Active = If (ACTIVE_PART = 1, PartID, "") and create a value list using PartID_Active and Description.

Active_Part is best left as a number field, formatted as a checkbox, with a value list that is simply 1. This is a common way to put in a "switch".

[Doozer]

I can see how this works but i am trying to display the description ie the second field in the value list for selection not the part no. This calcualtion means that it still displays active and inactive items but if an inactive item is selected then the field is not populated with the part no.

[bcooney]

Sorry, misunderstood. Here's a demo. I've added an overlapping field that is not enterable in Browse mode, to show you how the maintain the display of the desc if a part is no longer active.

ActiveOnlyVL.fp7.zip

[Doozer]

Thank you for this as it has helped a lot. The only thing now is that i think i am going to have to re-design the database as i had the PARTNO and ACTIVE_FLAG in one table and the actual DESCRIPTIONS and details of the item in another table. ie one table called STOCK contining just partnos, qty, price, active_flag and then other related tables ie CASE,MEMORY etc containing the descriptions with other details to describe the product. This was because CASES need a colour field but memory for example does not.

This seems to mean that the list view will not work because it is trying to use a relational field which can't be indexed. Unless there is a way round this i supose i will have to move the description into the main stock file.

[bcooney]

If only two fields differ for a product, then breaking up your data doesn't seem to have much of a payoff. Memory could have n/a in the color field.

[Doozer]

unfortunatley it's not that simple, The database is for creating build list for computer systems, therfore i broke it down into categories ie tables for CASES,MOTHERBOARDS,MEMORY,CPU,VIDEO,STORAGE,KEYBOARDS,MICE,OPERATING SYSTEMS etc.

Id did this because as you can imagine to describe a CPU i need fields for MAKE,MODEL,SPEED,SOCKETTYPE. but for storage fields for MAKE,CONNECTIONTYPE,CAPACITY,SPEED and so on with all the other categories.

Maybe i just need to have a bit of a re-Think!!

[bcooney]

So, you're faced with lots of categories of products. But I don't agree they should be split across several tables. A simply Type field would allow you to filter your product records, even create filtered value lists, as we did with the active flag.

Some threads to read come to mind:

J Stark "Recursive Data Structures"

and of course the classic thread: Subtype/SuperType

Any and all inventory threads on the Forums