How to create random arrival schedules with fixed appointment times?

Question

question

Yasaman N asked Jan 22, '19 Cliff King edited Mar 1, '19

How to create random arrival schedules with fixed appointment times?

I am trying to model the arrival of patients, but I am struggling with choosing the right arrival pattern for model. I have experimented with the inter-arrival, appointments, and custom arrivals patterns, but have faced limitations with each.

Please see attached sample arrival data.

I would like to model the arrival of patients that arrive according to appointment times with the following characteristics:

There are different types of patients based on the service they receive. (Column A)
The number of patients that arrive per service is a variable number with the average a and it is between b and c. (Columns B-D)
There are empty appointment slots per day for each patient type. (See Column E)
The operating hours are different per patient type. (Column F-G)
Schedule frequencies are different per patient type. (Column J)

Please see attached sample arrival data.

I would like to create a model with the requirements below:

Create a random number of time slots to be filled per day between start time and end time for a patient type with a set frequency
Assign the generated number of time slots per day into appointment slots randomly. For example, if there are 10 appointments to be filled in a day where 15 slots are available, would not want the 10 appointments to be the first 10. I would like to randomly assign them.
Would like to add a distribution for the variability of the arrivals. For example, would like to see patients coming in between 5 minutes late or 10 minutes early to an appointment.

Thank you

sample-data.xlsx

Software Version:

FlexSim HC 5.3.8

patient arrivals appointment schedule

sample-data.xlsx (10.1 KiB)

Answer 1 · 2019-01-22T21:45:35Z

Cliff King answered Jan 22, '19 Cliff King edited Mar 1, '19

My first thought is to do all your "heavy lifting" in Excel. By that I mean, use macros, or whatever it takes, to create a single patient appointments table (in the format FlexSim HC expects it for a standard Appointments table on a PatientArrivals object) containing patient arrivals for all types of patients for all the days you plan on running the model for. Then you just need to do a simple import using the Excel Import/Export tool and you're good to go. The "Variability" field for the Appointment table arrival scheme in FlexSim will handle the "5 minutes late to 10 minutes early" requirement you mentioned.

However, because I'm a developer, I thought it'd be a "fun" exercise to try and populate an Appointments table in the model for a single day at the beginning of each new day of the model run. I've attached a test model where I accomplished this. I used the following two global tables to do this: MasterTable and CurDayTable. In my test model, I just worried about the arrivals of your "Patient Type 1" definition, but the idea could be expanded to cover a larger number of arrival types.

The MasterTable contains the maximum appointment slot scheme for this type of patient, matching the one you defined in your Excel sheet range B6-B39, but it is in the exact format that the PatientArrivals1 object expects an Appointment table to be in. I created this table by first populating the Appointment table on the arrivals object and then copy/pasting (CTRL+C/CTRL+V) it into the global table to ensure I had the proper format.

The CurDayTable gets overwritten during run time at the beginning of each day by some code I wrote on a User Event tool I named SetCurDayAppts. I documented each line of code in the user event so hopefully you could understand what I had to do in order to set up the CurDayTable with just the number of appointments for the current day and then copy this scratch table into the Appointments table of the PatientArrivals1 table.

I didn't want to take the little test model any further until I heard back from you as to whether or not you want to pursue this path, or if you would rather just do all the "heavy lifting" in Excel. Let me know if you'd like any further assistance, and I'd be glad to help!

NOTE ADDED Feb 14, 2019: Please improved and corrected model I posted in a later answer down below called "Dynamically_Fill_Appointment_Slots_Daily"!!

Cliff

randomly-fill-appointment-slots-on-a-daily-basis.fsm

randomly-fill-appointment-slots-on-a-daily-basis.fsm (65.2 KiB)

· 6

Yasaman N commented · Jan 23, 2019 at 04:22 PM

Thanks a lot for your help with this!

I understand your logic with setting up the tables and updating the CurDayTable per day with a new random schedule. The descriptions were very helpful. It looks like this method is more efficient than developing this in Excel.

Is there an efficient way to also expand this to multiple patient types?

Thanks again

0 ·

