Diminishing Value List to Remove Multiple Values, Available in Join Table?
I'm wondering if it's possible to construct a Diminishing Value List for my vehicle rental databases as follows:
To select drivers for a rental there is a join table between Booking Table & the Customer Details Table, with three additional fields after the two foreign keys: a primary key, a forth field which records the role of the customer in the rental which is derived from another table, and a fifth field which records if the customer is driving or not. The fifth field is automatically calculated from the value of the forth with a result of 1 or 0. The fifth field is to calculate the number of additional drivers to charge for.
The values for the forth field from another table are listed as follows:
1) Renter/Driver (Driving score 1)
2) Renter (Driving score 0)
3) Driver (Driving score 1)
4) 2nd Driver (Driving score 1)
5) 3rd Driver (Driving score1 )
The tables are set as follows:
I have been looking at the ManytoMany demo from PhilModJunk, as well as a Dwindling Value list from Kevin Frank but so far I haven't been able to set up a Diminishing Value list. I have been trying to replicate PhilModJunk's solution to match my own,where I have matched the following tables in my database to the ManytoManyWextras database as follows:
Booking equals Events, Customer_Booking Join Table equals Contact_Event and Customer_Type equals Contacts. For this version Customer_Booking::Customer Type>---<Customer_Type::pkID instead of the Type field, which contains text.
There is a portal from the Customer_Booking Join Table in a layout based on the Booking Table to select the drivers but I get an error of <index missing> when trying to select from a value list (data from Customer_Bookings, Values from a TO of Customer_Type called Unselected_Customer_Type) but if I remove the option on the value list of "Show only related Values from the Booking Table" I get the full list again, thought it obviously doesn't diminish.
I had hoped that as the join table is already a way to link multiple drivers to one booking that I would be able to also use this to setup the diminishing value list, with a Booking::BookingID being related to the Customer Type::Type via the join table and using it to remove the statuses as they are selected.
I don't know if I need a different approach, such as a separate join table for this diminishing relationship but as I am having problems getting this to work I wondered if there was another solution or a step by step guide to implementing this, as I must be missing something in trying to reverse engineer the example files.
Also I don't know if this is possible but I would like to be able to convert the Customer Role value list in to a diminishing one but with the added twist that if option 1) "Renter/Driver" is selected it also removes options "2) Renter" & "3) Driver" OR if option "2) Renter" is selected it also removes option "1)Renter/Driver".
Is this multiple value list option removal a bit over ambitious? I have been trying to think of an additional field that this could be calculated and selected or would it be a case of using the If function with the List() parameter to show different options available depending on what has been selected? But seeing as I can't even get a normal diminishing value list to work I think I am a bit out of my depth here.
If anyone can point me in the right direction that would be great!
Thanks for reading this long ramble,