This is what I use, I think you would want normal order for future appointments and reverse for past. You could tweak it to limit the number of appointments returned, if you have an old database this can take awhile to load. Also, I really only use it for LIST return method so it may need some additional cleanup.
{fn SEARCH_APPTS(returnMethod,type){
//Valid types are NEXT, PREV, and CANC
if (toUpper(returnMethod) <> "DELIMITED" AND toUpper(returnMethod) <> "LIST" AND toUpper(returnMethod) <> "COMMA" AND toUpper(returnMethod) <> "FORMATTEDLONG") then
// Default Return Method if no value passed edit this if you want to change the default
returnMethod = "LIST"
endif
// initialize local variables
local retVal = ""
local z = getRowCount("_MELCurPatientAppt")
// loop through rows in _MELCurPatientAppt data object
for iq=1, iq<=z, iq=iq+1 do
local a = (getRow("_MELCurPatientAppt",iq,"AppointmentsId","ApptStart","ApptTypeId","FacilityId","DoctorID","ResourceId","Status","Canceled"))
if (val(durationdays(datestamp(),a[2]))>0 and a[8]=="" and toupper(type) == "NEXT") or (val(durationdays(datestamp(),a[2]))<0 and a[8]=="" and toupper(type) = "PREV") or (a[8]<>"" and toupper(type) == "CANC") then
// Appointment type lookup in _MELApptType data object
if (find("_MELApptType","Name","ApptTypeID",a[3]) <> "") then
a[3] = find("_MELApptType","Name","ApptTypeID",a[3])
endif
// Facility lookup in _MELLocation data object
if (find("_MELLocation","ListName","DoctorFacilityID",a[4]) <> "") then
a[4] = find("_MELLocation","ListName","DoctorFacilityID",a[4])
endif
// Doctor lookup in _DoctorFacilityForPatAppt data object
if (find("_DoctorFacilityForPatAppt","ListName","DoctorFacilityID",a[5]) <> "") then
a[5] = find("_DoctorFacilityForPatAppt","ListName","DoctorFacilityID",a[5])
endif
// Resource lookup in _DoctorFacilityForPatAppt data object
if (find("_DoctorFacilityForPatAppt","ListName","DoctorFacilityID",a[6]) <> "") then
a[6] = find("_DoctorFacilityForPatAppt","ListName","DoctorFacilityID",a[6])
endif
// ApptStatus is actually a combination of two fields, Canceled or ApptStatus. If Canceled <> "" then write Canceled, else write contents of ApptStatus
if (a[8] <> "") then
a[7] = "Canceled"
endif
cond
case toUpper(returnMethod) == "DELIMITED"
// Don't add pipe before first element
if (retVal <> "") then
retVal = "|" + retVal
endif
retVal = str(a[2],"^",a[3],"^",a[4],"^",a[6]) + retVal
case toUpper(returnMethod) == "LIST"
// Don't add HRET before first element
if (retVal <> "") then
retVal = retVal + HRET
endif
retVal = retVal + str(a[2],", ",a[3],", ",a[4],", ",a[6])
case toUpper(returnMethod) == "COMMA"
// Don't add comma before first element
if (retVal <> "") then
retVal = "," + retVal
endif
// Need to replace commas in elements with semicolon to prevent errors in comma separated list
retVal = str(ReplaceStr(a[2], ",",";")," ",ReplaceStr(a[3], ",",";")," ",ReplaceStr(a[4], ",",";")," ",ReplaceStr(a[6], ",",";")) + retVal
case toUpper(returnMethod) == "FORMATTEDLONG"
// Don't add new lines before first element
if (retVal <> "") then
retVal = HRET + HRET + retVal
endif
// Need to replace commas in elements with semicolon to prevent errors in comma separated list
retVal = str("ApptTime: ",ReplaceStr(a[2], ",",";"),HRET,"ApptType: ",ReplaceStr(a[3], ",",";"),HRET,"FacilityID: ",ReplaceStr(a[4], ",",";"),HRET,"ResourceID: ",ReplaceStr(a[6], ",",";")) + retVal
endcond
endif
endfor
return retVal
}}
Here is a sample call within a form
{if SEARCH_APPTS("LIST","CANC") <> "" then SEARCH_APPTS("LIST","CANC") else "<None>" endif}
