8 Replies Latest reply on Nov 18, 2013 5:44 PM by JimBessette

    Way to create Value list based on a number of fields and their values

    JimBessette

      Title

      Way to create Value list based on a number of fields and their values

      Post

      Trying to figure out how to limit a drop down (or pop-up) to limit the items that can be selected by a number of fields.   Table I have are “Tour”, “Hotel”, HotelDates”, “Names”, “Tourer”, and “MemberTours”.

            

      Tour Table contains a Tour Code.

      Hotel is linked to Names for the name of the Hotel  (Names::Company)

      HotelDates contains a “Booked” field set to Y/N to designate if the hotel is booked for a specific TourCode.

            

      What I need for a layout using MemberTours is a way to choose from a drop down of only Hotels that are booked (Booked field = Y) for the TourCode that this Member is on.

            

      I hope that makes sense.  I'm still pretty new to FM.  I've tried creating TOs and all kinds of things, but this one is eluding me.

      Jim

        • 1. Re: Way to create Value list based on a number of fields and their values
          philmodjunk

               When you choose a hotel from the drop down, should the value list enter a HotelID or the Hotel's name?

               How does the Hotel table link to Names? (What fields are used as match fields?)

               There are a couple of different ways that a conditional value list might be possible depending on your answers to these questions.

          • 2. Re: Way to create Value list based on a number of fields and their values
            JimBessette

                 Thanks Phil,

                 Selection from the Value list will put the Hotel Name in the Member's Tour record.   Now you got me wondering if that makes my DB a little redundant. Perhaps it really should just have a link to the Hotel.  But for now, I'll just go with copying the Hotel Name to the Member's Tour.

                 Hotel::_fkNameID-----Names::_pkNameID

                 Just a little more info if it helps:

                 Hotel::_pkHotelID----<HotelDates::_fkHotelID

                 HotelDates::_fkTourID>---Tour::_pk_TourID

            • 3. Re: Way to create Value list based on a number of fields and their values
              philmodjunk

                   I must assume that __pkNameID and _pkHotelID are an auto-entered serial numbers. but given what I see, it looks like your value list should really enter the HotelID value, not the hotel name. With the two fields that can serve as a source of values from a value list, you can display a list of names but have the value list enter the associated ID number. If you can get the HotelID, showing the name from Names--if you do not have a text field in Hotel is simply a matter of the right relationship between tourer to Hotel to names.

                   I also don't see a table for "members" in your description so am assuming that one record in Tourer represents one member.

                   Since you want this on a MemberTours layout, we need to establish what relationships exist linking to that table. Does this look correct for what you have?

                   Tourer----<MemberTours>------Tours----<HotelDates>-----Hotel

                   Tourer::_pkMemberID = MemberTours::_fkMemberID

                   Tours::_pkTourID = MemberTours::_fkTourID

                   Hotel::_pkHotelID----<HotelDates::_fkHotelID

                   HotelDates::_fkTourID>---Tour::_pk_TourID

                   But that doesn't clarify what field in MemberTours you would set up for selecting a hotel and how that would figure in these relationships.

                    

              • 4. Re: Way to create Value list based on a number of fields and their values
                JimBessette

                Yes, all _pk's are auto enter serial #s.

                Tourer----<MemberTour>------Tours----<HotelDates>-----Hotel    

                Tourer::_pkTourerID = MemberTour::_fkTourerID      YES, but I changed to my actual field names.   

                Tours::_pkTourID = MemberTour::_fkTourID             YES

                Hotel::_pkHotelID----<HotelDates::_fkHotelID          YES

                     HotelDates::_fkTourID>---Tour::_pkTourID               YES

                     SInce you know all the relationships I have (I do have some TOs to do that), I'll just try to explain what I'm trying to get at.

                     I'm trying to set up a Hotel Reservation for a Tourer on a "MemberTour".   (This is an additional reservation - not part of the tour).  But, the only hotel available for this extra reservation are those that are part of the tour the MemberTour.   The only relationship I have for Tour and Hotel on a specific Tour is in the HotelDates table.  That layout shows the hotel and a portal of Hotel dates.  That portal contains possible dates of stay for the hotel, a "booked" field (Y/N) and the Tour Code (from Tour table) for that date.  

                     If the Hotel is booked (=Y) and Tourcode matches the TourCode for this MemberTour Record, then that Hotel is OK to see in the value list.   I tried to pare down my DB to show all this, but I don't really have it right.

                • 5. Re: Way to create Value list based on a number of fields and their values
                  philmodjunk

                       What does one record in MemberTour represent? Is there one record for each trip that a member takes or is there one record for each destination that is part of a tour?

                  • 6. Re: Way to create Value list based on a number of fields and their values
                    JimBessette

                         MemberTour is a single tour that a Tourer went on, or is scheduled to go on.  BTW, I have no problem rearranging anything to make this easier and more maintainable.  Adding another table, moving fields, anything like that is possible.  I'm not married to this design at all.  Yes, I have the functionality I want so far, but ease of use and maintainability are foremost.

                         Thanks.

                    • 7. Re: Way to create Value list based on a number of fields and their values
                      philmodjunk

                           I would think that you would need a record for each "stop" on the tour though that doesn't necessarily require multiple records for that purpose in MemberTour (I'd guess that's one use for HotelDates). I'm just trying to get a sufficiently clear picture of what you have so that the needed table occurrences and relationships I might suggest that you add will work for the way that you need to for your database design.

                           So you want to select one additional hotel in an added field in the MemberTour record? And this hotel should be selected from the list of hotels from those that have a matching record in HotelDates and that has "Y" in the booked field?

                           If I have that correct, then:

                           Define a calculation field, constY, in Tours. Select text as the result type and enter "Y" as its calculation. This field will store a "Y" in all records of MemberTours.

                           Make a new Table Occurrence of Tours and HotelDates to start and link them like this:

                           MemberTours>-------Tours|Booked------<HotelDates|Booked

                      Tours|Booked::_pkTourID = MemberTour::_fkTourID

                           HotelDates|Booked::_fkTourID = Tour|Booked::_pkTourID AND
                           HotelDates|Booked::Booked = Tour|Booked::constY

                           Now you can set up a value list using the specify values from a field option to list _fkHotelID from HotelDates|Booked and you can select "include only related values starting from MemberTours" to get a value list of only Booked hotel ID's.

                           But this leaves you without a hotel name to list in the value list so that you can select hotels by name. You can link in an additional table occurrence of Hotel and then (if there is no text field in Hotel that stores the hotel name) an occurrence of Names linked to Hotel. This can allow you to select the Name field from one of these new occurrences as the "second field" in your value list.

                            

                      • 8. Re: Way to create Value list based on a number of fields and their values
                        JimBessette

                             Thanks Phil, it's all working now!!!