Assistance on (3) related value lists, please

[Andrea S]

Hello All!

This is my first post and I'm only a 4-week user of FM, so please be gentle with me... :-)

My database is for Candle & Candle Accesssory order entry, with the need for (3) related cascading value lists.

I have been playing with the popular sample solution "Boggle.fp7" ( http://www.fmforums.com/threads/download.php?Number=134828 ).

The end result of the Boggle logic would give me what I need, however I'd like to maintain my main data table a tad differently.

The Boggle product list has items related based on the <Belongs To> field. However, my product list is an import of an Excel spreadsheet that is updated every-so-often and does not contain a <Belongs To> field. I'd prefer to use that import directly and not have to manipulate the table at all.

Basically, this is my hierarchy of items within my Product table:

Product Type-->Product Name--Fragrance/Item Name

Sample values are:

Candle

Candle Style 1

Fragrance 1a

Fragrance 1b

Candle Style 2

Fragrance 2a

Fragrance 2b

Accy

Accy Collection 1

Collection Item 1a

Collection Item 1b

Accy Collection 2

Collection Item 2a

Collection Item 2b

I've been able to relate my value lists by creating TO's of my Product table. I have also been able to get my values list for Product Name to narrow-down based on Product Type.

Unfortunately, when I've tried to get Fragrance/Item to narrow by Product Name I'm still not quite there. Depending on where I set my related value in the value list definition, all I've been able to get are results like:

a) all values found for Fragrance/Item based on Product Type, regardless of Product Name

: only the 1st value found for Fragrance/Item based on Product Type regardless of Product Name

c) only the Fragrance/Item values for the 1st Product Name based on Product Type even if I've chosen the 2nd, 3rd, etc Product Name

It seems that I'm almost to where I need to be. If I may be so bold, I'm attaching my sample db of what's been happening. Perhaps someone can shed some more light on it?

To add an additional twist, we would also need to have the other fields on the layout generate once the Product Name & Product Fragrance are chosen. Ex: Product Code, Product Price, etc.

Ok...I guess that's enough from a newbie. ;-)

Any help is appreciated!

--Andrea

Andrea-valueslisttest1228.zip