Hey everyone. I almost hate to post this because I should be able to figure this out. Pretty familiar with FM now and have done conditional value lists before. I have an inventory DB. There are about 2 dozen different types of structural items and hundreds of different dimensions with specific weights. I have a table Inventoy and Lookup. Lookup has the type, dimensions, and weight. In the inventory table, have drop downs to select type, then specific dimensions. This would then be used by a calc to define the weight. Thought it would be easy but the calc is using only the first records weight. Check out my file. Any help would be greatly appreciated.

matltracking.zip

You'll need another TO of the Lookup table that's filtered by Dimension too. Use this TO for looking up the weight for that calc.

Perfect. Thanks Ender.

Just to double check; is this right? Do I need to enter the records twice?

matltracking.zip

No of course not, the table Lookup is not necessary. Use the table LookupDimensions for both table occurrences.

-jens

I'm sorry, I'm still confused? Now how do I make my VL conditional? It is only listing one record? Darn, I though I understood this better.

I correct myself: the table lookup is necessary, but it is malformed.

Its name should be 'lookup_type' and it should have only three records for the three types with a clear id value 1-2-3 for the three shapes.

-jens

Just to double check; is this right? Do I need to enter the records twice?

I think you've missed it. You would add another TO (table occurence), NOT another copy of the Lookup table. Maybe call the new TO "Lookup by Type_Dimension" and relate to it by:

Inventory <=> Lookup by Type_Dimension =

Inventory::S_Type = Lookup by Type_Dimension::S_Type

AND Inventory::S_Dimensions = Lookup by Type_Dimension::S_Dimensions

Use this TO for the getting the Weight.

Sorry this is so late of response. I've been trying to figure this out on my own and I am still confused. I'm still not really understanding what is needed. I want to make sure I understand the concept and not just get it to simply work.

I guess I don't really understand what a "Table occurance" is? I thought it is the relationship from one table to another? So when I set

Inventory::S_Type = LookupDimension::S_Type

Inventoyr::S_Dimensions = LookupDimenstion::S_Dimension

I do get the correct weight, but dimension VL doesn't work conditionally anymore.

Also, what would TO "LookupbyType_Dimension" be? Is this a new field in one(orboth) tables? Does data go in it?

I don't exactly want someone to just solve my problem, but if you could put up a quick referece file, or if there is an example in another post I could reference that would be really helpful. I've looked pretty hard and didn't find anything that helped.

Thanks for your help and patience.

OK. I am so stupid. I needed a "self join". I'm sorry, I just never referred to them as Table Occurances. Not only did I get it to work. I understood what I did and even learned that when I make a new TO I can name it something easier than "table name" "2" or "3." I hate and love this program. Thanks again Ender and jteich.