Relationships and script issues
I have been working with a very basic 2 table database, I need to add tables and I'm having trouble deciding on the relationships and subsequently scripts as well.
I am a Nurse Practitioner. I have many patients, the patients have many encounters. The parent table (PATIENT) consists of the patient info that does not change and has a one to many relationship with the child table (ENCOUNTER). So far, so good and I have been able to create "reports" that I can print and use as progress notes, but I have to separate fields for Diagnosis1, Diagnosis 2 etc,
I think I need to add a DIAGNOSIS "join" table (each patient/encounter has many diagnoses) and then subsequently an ASSESSMENT table (each diagnosis has many assessments) branching off that. I have the tables in place, each has its own primary key and I have also added the foreign keys to every table. Aside from the initial relationship (Patient table's PATIENT_ID primary key connecting to the Encounter table's PATIENT_ID foreign key) and also checked the "allow creation through this....." on the child side, I'm not sure what to connect in what direction. I have tried several different configurations of relationships with different keys (in a copy of the database) but I can't seem to accomplish what I need to do. I am missing something somewhere, several things, I'm sure.
Currently I have a layout with portals for the child table, I "find" a patient or add a new one and to add a portal record.
I have a script on a button that lets me enter new encounters for that specific patient (An online tech wrote it and I do not understand anything about it, he gave me a fish, but I want to learn to fish :)
After I "find" a patient (or enter a new one), I would like to have only that patient's diagnoses show in that field... choose which diagnoses (usually at least 4) I am addressing at that encounter and enter assessments specific to each diagnosis.
How do I use the inspector to narrow the values to be unique to that patient or do I need a script? ( I saw how to use the pk ID Field as a value list but I would like to take it down a notch)
The script writing thing has me totally stumped. Completely new language.
After I get this up and running, I will need scripts to add the "found set" of the diagnoses and their assessments within that encounter record to incorporate that data into the appropriate place in the report (in this case the report is the progress note I have to print out).
I have "The Missing Manual" ...just as clear as mud.
PhilModJunk are you out there? :)
Thanks to Phil and/or anyone who is able to help with this.