3 Replies Latest reply on Apr 14, 2017 10:28 AM by philmodjunk

    Omitting values from value lists


      Hi there,


      I am trying to develop a booking system where an Administrator arranges an appointment for a patient via telephone. The Administrator will firstly select a venue, then detail the dates available to the patient. Once a date has been selected, the Administrator will select a time for the patient. I have this functioning in the attached database, my problem is once the Administrator selects a venue, date and time for the patient, this appointment is still available to be booked. This runs the risk of ‘double booking’ patients. I want to omit taken values to prevent this, but do not know how to do this via relationships. Perhaps it must involve the ‘not equal to’ operator.


      I have four tables:

      1. Patient (For storing patients/people)
      2. Venue
      3. Date (On which dates the venue is available)
      4. Time (Which times the venue is available on a specific date)


      In the attached ‘Test’ database, two records are present in the PATIENT table. Both patients have booked the exact same appointment. I’d very much appreciate advice on how to prevent this.


      Many thanks


      P.S. I’m using FileMaker 15 Pro Advanced and FMS 15 in my solution.

        • 1. Re: Omitting values from value lists



          Thank you for your post!


          I'm going to move this thread from the FileMaker Community Feedback Space (which is specifically for input on the Community itself) to the Discussions Space where you should receive more views and potentially more feedback on this topic!



          FileMaker, Inc.

          • 2. Re: Omitting values from value lists

            What you need to do is keep a return-separated list of the value that have already been selected. This can be used as a multi-key in a relationship with not equal operator to exclude choices via a portal or a value list. It's not clear how the appointments are being selected but this video should get you started to learning dwindling value lists.


            Dwindling Value Lists - YouTube

            1 of 1 people found this helpful
            • 3. Re: Omitting values from value lists

              Your file does have some basic problems to work out before you try to use a dwindling value list or other method to avoid double booking.


              Currently, you appear to "book" a patient by selecting a venue and a date. Your "Time" record links by venue and date and this doesn't seem likely to work. Seems like you need to also link by time as well.


              Even then, a patient can only be booked once. Booking them for an event in the future would require overwriting their current link to a previous event.


              Thus, it would appear that you also need another table used to book patients to a specific venue, date and time. That table would link patients to the other tables in your solution--though if I were in your shoes, I'd consider using one table for both date and time with a link to venue so that you have one record in this table for every possible time slot for every possible venue.


              And with all that and a dwindling list, if you have more than one person doing the booking at the same time, there's still a chance of double booking as two or more users might select the same venue/date/time at the same time. Some error checking will be needed to trap for that error while the admin is still in the process of booking a patient.