What does one record on your sample layout represent?
While I can definitely see why you'd need a conditional value list so that selecting the performance populated the drop down list with the correct list of discount codes and matches them to the correct values for that performance, I'm having trouble picturing why such a list needs to diminish...
Thanks Phil, I'm in the process of reworking this, not going to use a Discounts table to try and keep the structure simpler (unless I have to), so layout is Performances, with a new record for each Performance, related to Productions. First the user selects which Production season he's entering data for, then time, date etc, then enters the discount codes and prices to be allocated to those codes. Having chosen, for example 'Full' as Discount A, the code 'Full' should not be available to choose for any of the Discounts B-K.
'Productions' has fields;
Title of Show
'Performances' has fields:
We're not populating the discount codes by selecting the performance, rather we're allocating up to ten discount codes (out of a Value List choice of about 16) to that performance and entering the relevant price for each one..
Simpler isn't always better and often leads to more complexity down the road. Sounds like you need these relationships:
And then you'd use a portal to Performance_Discounts to select the desired discounts categories and either enter the discount amounts/rates or look them up from Discounts (depends on your business model).
And that could very much benefit from a diminishing or dwindling value list of Discounts. That would keep you from accidentally selecting the same discount twice. It's also possible to display what looks and functions as a check box list where you select the desired discount options for a given performance by clicking "check box" controls listed in a portal.
Both options are demo'd in this many to many demo file:https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7