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:
- Patient (For storing patients/people)
- Date (On which dates the venue is available)
- 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.
P.S. I’m using FileMaker 15 Pro Advanced and FMS 15 in my solution.