AnsweredAssumed Answered

Diminishing Value List to Remove Multiple Values, Available in Join Table?

Question asked by pmjd on Mar 7, 2014
Latest reply on Mar 7, 2014 by philmodjunk


Diminishing Value List to Remove Multiple Values, Available in Join Table?


     Hello everyone.

     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:

     Customer_Details::CustomerID---<Customer_Booking::CustomerID Customer_Booking::BookingID>---Booking::BookingID

     Customer_Booking::Customer Type>---<Customer_Type::Type

     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,