SeedCode Support Forum

I have created a table of medications (Rx's). I now want to set up a relationship that will allow me, at each patient encounter (appointment), to select and display a list of that patient's current medications. I think I need to create a "many to many" relationship, since each appointment can list any number of medications, and any given medication can be "used by" any number of encounters. But I can't figure out which tables to relate in this way. Can you advise?

So let me ask a question before we sketch this out. A patient's medications aren't an attribute of the encounter- rather they are an attribute of the patient, right? I mean a given patient is "on" prozac whether you see them or not, and may have several encounters while they are on a given medication, right?

Yes, a patient is "on" a given Rx whether or not they have an appointment. And, they may have several encounters (appointments) while on a given Rx or on a given set of Rx's.

At the same time (just thinking ahead here), I would like to structure this application so that I have the ability to view a patient's historical Rx "set" at any given point in time. For example, I want to know which Rx's Mary is prescribed "now", (where "now" is understood to mean "the date of our most recent encounter.") But I also want to be able to browse back through her previous encounters, to see what she was getting at those times. Hope this is clear!

OK Jim, here is how I'd do this, from the simple to the more elaborate.

At the baisc level you need two new tables. One for all your possible medications, and a second for the medications prescribed to a contact. Lets call this last one ContactMeds. From the contact;s side you'd related this to Contact Table by ContactID_kprime to the contact ID in ContactMeds.

You can include a prescribed date and discontinued date in ContactMeds. I'd add another calc field equal to 1 if there is a discontinued date and its less than the current date. This will let you put a green dot (using number formating) beside all the meds that are currently active.

When you're looking at an encounter's record (an appointment) we can already see the contact's info, so now we just need to add the contact's meds. This would be a new table occurrence (TO) of ContactMeds related to CalDailyApptContacts, again by contact ID. Call this one CalDailyApptContactMeds and you can place a portal to it on any of the appointment layouts (perhaps making a new tab for this on the Edit Appointment mini window).

That will give you the basics. Going further, you could add an appointment ID to the ContactMeds table so that you'd know at which encounter the medication was prescribed (this may be redundant since you have the prescribed date). You could then build a second TO based on ContactMeds but related to CalDailyAppointments based on BOTH the Appointment ID and the ContactID. This would show you just the Meds prescribed at that encounter and, if you had "allow creation of related records" on for the relationship, would let you easily add new meds through this portal.

Now, if you really need to know which meds were in effect at the time of any past encounter, you'll add yet another TO based on ContactMeds and relate it to CalDailyAppointments. This time the relationship would be based on the contact ID and a date range between the appointment's start date and the prescribed date and discontinued date in ContactMeds. Actually you'd use a new calculated field for the discontinued date incase there is none, something like if ( not isempty (discontinueddate) ; date ( 1 ; 1 ; 2025 ) ; discontinueddate). Be sure that resolves as a date, not a number.

Your relationship would look like this:
ApptContactID_kf = MedContactID_kf
ApptDateStart_k >= prescribed date
ApptDateStart_k <= discontinued date calc

Again, this would show all the meds in effect at the time of the encounter and you could still use that green dot mentioned above to see the ones still in effect.

Hope that gets things started.

I've just started trying to implement this, John. Regarding the following:

add another calc field equal to 1 if there is a discontinued date and its less than the current date. This will let you put a green dot (using number formating) beside all the meds that are currently active

1) can you explain how you set up the calculation here? Is the resulting field similar to the already-existing fields named "_c_Common_One"?

2) can you provide an example of how you would put a green dot beside current meds?

Thanks!

Sure. Try something like

If ( not isempty ( discontinueddate ) and discontinueddate < Get ( CurrentDate ) ; 1; 0 )

The place this field on your layout and select the Number format options. Select "Boolean" and have non-zeros read as blank, and zeros to read as the bullet character ( • ). Then format this field to be a nice bright green and you'll have a green dot beside anything current.

Thanks John. I'll try that. Meanwhile, here's another question.

Near the end of your 10/17/06 posting, you suggest the following:

Your relationship would look like this:
ApptContactID_kf = MedContactID_kf
ApptDateStart_k >= prescribed date
ApptDateStart_k <= discontinued date calc

