Hi John:

I have added a several fields to my Contacts table, including cContactBalance, which displays the patient's (contact's) amount ($) due. I have added that field next to the CalDailyApptContacts::ContactNameLastFirstCalc field in my Daily Calendar view. That works fine. But I frequently schedule each patient twice on the same day, at the same time: once for the actual visit, and once for the patient's urine screen. As a result, the same "balance due" for that patient shows up twice.

I would like to create a calculated field that will display a patient's balance due only once on a given day. As I envision it, that calculation would test for the presence of another appointment for the same patient on the same day. If present, the balance would be hidden. If not, the balance would be displayed.

Can you point me to any examples/models of this sort?

Sure. This is kind of messy, but...

The calc becomes part of the Appointment record, not the contact's record. Then, create a self relation between CalDailyAppointments and another instance of itself (call it something like CalDailyAppointmentsSamePatientDay) that matches on the date and contact ID. Sort this relationship by appointment time, ascending. Your calc in CalDailyAppointments then returns the patient's balance (returns the field you already have in contacts) if...

CalDailyAppointments::ApptID_kprime = CalDailyAppointmentsSamePatientDay::ApptID_kprime

That is, if the current appointment is the first appointment for that patient, that day. "First" here being determined by the sort order of the relationship between CalDailyAppointments and CalDailyAppointmentsSamePatientDay

Thanks John. I will try that. Can I ask you why you feel this is messy? I'm guessing it's because now I'll have fields in my Appointments table that don't, strictly speaking, have anything to do with the appointments themselves. Is that it?

Well it is kind of that we have this whole self join that is just about something cosmetic: showing the balance for the first appointment instead of both. And, that its a sorted relationship, so that balance calc will be slow in some cases.

Nothing serious and it's not as if this isn't a stable technique.

Got it. Thanks again John!

Hi John:

I'm finally getting around to setting this up, and now find I am having trouble. Hope you can step me through this if it's not too time-consuming.

1) I created a calculation field in Appointments, called ApptTwoSameDayCalc.

2) I created a new table occurance of CalDailyAppointments, called CalDailyAppointmentsSamePatientDay. Then I created a self-join, CalDailyAppointmentsSamePatientDay to CalDailyAppointments. The relationship requires that ApptStartDate_k and ApptContactID are matches, and on the left-hand side, under CalDailyAppointmentsSamePatientDay, I checked "Sort Records", and specified ascending sort order for CalDailyAppointmentsSamePatientDay::ApptTimeStart.

3) Now I went back to my calculated field, Appointments::ApptTwoSameDayCalc, and defined it as follows:

Code: Select all

If ( CalDailyAppointments::ApptID_kprime = CalDailyAppointmentsSameDayPatient::ApptID_kprime; Contact::cContactBalance; "" )

4) In my Daily calendar layout, I inserted the field CalDailyAppointments::ApptTwoSameDayCalc in the CalDailyAppointments portal.

But darn! In Browse mode, that column just displays blanks. So I know I've done something wrong! (In the same Daily calendar view, if I insert the field CalDailyApptContacts::cContactBalance into the CalDailyAppointments portal, then in Browse mode, that column correctly displays each patient's current balance. However, the current balance is displayed twice if the patient has two appointments booked that day -- and that's what I want to avoid).

Can you help me figure out where I went wrong?

Yours,
Jim R.

Thanks for the thorough explanation. I believe that your calc is showing the contacts balance from "Contacts::..." while your on layout test is showing it from "CalDailyApptContacts::..."

Switch the calc to use CalDailyApptContacts::cContactBalance and you should be all set.

If that doesn't work please feel free to send me the file and we'll tackle this under paid support.

Wow, fantastic. This works. Thanks again John!