That's quite a puzzle!
You might consider creating records for every 10-minute block of time. Then, scheduling would involve modifying the pre-existing records. The advantage being, you could perform a find for un-reserve times. (It's hard to search for nonexistent records.)
I'd love to hear how you actually solve it.
You can bet I'll solve it... don't forget that it must be multi-user...
The nonexistant records is not really a problem. You can get them in a list.
for a given day and a given machine,
- you do a $existingApps = executeSQL on the appointments, getting startTime and Endtime;
- $ExistingApps = List ("07:00 07:00"; $existingApps; "21:00 21:00); $Holes = "";
- you loop on $i going from 1 to ValueCount($existingApps) - 1
- in the loop you build $Holes = List($Holes; RightWords(GetValue($existingApps; $i);1) & " " & LeftWords(GetValue($existingApps;$i + 1); 1) )
- ( ideally you use a Let and avoid creating a line in $Holes if its left time = its right time. )
I developed something like that for a music centre with rehearsal rooms.
Next to the reservations table, you can create a planning table with the fields: PK planning ID, machine ID, machine type, start time, end time, reservation ID, status.
I would try to avoid repeating fields in this table.
Create a script with a loop to auto fill the planning table the planning table with a records for each machine and each timeframe, use a start and an end date.
Fill the fields: PK planning ID, machine ID, machine type, start time, end time and status (0 = free time frame).
Then you have a table filled with records with all possible times for all possible machines.
You can also create a table with holidays to set the status in the planning table to "9= not available"
And if a machine is out of order you can set the status in the planning field to "8= machine defect)
This way you can also detect how much a machine is defect.
When a reservations are made and a machine is defect machine you can make a script to move them to a free machine and if available the same type.
When a reservation schedule is made you can fill reservation ID in the existing planning records with status open and necessary machine type in the same timeframe.
When you create a schedule you must also have fields that have information about the dates and the timeframes client is available to exercise.
Hope this gives you a idea.
thank you Karina.
As I said, it must be multiuser, that means I could have 4 people at reception on the phone with a client communicating the trainer's recipe, looking for alternatives. That also means I can't create records in a table (unless I mark them with PersistentID and delete them afterwards, but I'd rather not go that road), so everything has to happen in globals.
I can't speak for karina, but I think you misunderstand my (our) suggestions. The 10-minute records are permanent, and probably created by a server script on a nightly basis, so that you always have 60 days of lead time. The Multi-user nature of the solution is a non-issue.
Was this a publicly posted project? I swear I saw this in the last few weeks. I remember giving it some thought.
The sollution I developed is also multi user and they also communicate with the clients on the phone.
The user work in a layout in a user table, this table is related with the planning table.
I didn't use globals.
When the user's still on the phone the client recieves a mail with the reservation.
I also created dashboard (also in the user table) so that they can see wich clients are scheduled for that day, and if they order drinks you can easily create a record in the POS table, with the POS layout so that the client automatically has a tab and don't forget to pay the bill
But maybe there's a better or another way.
What sometimes helps me thinking in another direction is start creating a flowchart in Visio (or drawing it on a paper)
Then I see it from another point of view.
But maybe you already did that.
This problem is a real challenge
Sounds amazingly similar to the fitness centre demo FM are using in their launch of FM15. Have you looked into that at all?