Cliff King Yasaman N commented · Jan 23, 2019 at 05:06 PM

You're welcome! Glad you were able to follow my approach ok.

You can get away with one Appointments table to define the arrivals for all your patient types, because the Appointments table on a PatientArrivals object allows you to specify the PCI. You also do not need to list the appointments in chronological order, so for example the first 33 rows could be for PCI 1 and the next 20 rows or whatever could be for PCI 2. The software will sort the arrivals chronologically at the start of each repeat interval (ie. day) automatically behind the scenes.

If you only have a handful of patient types (appointment slot definitions) to worry about, then it's probably easier to duplicate the two global tables already in the model for each patient type. However, you could probably get away with using just the two existing tables and then modifying the code to take into account the range of rows corresponding with each of the multiple patient types existing in the same table(s). I'm also questioning whether you even need to use the "scratch" table I called CurDayTable, or if you could copy directly from the MasterTable to the apptTable each day and delete rows as needed in the apptTable itself.

0 ·

Yasaman N Cliff King commented · Jan 23, 2019 at 05:40 PM

I have several patient types, but I can define the timeslots in a single table as you explained in the same format.

The next challenge then becomes incorporating the other inputs. It looks like you hardcoded the min, max, average number of patients and the number of timeslots. Since these input numbers are different for each PCI, can the code be modified to accommodate this for all PCIs at the same time? How can this be done?

Thanks

0 ·

Show more comments

Answer 2 · 2019-01-25T00:19:49Z

Cliff King answered Jan 25, '19 Cliff King edited Mar 1, '19

Things just got a whole lot slicker! With help from @Matthew Gillespie, I built a model for you that automatically generates a random number of filled appointment slots each day based solely on information provided in a single input table named SlotDefs that looks similar to the information you provided in the Excel spreadsheet initially. I think you're going to like it. It wouldn't be much trouble to change the code in the daily User Event to accept more than a single break period in the appointment schedule, so let me know if this is your desire. I think you're going to like this!

randomly-fill-appointment-slots-on-a-daily-basis-f.fsm

randomly-fill-appointment-slots-on-a-daily-basis-f.fsm (64.6 KiB)

· 4

Yasaman N commented · Jan 25, 2019 at 03:41 PM

This model is really awesome!!! It includes all the requirements and also looks very user friendly. Thank you very much @Cliff King and @Matthew Gillespie for all your help with this!

I would appreciate it if you would modify the code to accept more than one break as I have different breaks for different departments.

Thank you again!

0 ·

Cliff King Yasaman N commented · Jan 25, 2019 at 04:31 PM

Yes, it turned out a lot more straight forward than I originally imagined. Glad you like it.

I updated the model to accept the definition of two breaks. I reorganized the columns of the SlotDefs table so the break definitions are the far right columns. I think you'll find it easy to expand if necessary.

Pay attention to how the break periods are skipped over in the final appointments table so you understand how it's been implemented. You may want to change the >= to just > or maybe the < to <= in the part of the code (lines 37 and 38) that address the two breaks.

Let me know if you need any further explanation of anything, and good luck with your project. Let me know how it goes!

Cliff

randomly-fill-appointment-slots-on-a-daily-basis-f.fsm

0 ·

randomly-fill-appointment-slots-on-a-daily-basis-f.fsm (65.8 KiB)

Yasaman N Cliff King commented · Jan 25, 2019 at 06:09 PM

Thanks a lot for updating the model.

Will do!

0 ·

Show more comments

Answer 3 · 2019-02-14T21:36:21Z

Cliff King answered Feb 14, '19

I needed to make a correction to my previous example model I posted. I didn't realize there is a scratch table used by the patient arrival object to sort the current day's arrivals chronologically. The scratch table is stored on a variable named TodaysScheduledArrivals and is sized only once when the model is reset. This is because it doesn't expect the size of the Appointments table to change dynamically during the course of a simulation as it does in our model. In our model I needed to add a couple of lines of code to the SetCurDayAppts user event function to resize the scratch table each day.

dynamically-fill-appt-slots-daily.fsm

dynamically-fill-appt-slots-daily.fsm (65.9 KiB)

question