Again, this would show all the meds in effect at the time of the encounter and you could still use that green dot mentioned above to see the ones still in effect.

Are you suggesting that I use the green dot in the portal to this TO? It seems like that would be redundant, since by design, that portal will only show the meds which are in effect at the time of the encounter. I'm guessing that, instead, you meant to suggest that I use a portal to this TO in my Edit Appointments layout (thereby displaying only the patient's "current" meds at each encounter) and a separate portal to the ContactMeds TO in my Contacts layout, containing a green dot to show "current" meds in that display. Am I correct?

John, I've come up with another problem. Backing up to the first two sentences in your 10/17/06 post:

At the baisc level you need two new tables. One for all your possible medications, and a second for the medications prescribed to a contact. Lets call this last one ContactMeds.

Can you explain how these two fields (Meds and ContactMeds) are related? And am I correct in assuming that the Meds table will contain a primary key and one or more fields such as "GenericName", "BrandName" etc., while the ContactMeds table will contain several other fields including the foreign key, StartDate, StopDate, etc?

jimrecht wrote:Thanks John. I'll try that. Meanwhile, here's another question.

Near the end of your 10/17/06 posting, you suggest the following:

Your relationship would look like this:
ApptContactID_kf = MedContactID_kf
ApptDateStart_k >= prescribed date
ApptDateStart_k <= discontinued date calc

Again, this would show all the meds in effect at the time of the encounter and you could still use that green dot mentioned above to see the ones still in effect.

Are you suggesting that I use the green dot in the portal to this TO? It seems like that would be redundant, since by design, that portal will only show the meds which are in effect at the time of the encounter.

I don't know if it's useful, but I don't think it is redundant. As you look at appointments in the past the portal above will show those in effect at that time. Some of those may still be in effect, and the green dot would indicate this.

jimrecht wrote:John, I've come up with another problem. Backing up to the first two sentences in your 10/17/06 post:

At the baisc level you need two new tables. One for all your possible medications, and a second for the medications prescribed to a contact. Lets call this last one ContactMeds.

Can you explain how these two fields (Meds and ContactMeds) are related? And am I correct in assuming that the Meds table will contain a primary key and one or more fields such as "GenericName", "BrandName" etc., while the ContactMeds table will contain several other fields including the foreign key, StartDate, StopDate, etc?

Perfect. Of course ContactMeds will have two foreign keys--one for the medication and one for the contact--along with those attributes specific to the prescription (start date, etc,.).

Hi John: You are being incredibly patient with me on this! At the risk of trying that patience even further, I feel I need to clarify a few things. I've highlighted my questions in bold.

1) Now I have 2 tables:

i) Meds: contains three fields ("GenericName", "BrandName", "RxID_kprime") and is related to the ContactMeds table (Meds::RxID_kprime = ContactMeds::RxID_kf);

ii) ContactMeds: contains the fields "RxID_kf", "ContactID_kf", "ApptID_kf", "RxStartDate", "RxDiscontinueDate", "RxDiscontinuedDateCalc" "RxDose", and "RxSchedule" (the last two fields would allow me to select various doses -- 1mg, 5mg, etc. -- and schedules -- once a day, three times a day, etc. -- for any given medication).

ContactMeds is related to the Meds table (Meds::RxID_kprime = ContactMeds::RxID_kf) , and to the Contacts table (Contacts::ContactID_kprime = ContactMeds::ContactID_kf).

If I place a portal to ContactMeds in my Contacts layout, I will see a complete list of that patient's medications, past and present, correct?

2) ContactMeds has two additional TO's:

i) CalDailyApptContactMeds: this is related to CalDailyApptContacts (CalDailyApptContactMeds::ContactID_kf = CalDailyApptContacts::ContactID_kprime).

If I place a portal to this TO in my Edit Appointment layout, I will see a complete list of all the patient's meds, past and present, correct?

ii) CalDailyApptMeds: this is related to CalDailyAppointments (CalDailyApptMeds::ContactID_kf = CalDailyApptointments::ContactID_kf and CalDailyApptMeds::ApptID_kf = CalDailyAppointments::ApptID_k).

