Creating a calendar look schedule report

[kokomol]

I am trying to create an interview schedule that is driven from a database known as ORSOS Onecall. I can't see any tables in the database that contain appointment book hours. I have schedules that show people that are booked for specific times but would like to create a schedule that also shows the unbooked times.

 

[lbass]

How would you want the report to display? Just one day per page? What time intervals would you want to see on the schedule? Would this be just for certain working hours?

-LB

 

[kokomol]

We want it to have specific start and end times and display a different page for each room that is booked. For example, one room would have open and close times of 9am-5pm and spaces for new appointments every half hour.

 

[lbass]

Would a report that allowed you to select which room and to select the specific date work for you (versus showing multiple rooms and dates per report run)?

-LB

 

[kokomol]

I have a report set up right now that is driven off of the date and grouped by the room. I was hoping to find out how I can add unfilled time slots to the report when it prints. Right now, the only times that print are the ones that have been booked. The report runs from an appointment book that is used in ORSOS, which is a surgery scheduling system.

 

[lbass]

Well, the solution I had in mind wouldn't work with groups.

Do you have a separate time field or just a datetime field for the appointments?

Would both the intervals and the range of times for appointments vary per room?

-LB

 

[kokomol]

The time fields are all stored as date and time together. The report pulls information from a precase start time that comes from the appointment book. The intervals can be 30 minutes apart for each room.

 

[chelseatech]

There is an example in our newsletter and a sample report on our web site. Check those out. Look for Issue 60 on

http://www.chelseatech.co.nz/pubs-old.htm#2007

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[kokomol]

Thanks but not really what we want.

 

[lbass]

The following worked when I tested it. In the main report, insert a group on {table.apptdatetime} on change of day->repeat group header on each page, and then a group on {room.roomno}. In the section expert, select the GH#1->new page before->x+2 and enter:

not onfirstrecord

Format GH#2->new page before->x+2:

not onfirstrecord and
date({table.apptdatetime}) = date(previous({table.apptdatetime}))

Then create these formulas:

//{@reset} to be placed in the GH#2:
whileprintingrecords;
shared timevar array x := time(0,0,0);
shared stringvar array name := "";
numbervar i := 0;
numbervar j := 0;

//{@toshare} to be placed in the detail section (and then suppress the detail section):
whileprintingrecords;
shared timevar array x;
shared stringvar array name;
shared timevar start := {room.starttime};
shared timevar end := {room.endtime};
numbervar i := i + 1;
numbervar j := count({room.roomno},{room.roomno});
if i <= j then (
redim preserve x[j];
redim preserve name[j];
x := time({table.apptdatetime});
name := {table.name};
);

Next, insert an UNLINKED subreport to be placed in the GF#2 footer. Add a table to the report that has at least 48 records (one for each half hour of day). Place a recurring field from that table in the subreport header and suppress it.

Then in the sub, create a formula {@halfhours}:

whilereadingrecords;
numbervar i := i + 1;
timevar basetime := time(0,0,0);
timevar array hourx;
timevar y;
if i <= 48 then (
redim preserve hourx[48];
hourx := time(dateadd("n", (i-1)*30, datetime(currentdate, basetime)));
y := hourx;
if i = 48 then (
i := 0;
));
y

Insert a group on this formula->on change of minute and ALSO, place the {@halfhours} formula in the detail section and insert a maximum on it at the group level. Remove the groupname from the GH#1 and replace it with the inserted maximum.

Then go to report->group sort->select "All" and maximum of {@halfhours}->ascending. (The group sort is necessary because the halfhours formula is "whilereadingrecords", and in the next step, you will be comparing a shared variable (whileprintingrecords) with a value from the halfhours formula, and this disrupts the order of the half hours. The group sort fixes this.)

Next create a formula {@sharedvals} and place it in GH#1 in the sub (next to the maximum of {@halfhours}:
whileprintingrecords;
shared stringvar array name;
shared timevar array x;
numbervar i;
numbervar j := ubound(name);
stringvar z := "";
for i := 1 to j do(
if maximum({@halfhours},{@halfhours}, "by minute") = x then
z := cust
);
z;

Suppress all other sections within the subreport, and remove the borders from the subreport. Then in the section expert within the sub, select the GH#1->suppress->x+2 and enter:

whileprintingrecords;
shared timevar start;
shared timevar end;
maximum({@halfhours},{@halfhours},"by minute") < start or
maximum({@halfhours},{@halfhours},"by minute") > end

Although you could probably have worked the room start and end times into the halfhours formula, the suppression here is simpler to do, and the number of records is so small that I don't think it makes much difference in terms of processing time.

This worked when I tried it. However, this does assume there is only one patient name per half-hour slot. It won't show multiple names per slot. Not sure whether that is significant to you--I would assume there would be only one patient per room per datetime.

-LB