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




      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



                        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?