10 Replies Latest reply on Jun 21, 2016 5:45 PM by keywords

Best match problem

I have the following problem to solve:

Offer a list of possible dates and times to the client of a fitness center.

The fitness center

- has 10 different machine types (rowing, lifting, stepping etc)

- has 1 to 5 machines of the same type

- all reservations are in a single table, with machineID, clientID, date, start and end time, times are in 10 min steps, i.e. you can have 9:00 - 9:40 but you won’t ever have 9:15 - 9:23

The client

- is told by his personal trainer what he should do the next time, in form of a “recipe”:

n[i] minutes on machine type t[i], 0 < i < 5

- it does not matter in which order he uses the machines.

- When he chooses a date and a time among the ones calculated (presented in a global repeating field with 25 reps), he will be shown the details in a popover:

Appointment on june 21, 2016

9:00 to 9:10 on rowing #3

9:10 to 9:30 on stepper #1

wait 10 min till punchingBall #2 is free

9:40 to 10:00 punch ball on puncher #2

etc

The algorithm

- will stop after evaluating 60 days from given start date (usually set to today + 1)

- will stop after finding 10 alternatives with 0 wait time even if it did not reach the 60 days horizon

- will stop after reaching 25 alternatives

- will try to avoid leaving holes in single machine agendas if possible

Ideas ?

ps repeating fields can be used

• 1. Re: Best match problem

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.

• 2. Re: Best match problem

You can bet I'll solve it... don't forget that it must be multi-user...

• 3. Re: Best match problem

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. )

• 4. Re: Best match problem

Hi Siplus,

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.

• 5. Re: Best match problem

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.

• 6. Re: Best match problem

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.

• 7. Re: Best match problem

Was this a publicly posted project? I swear I saw this in the last few weeks. I remember giving it some thought.

• 8. Re: Best match problem

Hi,

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

• 9. Re: Best match problem

iBeacons demo?

• 10. Re: Best match problem

Sounds amazingly similar to the fitness centre demo FM are using in their launch of FM15. Have you looked into that at all?