Calculation to avoid double appointment booking?

[mattlight]

Hi guys,

Is there an easy way in an appointment database that you can make the database look through the rest of the database to see if an appointment time, meeting room or person already has a meeting?

I'm sure that you can create an entry screen for an appointment request and then create a SUBMIT APP REQUEST script to lookup the rest of the database to check the time, person and room and ask for confirmation of availability. How do I enter a person's name and then for it to not allow appointment at a specified time by using a calculation that references the rest of the database?

This is to be used for a medical practice where an allocated time slot is reserved on appointment with assignment to a certain treatment room. It has to lookup the room (see if it's available), doctor's name and time in order to proceed with the appointment.

If it can only be done by a script, what formula would I need to look up details in the entire database? Please be as specific as possible with maybe an example - I haven't been FULL ON programming for a while so feel the need to let you know my database muscles are a little flabby at the moment.

Thanks guys and gals, speak to you soon!

NB: My previous post regarding Appointment Sceduler is the same one that I wish to update with the DOUBLE BOOKING WARNING facility - check it out if you need more details - it contains all field definitions and everything - free-be from me to help other people with appointment bookings - this above problem is the only missing link to an otherwise sound solution.

[LiveOak]

The best way is to use a self relationship (from a file to the same file) based upon a multi-key. The multi-key in this case would be the treatment room and the doctors name. Both should be selected from a drop down list to avoid variations that would break the relationship. The computer doesn't know that Room 1 is Rm 1 is R1. I would use something like the doctors last name,the room number, and a time slot. A multi-key for doctor Jones and room one at 1:00 PM is a text field defined as:

Key (calculation, text, indexed, ASCII) = DrName & "-" & TimeSlot & "

[mattlight]

I understand the principle of what you are saying about the solution to the problem but I still find it hard to understand how to do it!

Is the warning message simply a portal displaying a conflict message (namely the room, doctor or time is already taken)? Or is it a message box that you can do by creating a script with DISPLAY MESSAGE?

Secondly, to give you more information, I have the times of appointments on a menu that jumps in 30 minute incriments from 8:00 to 19:30 hours and length of appointment using 30,60,90 or 120 minute incriments. On the database I have a container repeating field(24 time possibilties) that looks at both the time of appointment and duration and fills in each respective time slot with a colour - YELLOW = appointment booking, RED = Waiting to be seen, BLUE = with doctor, GREY = Doctor away and clear = available. To help with further calculations I could make another invisible repeating field that puts a number (Room number) in the respective repeating field number instead of a colour - to help with any DOUBLE BOOKING calculation.

But, how can I apply what you have written to what I have already done? If you wish me to send you a copy of the SCHEDULER that I have already done please ask and I will send.

Thank you for your help so far but, please could you put it in a way that might work with the scheduler I already have in place.

The following post to this will be a copy of the original post that I placed on a different forum with all of the field definitions and calculations that I used to create the SCHEDULER (I forgot to copy it before starting to write this out)

[mattlight]

This is the original post that I put up a while ago.

The first database is called SCHEDULER - which, through a portal, you enter in each appointment - and through another portal you get a very nice full colour SCHEDULER. (Microsoft Word docoment with a screen shot as to how mine looks!)

The fields.

Appointment Date Date

Week Number Calculation (Number) = WeekofYear(Appointment Date)

Appointment No Serial Number with Current Value: "1" Increment: "1"

Appointment Time Time

Doctor Name Text

Duration of Appoin.. Text

Appointment Status Text

Global Colours Global (Container) Repeating field with 4 repetitions

Status Colour Calculation (Number) = If(Appointment Status = "Booking", 1, If(Appointment Status = "Waiting", 2, If(Appointment Status = "With Doctor", 3, "")))

DATA code Calculation (Number) = If(Duration of Appointment = "two hour", 15, If(Duration of Appointment = "one and half hour", 7, If(Duration of Appointment = "one hour", 3, If(Duration of Appointment = "half hour", 1, ""))))

Schedule Finder Calculation (Text) = DateToText(Appointment Date) & "-" & Doctor Name

SCHEDULER Calculation (Container) = If(DATA code = 1, GetRepetition(Global Colours, Status Colour), If(DATA code = 3, GetRepetion(Global Colours, Status Colour), If(DATA code = 7, GetRepetition(Global Colours, Status Colour), If(DATA code = 15, GetRepetition(Global Colours, Status Colour), ""))))

Graphical Length Calculation(Text) = If(Duration of Appointment = "two hours", "half hour-one hour-one and half hour-two hour", If(Duration of Appointment = "one and half hour", "half hour-one hour-one and half hour", If(Duration of Appointment = "one hour", "half hour-one hour", "half hour")))

Client Name Text

One hour from app Calculation (Time) = If(Left(graphical length,18) = "half hour-one hour", Appointment Time + 1800, "")

One half hour app Calculation (Time) = If(Left(graphical length,36) = "half hour-one hour-one and half hour", Appointment Time + 3600, "")

Two hour from app Calculation (Time) = If(Graphical length = "half hour-one hour-one and half hour-two hour", Appointment Time + 5400, "")

Increment Time Global(Time) Repeating field with 24 repetitions

Increment Colours Calculation (Container) = If(increment time - Extend(Appointment Time) = 0 or increment time - Extend(one hour from app) = 0 or increment time - Extend(one half hour from app) = 0 or increment - Extend(two hour from app) = 0, Extend(SCHEDULER), "")

Then Insert Object (Bitmap) four seperate colours (one in each of the repetitions) and as someone said - you only need ONE pixel's worth of colour and then make sure that the field has MAINTAIN ORIGINAL PROPORTIONS unchecked.

Yellow = Booking

Red = Waiting - at reception

Blue = With Doctor

Grey = Time off

Then go to the TIME INCREMENT field in BROWSE mode and enter in -

08:00 next 08:30 next 09:00 etc etc > 19:30

then go into layout mode and Disallow entry into field.

Create a value list of Appointment times 08:00, 08:30 etc

Create a value list for Duration of Appointment

Half Hour

One Hour

One and Half Hour

Two Hour

same as stated in GRAPHICAL LENGTH and DATA code fields

Then line up the repeating increment TIME field with increment colours field (one under the other)

Then from another database you can have a booking screen including these fields -

Doctor Name - with same source value list

Appointment Date Date format

Times Global(Time) Repeating with 24 values - same as other one

Combined Search Calculation (Text) = DateToText(Appointment Date) & "-" & Doctor Name

Create a relationship between both databases based on

Current Database - Combined Search

SCHEDULER - Schedule Finder

Create a Portal the length of the screen at the bottom of screen with 10 thin rows and vertical scroll bar

Place the Times field above the portal in 9pt Horizontally repeating so it fits on screen AND so you can read the times

then, in the portal grab the INCREMENT COLOURS field and make it as small as the row and as long as the times (so they match up)

With the space at the end of the portal place in there two more fields from the other database - Client Name and Appointment Status

Disallow entry into field on all but APPOINTMENT STATUS and make that field a PULL DOWN MENU

Value list Booking, Waiting, With Doctor

When you select the Doctor name and Appointment date in this database it will list all the appointments for the day for that doctor.

To test it out, flick over to the SCHEDULER database and make a coupe of appointments to the same doctor at different times and different durations. Set them up as "Booking". (I haven't set up an appointment entry screen on the other database yet!)

Then go back into the database with the portal (make sure the portal is sorted by Appointment Time) and you should see the appointments in the SCHEDULER - I do anyway! - Then click on the Status Field on one of them and change it from "Booking" to "Waiting". It turns from Yellow to Red. Because it's a portal, in the doctor's office he should always have a copy of the SCHEDULER visable on all screens of the entire database because even though he might not touch the computer before his eyes the booking turns from yellow to red - notifiying him that the patient is in the building!! Good uh?

Hope this helps you guys - remember me in your will!!

[Rigsby]

Seems like I

[mattlight]

Rigsby, thanks for your replies very much - yes, it does seem like you've been solving all my problems today! I'm going to give it a go and I'll get back to you with the low down on the outcome.

Thanks you again.