10 Replies Latest reply on Mar 7, 2014 12:38 PM by philmodjunk

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

    pmjd

      Title

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

      Post

           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,

           Paul

        • 1. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
          philmodjunk

               There are very specific details key to getting the diminishing value list to work not found in your post. I don't follow why you so carefully described your "score" system. What affect is that supposed to have on your diminishing value list?

               What is the diminishing value list supposed to do in your case? Keep the same driver from being selected twice in the same rental? or Something else?

               What calculation did you set up for the exclusion list? In what table did you define it? What relationship did you define to use that exclusion list so that you can use the "include only related values" option to exclude previously selected values?

               The error message suggests that your relationship matches to an unstored calculation or a data field with indexing turned off. Neither of which should be the case for a diminishing value list patterned after my demo file.

          • 2. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
            pmjd

                 Thanks for looking.

                 The score system I mentioned for information, as I don't know if there were I'm going wrong and although I thought it shouldn't interfere with the join table I thought I'd mention it anyway. 

                 What I wanted to achieve was to select the Driver Type and once selected remove it from the list so it could not be selected again. In time I would also apply a diminishing value list to the driver choice but that wasn't the first concern.

            The Tables, and their occurrences are as follows:

            Booking

            Customer_Booking

            Customer_Type, Unselected_Customer_Types and All_Customer_Types

            Fields

            Booking has : BookingID (number, indexed, auto-enter Serial, Can't Modify Auto)

                                        Driver_Type_List (calculation, unstored from Booking = List ( Customer_Booking::Customer_Type ) & "¶0"

                                        gRefreshTrigger (text, global)

            Customer_Booking has BookingID (number, indexed) 

                                                        Customer_Type (number, indexed) 

            Customer_Type has primaryID (number, indexed, Auto-enter Serial, Can't Modify Auto)

                                                  Type (text, indexed)

                                                  Refresh (text, indexed, Auto enter Calculation, replaces existing value| calculation = booking::gRefreshTrigger)

            Relationships

            Booking::BookingID----<Customer_Booking::BookingID      Customer_Booking::Customer_Type>----Customer_Type::primaryID

                 Also Booking::BookingID>--x--<All_Customer_Types::primaryID

                 Also Booking::Driver_Type_List>--does not equal--<Unselected_Customer_Types::primaryID AND Booking::gRefreshTrigger>--x--<Unselected_Customer_Types::refresh

            Value List

                 Data from Customer_Booking::Customer_Type

                 Use Values from Field Unselected_Customer_Types::primaryID  , Use Values from First Field primaryID Also display values from second field Type

                 Include only related values from Booking. Sort via first field.

                 Is that all the information you need or have I missed anything?

                 I found I did have a missing index option on one field but with these settings I get an error of <no values defined> in the portal. As far as I can see I didn't miss anything but obviously something is missing.

                  

                  

                  

            • 3. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
              philmodjunk

                   Exactly how is your value list defined? What exact options did you select? (A screen shot of the dialog would show these details.)

                   Does Customer_Booking::Customer_Type store text such as "renter", "driver", "2nd driver" or ID numbers?

                   What values do you put in Customer_Type::PrimaryID?

              • 4. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
                pmjd

                     I have attached a screen shot of the value list options.

                     Customer_Type::PrimaryID stores numbers, 1 - 5, "renter" "driver" etc are stored in Customer_Type::Type. in turn Customer_Booking::Customer_Type also stores numbers as it is linked to Customer_Type::PrimaryID.

                     I have tried to match my tables to yours, so that my Booking is your EventDiminishingVL, my Customer_Type is your EmDmViContact and my Customer_Booking is your EvDmVlContact_Event.

                      

                • 5. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
                  philmodjunk

                       I will observe that your field names are inconsistent. In one table, "type" stores ID numbers for the type and in the other "type" stores text naming that type. This should not keep this from working, but it does make it harder to work with your tables/relationships after some time goes by and you start forgetting details on how you designed the database.

                       What is the Result type specified for Driver_Type_List? Is it text or number? It should be text.

                  • 6. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
                    pmjd

                         Before I started adapting things for a diminishing value list Customer_Booking::Customer_Type originally held text from a custom value list of "Renter" etc., rather than a number as it does now. I'll modify it for clarity once everything is working.

                         The result type for Driver_Type_List is text.

                    • 7. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
                      philmodjunk

                           So far, I haven't found any errors in your setup.

                           The next thing to check is your layout. If you select Layout Setup, what is the exact name selected in "Show Records From"? In portal setup for your portal, what is the exact text shown in Show Related Records From?

                           And is the field that you are formatting with this  value list, the Customer_Booking::Customer_Type field?

                      • 8. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
                        pmjd

                             Show Records From = Booking

                             Portal is from Customer_Booking

                             Field in Portal is being formatted by Value List is Customer_Booking::Customer_Type

                             Thank you for taking the time to look at this. I have probably done something stupid somewhere and might try build your solution again from scratch tonight rather than try to put it into an existing database and see how I get on.

                        • 9. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
                          pmjd

                               Not sure what has changed but all of a sudden it seems to be working. I had closed and reopened the file to compare with yours and suddenly values are available for selection and disappear from subsequent selections as they should.

                               Many thanks again!

                          • 10. Re: Diminishing Value List to Remove Multiple Values, Available in Join Table?
                            philmodjunk

                                 One last detail to check comes to mind: Make sure that The check box for "do not evaluate if all referenced fields are empty" is NOT selected in the Driver Type List calculation field.