How do I tell who scheduled the appointments?

Management and Administration
1

midder wrote on Tuesday, January 24, 2017:

I’m looking for either a report or a query where I can get the info on who scheduled the appointments for a certain day. Does a report like this exist? Or can you tell me how I get this data? I don’t see anything in openemr_postcalendar_events that looks obvious. I’m guessin the data is here?

Thanks

2

bradymiller wrote on Wednesday, January 25, 2017:

Hi Brian,

Are you looking for whom actually created or modified the appointment. There is an appointment tracker feature that stores all changes to appt. Associated report is at:
Reports->Visits->Patient Flow Board

In the patient_tracker sql table there is sql column original_user which should be the original user that created the appt.

Then whenever the appt is modified, a element is dropped in the patient_tracker_element sql table where the user that made the change is in user sql column.

Note I don’t think the above report shows this information, but this would be a nice feature if this was an option in the report to show this information.

-brady
OpenEMR

3

midder wrote on Wednesday, February 08, 2017:

Thanks for this, Brady.

The Patient Flow Board wasn’t really what I was looking for.

I thought I had sorted it out but I think there are bits I’m not understanding.

Regarding the “status” field of patient_tracker_element . . what are the different values there? Is there always a record written to this table whenever a patient appt is made?

4

bradymiller wrote on Wednesday, February 08, 2017:

Hi Brian,

Every change, including the initial setting of the appt, is logged in patient_tracker_element. Each appt gets 1 entry in patient_tracker and every change to the appt (including the appt creation) gets an entry in the patient_tracker_element(which is mapped to the item in patient_tracker).

The only time the item does not get recorded is when a recurrent appt is made (however, when the appt status is set, a non-recurrent appt is then made which then gets tracked/recorded). To see the possible items for status, check out Administraiton->Lists->Appointment Statuses

btw, if all you care about is whom made thel appt, I think the pc_informant column in the openemr_postcalendar_events stores. I am not sure if this gets changed when an appt is modified. Again, though, the patient_tracker/patient_tracker_element tables provide much more granular data.

-brady
OpenEMR

5

midder wrote on Thursday, February 09, 2017:

Is there a list somewhere of what the different statuses in patient_tracker_element.status mean?

Also, can I assume that if we have a record in openemr_postcalendar_events that we definitely have an appointment for a particular day? I wrote my own reminder service query and I’m wondering if it’s accurate. Following is the query that I’m using. I’ve got placeholders for start and end in case I want to broaden my windown.

SELECT concat(e.pc_eventDate, ’ ', e.pc_startTime) as DateAppt, e.pc_title as NameAppt, e.pc_aid as ProviderId, d.fname, d.lname, d.phone_home as phone, d.email as emailaddress,
concat(‘VOICE_’, d.hipaa_voice) as voice, concat(‘SMS_’, d.hipaa_allowsms) as sms, concat(‘EMAIL_’, d.hipaa_allowemail) as email, 'pc_topic as ApptStatus, f.name as facility, u.fname as providerfirst, u.lname as providerlast
FROM openemr_postcalendar_events e
JOIN patient_data d ON d.pid = e.pc_pid
JOIN facility f ON f.id = e.pc_facility
JOIN users u ON u.id = e.pc_aid
WHERE (e.pc_eventDate > curdate() - @start) = 1 AND (e.pc_eventDate < DATE_ADD(CURDATE(), INTERVAL + @end DAY)) = 1
ORDER BY pc_eventDate, e.pc_startTime

Finally, how do I figure out if an appointment has been deleted? Is it both removed from openemr_postcalendar_events and we also have a record in patient_tracker_element that says it’s been removed somehow?

Appreciate the help with this.

6

bradymiller wrote on Thursday, February 09, 2017:

Hi Brian,

The best way to get an idea of the statuses is to check it out in OpenEMR here:
Administraiton->Lists->Appointment Statuses
(note some items are considered checkin and checkout in that gui, which is used by the patient tracker; looking at this of the status provides a good overview of the statuses; if confused about any of the items, then feel free to ask here)

Yes, openemr_postcalendar_events correspond to appt (all appts will have an entry here). Note that recursive appt are a bit different where 1 entry in openemr_postcalendar_events can represent multiple appt. In this case, when a user sets a status for this appt, then it will get its own entry in openemr_postcalendar_events at that time.

Trying to collect appt as you are doing will break with recurring appt. Recurring appt are tough to deal with since 1 entry in openemr_postcalendar_events can represent many. Here is a nice function that can be used to collect all appt(including recursive) within a timeframe:

Removed appt are deleted from openemr_postcalendar_events, but the entries in patient_tracker_element and patient_tracker will remain(not sure if a delete adds another entry to patient_tracker_element, but it should and would be a good addition if that feature doesn’t yet exist).

-brady
OpenEMR