Hi John:

I want to modify my application to allow the user to keep track of charges (in dollars) for each appointment, and to keep track of each contact's (in my case, each patient's) payments and balance due. The daily calendar view would show, for each appointment, the charge for that appointment, and the patient's current balance due (these would be displayed alongside the date and time of each appointment). Similarly, clicking on any appointment would bring up the "MiniWindowEditAppointment" layout, which would contain fields enabling me to enter the charges and payments for a given appointment, and would also display the patient's current balance due.

Here's my strategy so far:

1) Add ApptCharge field to Appointments. This is the fee our office charges for the current appointment.

2) Add ApptPayment field to Appointments. This is the payment, if any, made by the patient at the time of the current appointment.

3) Add ApptCurrentBal field to Appointments. This is a calculated field, defined as (ApptCharge - ApptPayment). This is the balance due, for the relevant patient, at the time of the current appointment.

4) Add ApptSummBal field to Appointments. This is a summary field, and is defined as the running total of ApptCurrentBal. I hoped that this would give me the total balance due, for the relevant patient, at the time of the selected appointment.

5) Modify "Daily" layout as follows: add two fields to CalDailyAppointments portal (CalDailyAppts::ApptCharge, and CalDailyAppointments::ApptSummBal).

6) Modify "MiniWindowEditAppointment" layout as follows: add four fields (CalDailyAppts::ApptCharge, CalDailyAppts::ApptPayment, CalDailyAppointments::ApptCurrentBal, and CalDailyAppointments::ApptSummBal). The first two fields accept data entry, and the third and fourth display calculated results.

Well, it works -- but only if the appointments in the ContactActivity portal are sorted by date in descending order. When I re-sort my appointments in this portal in ascending order (as I do by default), the result is all screwed up.

Can you help me figure out why the sort-order is messing things up? And how I might fix that?

Hi Jim-

You might want to consider normalizing the transaction information separate from the appointment information. That will make summarizing charges by customer, day, etc, much easier. An appointment might have no or possibly many transaction details, and this will be messy in terms of the meaning. An appointment might also get cancelled or rescheduled, does this mean that the charges were cancelled?

My strongest advice in general to new database developers is to resist AT ALL COSTS the sometimes overwhelming temptation to add a field (quick and dirty) unless you are 100% positive that this information is directly related to the primary key of the table, the WHOLE primary key (not just part of a concatenated or multi-part key), and nothing BUT the key. This even fails the first clause of the test, which can set you up for what database gurus call "add-update-and delete anomalies".

For instance, if you failed to enter the transaction details (computer was down, etc?) for any reason, it would be difficult from an audit trail perspective to know precisely when the transaction actually got entered. If you ever want to allow for a link with Quickbooks, it will expect to see transactions as single events. This is only one example, the real downside is that your code is now tied directly to the data structure, and any changes in the data structure will require substantial recoding.

Example: A person may actually have more than one address, and the address is not directly related to the person's SSN or the personID it is only an association, and not PART of the person, as gender SSN, birthdate, etc.

In the actual case you describe, summary fields will be difficult or possibly unworkable, as they are dependent on the sort order. Any changes to your design will require scripting changes, which might be difficult to manage. Sounds like the balance information you are lumping into appointment is actually a statement or invoice. You could relate to the appointment and to the person, but I would put the transaction details and transaction summary (invoice) in separate tables.

So to review, I counted the following eight "things of interest" so far, one of them shows a need for a separate instance or record (appointment):

Appointment (present)
Appointment result (clinical notes?, suggestion for follow-up appointment, I saw an earlier post where you discussed this as an addendum, I like Appointment Result as it is more descriptive)
Appointment (follow-up)
Person (customer)
Person (Service provider)
Transaction (charge, payment)
Invoice (specific set of charges from one visit)
Invoice detail transaction (Invoice has multiple specific items)
Statement (group of invoices + payment history)

Thanks! But regardless of whether I create a new table to store this info, I will still have the problem with incorrect results when I am sorting by date ascending. So, I'm still looking for help with my original problem.

Jim-

Summary fields will not properly display unless you are in List view and you only have the records you are summarizing in the current found set. I don't think that was what you were intending. Summary fields are like a dynamic calculation which changes depending on the found records. In your case it will probably not show as you intend, and it will cause horrendous performance issues. Sorry I still am not helping with your original problem, but "you can't get there from here..."

I would not try to put two (or more!) different subjects together into one table like this. Very advanced developers will sometimes do it, but it even makes their hard work considerably harder, as everything must be under script control. If you want to add invoicing, transaction journaling, and payment processing to SeedCode Calendar, you might want to consider commissioning the work as a custom project.