Need help with Value list which looks at related values within related values
I understand how to create value lists and selected to show related values only. The problem I'm having is how to take that one step further.
I have a database with a table named Audit with the following fields - ::lamp type ::lamp size ::number of lamps ::lamp wattage.
I have a table named Lamps with fields of the same name.
I've created a relationship between Audit::lamp type and Lamps::lamp type
I've created a value list for Audit::lamp type to show all values from Lamps:lamp type (this works perfect)
I've created value lists for Audit::lamp size, Audit::number of lamps, Audit::lamp wattage to look at table Lamps for the matching field name and to show related records only.
The problem I'm having is that those three fields are showing just the related values dependent on what is selected for Audit::lamp type. However I want Audit::number of lamps and Audit::lamp wattage to only show related records after Audit:lamp size is selected.
I've tried messing around with relationships and creating a second table occurance for the Lamps table but I think i'm either setting up the relationship fields incorrectly or the value lists.
Any help would be appreciated.