1 of 1 people found this helpful
So you currently have 2 tables: Clients and ClientMedications
Since you say, "I then need to be able to produce a report by selected period, showing what medications were taken and when.", there is no way you can put this information in one of your existing tables because there are many doses for every Client and every ClientMedication. You need a third table that I might call "MedicationLog". Whenever a medication is administered, a record would be created in this table.
MedicationLog will need a timestamp field, and a foreign key field to insert the ClientMedication primary key.
In your existing portal to ClientMedications, you could DISPLAY fields to show the date/time of last dose, but those fields would not exist in the ClientMedications table - they'd be pulled from the MedicationLog table by a sorted relationship (sorted by timestamp, descending). If you need to show more than just the last dose in that portal, then it gets a bit trickier, but certainly doable.
Records in this table would best be created by a script. Perhaps a "administer does now" button in the existing portal you have.
Thanks Jason. Yes, As a matter of course I set up my DB with all tables having both a primary key field and a foreign key field with the primary key linked to the foreign key in the subsidiary table. And I have the table MedicationLog, but haven't been able to make it work.
Ideally I would like to display alongside a patients medications list the date and time of the last dose and 4 fields for dosing during each day, also ideally with fields that record that the dose has been taken. This could be radio buttons or check boxes or maybe a field with a timestamp. I tried setting up a script within a button, but I am out of my depth.
1 of 1 people found this helpful
If 4 doses per day is a strict maximum, it may be easier to make the medicationLog record a DAILY record, with fields to permit detail for for doses.
Here's a fairly simple setup that doesn't require any scripts:
I believe your relationship graph looks like this:
Clients --< ClientMedications --< MedicationLog
Add a date field to MedicationLog if you don't have one already. Must be a date, not a timestamp.
Add 4 time fields to MedicationLog and whatever other info you need to store about each dose.
Add a global date field to ClientMedications. Call it "gDoseDateFilter".
Add an additional table occurrence on the graph for MedicationLog, call it "MedicationLog_byDate"
Connect it to ClientMedications using the same key fields as MedicationLog, and add an additional connection between the global date field in ClientMedications and the date field in MedicationLog_byDate.
In the relationship settings, turn on "allow creation of related records" for the MedicationLog_byDate side.
Place the global date field on the Clients layout next to your portal.
You might want to have an "open" script that sets this field to the current date every time your database is opened.
On the client layout, you have a portal to ClientMedications. You can add the 4 dose time fields from MedicationLog_byDate. You could put buttons next to each of them that set the current time.
You can change the date in gDoseDateFilter to see your dose times on previous dates, but remember to set it to the current date before adding/editing times.
I think you may need to attach a script trigger on field exit to the gDoseDateFilter field on the client layout. The script should commit records and then refresh the screen. Otherwise the portal may not update to display doses for the correct date when you change it.
That great, thanks Jason. I will give that a go and report back.
A couple of questions for you to consider about the medications.
1.) Is there a requirement to record each medication every-time it is given, the dose, time, route, pre-administration and post administration patient condition, vital signs and other information.
2.) Is the medication considered a Controlled Substance per the DEA? They have a whole boat load of requirements.
3.) Any medical-legal requirement/concerns for documentation?
The only reason I bring this up is if something were to occurr to a patient the medication records will be closely examined. You may be better off with creating a record each time a medication/intervention is given and then have a report/view that pulls up the medications given to a specific patient by name and date.
Better to check the reporting requirements before you decide on your design.
Been There Done That!
Thanks Mike, all good points. I am in Oz, but I guess our legal constraints own this area will be as onerous as in the US. As you can imagine, I am trying to help someone who is a smaller non profit out on this who cannot afford one of the existing giant systems. They are not offering medical services as such, but they do look after people with intellectual and other disabilities such as Bipolar disorder where there are problems with the existing paper based system. These people are on medication regimes that chop and change and keeping up to date is still important, if not life threatening. I do like the idea of the report based system and will look into that.