If I place a portal to this TO in my Edit Appointment layout, I should see a list of only that patient's meds which were current at the time of the selected appointment, correct?

Finally, as you can see, at this point I have defined only one relationship for Meds, and that is to ContactMeds, as shown above. Is that correct?

I haven't yet embarked on the last TO/relationship you described, but I feel I need to re-group here first, to make sure I haven't made any serious errors.

Whew.

jimrecht wrote:Hi John: You are being incredibly patient with me on this! At the risk of trying that patience even further, I feel I need to clarify a few things. I've highlighted my questions in bold.

No problem Jim; I really appreciate the thorough questions.

jimrecht wrote:If I place a portal to ContactMeds in my Contacts layout, I will see a complete list of that patient's medications, past and present, correct?

Right.

jimrecht wrote:If I place a portal to this TO in my Edit Appointment layout, I will see a complete list of all the patient's meds, past and present, correct?

Right.

jimrecht wrote:ii) CalDailyApptMeds: this is related to CalDailyAppointments (CalDailyApptMeds::ContactID_kf = CalDailyApptointments::ContactID_kf and CalDailyApptMeds::ApptID_kf = CalDailyAppointments::ApptID_k).

If I place a portal to this TO in my Edit Appointment layout, I should see a list of only that patient's meds which were current at the time of the selected appointment, correct?

Not really. The relationship as described would only show those meds that were prescribed as a result of this appointment (ie that have this appointment's ID.) If you want to show all the meds in effect at the time of the appointment you'd remove CalDailyApptMeds::ApptID_kf = CalDailyAppointments::ApptID_k and add these two:

CalDailyApptointments::ApptDateStart_k >= CalDailyApptMeds::RxStartDate
CalDailyApptointments::ApptDateStart_k <= CalDailyApptMeds:: RxDiscontinuedDateCalc

jimrecht wrote:Finally, as you can see, at this point I have defined only one relationship for Meds, and that is to ContactMeds, as shown above. Is that correct?

I believe you'll need an instance of Meds attached to each instance of ContactMeds. This will let you show the Generic Name and brand name without looking these up into ContactMeds.

Good questions.

Excellent. Thanks again!

John: Two more questions.

1) Now I plan to place a portal to the TO CalDailyApptMeds in my MiniWindowEditAppointment. I want to be able to click on a drop-down or popup list in that portal, to bring up all available meds, sorted by generic name. But I'm stumped, since CalDailyApptMeds is based on ContactMeds, and therefore doesn't actually contain the names of the meds, only the key RxID_kf. Can you talk me through this one?

2) Backing up again to your 10/17/06 post, in describing the need for a calculated field for the med discontinuation date, you explain that:

...you'd use a new calculated field for the discontinued date incase there is none, something like if ( not isempty (discontinueddate) ; date ( 1 ; 1 ; 2025 ) ; discontinueddate)...

Do you mean if ( isempty (discontinueddate) ; date ( 1 ; 1 ; 2025 ) ; discontinueddate)...?

Yours,
Jim R.

jimrecht wrote:John: Two more questions.

1) Now I plan to place a portal to the TO CalDailyApptMeds in my MiniWindowEditAppointment. I want to be able to click on a drop-down or popup list in that portal, to bring up all available meds, sorted by generic name. But I'm stumped, since CalDailyApptMeds is based on ContactMeds, and therefore doesn't actually contain the names of the meds, only the key RxID_kf. Can you talk me through this one?

Not quite sure I follow (you don't need CalDailyApptMeds for the value list if its "all" medications) but this may have do with what I suggested above: that you'll need an instance of Meds attached to CalDailyApptMeds.

jimrecht wrote:2) Backing up again to your 10/17/06 post, in describing the need for a calculated field for the med discontinuation date, you explain that:

...you'd use a new calculated field for the discontinued date incase there is none, something like if ( not isempty (discontinueddate) ; date ( 1 ; 1 ; 2025 ) ; discontinueddate)...

Do you mean if ( isempty (discontinueddate) ; date ( 1 ; 1 ; 2025 ) ; discontinueddate)...?

Yes (woops!)