Another Conditional Value list
Previous question was having 2 condiitions for one Value list. But this problem is slighty different.
Consider next 2 tables. 'Invoice Items' and 'Products'. There is a One('Product')-To-Many('Invoice Items') relation between both tables.
A Product can be from a certain 'Category'. Consider this as a 3th table that feeds a Value List 'Product Categories'.
When I create a new record in the 'invoice Items' I want to be able to select first the 'product category' and afterwards the appropiate 'product code'.
Logically I create the relation between 'Invoice Items' and 'Products' by using the 'Product Code' field in 'Invoice Items'.
For achieving the Master-detail effect with dropdowns I used next article:
Hence the fact that in there example they use TYPE as relation. In my situation that would be Category.
Eventually by using these tutorial the dropdowns behave like they should. I select a 'Product Category' and in next dropdown I can select only the 'product code' which are related to the category.
But other tables/layout behave inappropiate now. This is because the 'Product Category' (table products) and 'category' (table invoice items) are the relationship > THIS MEANS NO UNIQUE VALUES FOR ONE OF BOTH TABLES. (derived from the tutorial). But when I add to the relationship the product code from table products and Invoice Items for making a correct relation with unique values. I cannot select any products anymore in my dropdown list. The dropdown list for my products wil show <No Values Defined>.
I tried also other strategies by changing the database structure. But I am hitting on a brick wall here. Can someone help me with the correct approach (Domain Model design or Settings).
I really would appreciate some help or hints. Have been trying for some hours with trial and error but I am not really progressing.
I hope the description and question are understandable by everyone.
Thank you for your Time